Does vLookup have to be all the way in the left?

[SEMMatt]

Hi All,

I'm trying to do a vlookup from a list where the text string that my lookup value is supposed to match is in the fourth column of the row on my index worksheet. Subsequently my v-lookup formula is giving back an error of N/A. I recently heard that the vLookup has to be all the way in the left hand column of the array - is that true?

For instance, in trying to obtain the metric in Sheet 2 in the same row as Apples this works:

Sheet 1: Apples Oranges Pear
Sheet 2: Apples 5 3

vlookup (apples)

but this does not:

Sheet 1: Apples Oranges Pears
Sheet 2: 5 3 Apples

vlookup (apples)

[tigeravatar]

SEMMatt,

That is correct. If you want to lookup an item and then return a corresponding value that is to the left of the vlookup column, you'll need to use Index/Match in this format:
=Index(resultcolumn,Match(lookupvalue,lookupcolumn,0))

[SEMMatt]

Thanks tigeravatar!

[SEMMatt]

tigeravatar maybe you could help me further...I've attached an example worksheet with the case.

example22.xlsx

[tigeravatar]

SEMMatt,

Attached is a modified version of your example workbook. In Sheet1 cell B1 is this formula:
=INDEX(Sheet2!A1,MATCH(A1,Sheet2!B1,0))

[SEMMatt]

Ok, but I'm still not 100% clear on what's going on. Can you try it with this example:

example22 (Modified by SEMmatt).xlsx

[tigeravatar]

In Sheet1 cell B1 and copied down:
=INDEX(Sheet2!$A$1:$A$3,MATCH(A1,Sheet2!$B$1:$B$3,0))

[SEMMatt]

got it - now I understand. Thanks so much tigeravatar.