VLOOKUP giving #N/A and I have no idea why

Question

Why doesn't this work? I tried checking for whitespace, made sure length was the same, etc. Driving me nuts! I just want Alabama! The error given is "Value not available".

Answer 1

The order of your column is wrong. The leftmost column must contain the value you are matching. What you are doing is looking for AL in the State Name column. Of course, excel can't see it and thus returns #N/A!.

You can try this:

=INDEX($A:$A,MATCH(D1,$B:$B,0),1)

Hope this helps.

Answer 2

The formula looks for "AL" in the first column specified (column A). It only finds "Alabama", so returns error.

Answer 3

It can be slightly easier, even:

=INDEX(A:A,MATCH(D1,B:B))

Answer 4

The usual solution is to apply =MATCH to find the row number in the array:

=MATCH(D1,B:B,0)  

will search for the value in D1 in ColumnB (the last 0 means exactly) and hopefully return 2 (the second row in the chosen array - ie the whole of ColumnB). This can then be fed into:

=INDEX(A:A,MATCH(D1,B:B,0))  

where it becomes the second row of ColumnA, ie Alabama

Details here =MATCH =INDEX and also http://www.excelhero.com/blog/2011/03/the-imposing-index.html