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

问题:

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".

回答1:

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

回答2:

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.

回答3:

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

回答4:

It can be slightly easier, even:

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