1

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.

pill45
  • 599
  • 3
  • 8
  • 23

1 Answers1

1

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

pnuts
  • 58,317
  • 11
  • 87
  • 139