VLOOKUP giving #N/A and I have no idea why
excel, excel-formula, excel-match, vlookup, worksheet-function
Solution
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
Problem
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".