I am trying to perform a VLOOKUP on a spreadsheet referencing a table array on another spreadsheet. Some, but not all, values are coming up as #NA. There are no invisible or hidden characters, and I checked most of the values, and they are in fact on the table array I am having the VLOOKUP search. I am completely at a loss.

Here is my formula:

=VLOOKUP(M2,'S:\FY 2012 ACTIVITY\FY12 Outstanding 2237s\[Status Tech.xls]Status Tech'!$A$2:$B$903,1)

Like I said, it finds many, but many more are coming back as #NA.

Also, the formatting is the same for all coolumns. They are all formatted for text.

I have included the two spreadasheets I am working from. Someone, please help.



The columns in question are:
(From the March 19 sheet) M and O
(From the status Tech Sheet) A and B

I want the formula to search the staus Tech sheet and return a value into the O column, searching for the M Value. Thanks.

Brian

Edit:

Solved this on my own, thanks.

When you use VLOOKUP you have to find a value in a column and then return the corresponding value from one of the columns to the right of that column. For example, you might want to look up a value in column A and then return the corresponding value from column B.

In this case you want to look up a value in column B and return the corresponding value from column A. ie. a "left lookup". The VLOOKUP() function can't do that.

So you have two choices.

(1) If you want to use VLOOKUP then you'll have to edit the Status Tech file and move the APPROP column to the right of the FCP column. Even though the data in the FCP column is sorted, unless you are 100% sure that there will always be an exact match, I recommend you pass 0 into the range_lookup parameter instead of 1 (you are omitting it at the moment so it is using 1 by default). Using 1 is faster, but it will return a value even if an exact match isn't found - something you might not want. So, if you swap around the APPROP and FCP columns then your formula would be very similar to what you have now:
(2) Use a different formula. One way to do a "left lookup" is to use a combination of IINDEX and MATCH. Something like this: