Can I use LEFT function in VLOOKUP?

[greyscale]

Hi,

Due to privacy policy, I am only able to upload a tiny part of my spreadsheet but I hope it is enough for one of you clever people to help me. Basically, I need to return the name of the area code in the spreadsheet 'table'. I am trying to only look up the first 4 digits of column j because if I try to convert it in the document before using vlookup it doesn't seem to work because I am lookinp up a formula rather than an actual value in the cell.

Does anyone know if it is possible to use this calculation? (cell A2 on the attached)

=IF(ISERROR(VLOOKUP(LEFT(J2,4),'Area Codes'!A:B,2,FALSE)=TRUE),"n/a",VLOOKUP(LEFT(J2,4),'Area Codes'!A:B,2,FALSE))

Thanks in advance and thank god for this forum.

[Richard Buttrey]

Hi,

Yes there are at least two ways, both array formulae - i.e entered with Ctrl-Shift-Enter

Please Login or Register  to view this content.

and if the Area codes are sorted by column A

Please Login or Register  to view this content.

or if you only want the first 4 characters of the name

Please Login or Register  to view this content.

[vlady]

Good morning grey

=VLOOKUP(--LEFT(J2,5),'Area Codes'!A1:B10,2,FALSE)

btw left(...,5) your value is 5 digit

[greyscale]

Thanks for your replies. I seem to have it set up from what I can tell but I still can't get it to work. I'm afraid I made a mistake on my initial attachment as there are only 4 digits in the area code worksheet. i have attached a new doc. Cell A2 on 'table' worksheet should be returning 'Newcastle, Lower Hunter'. Sorry for the confusion.

[Richard Buttrey]

It will work if you extend the lookup range to row 55. It's only 10 at the moment and the value you're looking up is on row 11
So using vlady's function

=VLOOKUP(--LEFT(J2,4),'Area Codes'!A1:B100,2,FALSE)

[greyscale]

Fantastic. thank you both. Can I ask what the '--' does?

[Richard Buttrey]

Fantastic. thank you both. Can I ask what the '--' does?

Hi,

With the data you're using the lookup table contains numbers. Even though the number you're using to look up is itself a number, when you extract the leftmost four characters the four characters are treated as a string of text. Hence a straightforward VLOOKUP() would return an error.

The '--' is what's known as a double unary operator, (no don't ask me!) and using this converts a text string to a number. You see it used a lot in SUMPRODUCT() functions. Think of it as a double minus mathematical operator which is forcing a calculation.

You could just as easily use
=VLOOKUP(VALUE(LEFT($J$2,4)),'Area Codes'!A1:B100,2,FALSE)
since the VALUE() function effectively performs the same function and converts a string to a number value.

Regards

[greyscale]

Ah! That's a great explanation and I appreciate you taking the time to make it clear. I had tried to google it but had come unstuck at 'double unary operator'

[sarxes]

Hi,

Maybe you can help me out to combine these two codes. I tried myself but always gave me an error at the end.

Please Login or Register  to view this content.

and

Please Login or Register  to view this content.

[Sam Capricci]

sarxes, please don't post a question for your issue on another person's post. It is against the forum rules (rule #4).
if you have a question start your own post, they are free and if you believe this post has something significant to add to your issue, paste the url in your post.
thanks.