Geek asks and answers

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

enter image description here

Why is this not working? I tried checking the spaces, making sure the length was the same, etc. Driving me nuts! I just want Alabama! The indicated error is: "Value not available."

+3
source share
4 answers

The usual solution is to use = MATCH to find the line number in the array:

=MATCH(D1,B:B,0)

will look for the value in D1in column B (the latter 0means exactly) and hopefully returns 2(the second row in the selected array - that is, the entire column B). Then it can be served in:

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

where it becomes the second row of column A, i.e. Alabama

= MATCH = INDEX, http://www.excelhero.com/blog/2011/03/the-imposing-index.html

+10

. , . , , AL " ". , excel , , #N/A!.

:

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

, .

+4

The formula searches for "AL" in the specified first column (column A). He finds only "Alabama", therefore returns an error.

0
source

It might be a little easier, even:

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

More articles:


All Articles