I have a problem when using VLOOKUP in excel for MAC 2011. I am looking up for a value two column in different sheet, Column A is the one I want to check, Column B is the reference.
=VLOOKUP(A1, Sheet2!$A$1:$B$1000,2,0)
Some of boxes in Column A get a value return, but the other cannot. For those which cannot, when I retype the words inside the box, and I get the value returned. I try to copy the Column A and selectively paste with value only, but those cannot get a returned value remained the same.
I guest there is something wrong with the format, but I cannot fix it. Please help.
Seems likely an issue with trailing spaces. With or without in the Table-array can be handled by adapting your formula to:
=VLOOKUP("*"&A1&"*",Sheet2!$A$1:$B$1000,2,0)
(provided you don't have something like manifestation as well as station in ColumnA!).
If your source data has leading or trailing spaces that your Table_array does not then the recommended solution is to remove those by a formula such as:
=TRIM(A1)
copied down to suit and then that array inserted back to A1 with Paste Special, Values.
Another possibility is that you may be trying to match a value to text that looks like a value (or vice versa).