VLOOKUP() not working properly

**ckgeary** · 06-28-2013, 01:23 PM

I am trying to use VLOOKUP() to make my spreadsheets work together........... I want to move info from column H on "Matser Inst List" to column D on "Report 1" based on the description in column C. I am using this

=VLOOKUP(C5,'Master Inst List'!A:AZ,8,FALSE)

but all I am getting in return is #N/A. Anyone know why?

I have attached my spreadsheet..........TESTER.xlsm

**Ace_XL** · 06-28-2013, 01:27 PM

use INDEX/MATCH

=INDEX('Master Inst List'!H:H,MATCH(C5,'Master Inst List'!$N:$N,0))

**Jonmo1** · 06-28-2013, 01:28 PM

There's 2 problems.

1. Vlookup looks for a match to the lookup value (C5) in the LEFTmost column of the lookup range (A:AZ)
There are no values in column A on the master Inst List sheet.
2. It appears you want to find the match to C5 in Column N on the master Inst List sheet, and return a value from column H.
Vlookup can't do that, it can only go to the right..

Try
=INDEX('Master Inst List'!H:H,MATCH(C5,'Master Inst List'!N:N,0))

**XOR LX** · 06-28-2013, 01:28 PM

Hi,

Perhaps you should be starting your table array in column N, since this appears to be where your search strings are located.

Regards

**FDibbins** · 06-28-2013, 01:32 PM

for a vlookup to work, what you are searching for MUST be in the 1st column of the range. also, generally speaking, vlookup cannot look to the left of that column.

So your formula needs to be something like this...

=VLOOKUP(C5,'Master Inst List'!N:AZ,..........and the column number you want to return the data from....,FALSE)

However, if you want to use the 8th column (H), then vlookup wont work for you. try this instead...

=index('Master Inst List'!B:AZ,match(C5,'Master Inst List'!N:N,0),7)