vlookup not working

[Guest]

Also I am using =VLOOKUP(J5,'Ship List'!A$1:B$30,2,FALSE) to look in column A
on another worksheet (Ship List) and return the value in column B. I have
successfully used vlookup before. Column A in Ship List is sorted and I have
tried formatting both the lookup value and Col A from the lookup array as
text, general, number. However only a few cells return matches. Also I have
made sure the lookup val and Col A are the same length. The lookup value is a
5 digit number. Any ideas?
Thanks,
Joe M.

 

[Guest]

Try:
=VLOOKUP(TEXT(J5,"00000"),'Ship List'!A$1:B$30,2,0)

 

[JE McGimpsey]

Sounds like the numbers in your lookup table were entered as Text
(changing the formatting doesn't change that). Try copying an empty
cell, selecting the numbers, then choose Edit/Paste Special, selecting
the Values and Add radio buttons. This should coerce Text numbers to
actual numbers.

 

[Guest]

more genaric
=if(iserror(Vlookup(J5,'Ship
List'!A$1:B$30,2,0)),if(iserror(VLOOKUP(TEXT(J5,"00000"),'Ship
List'!A$1:B$30,2,0)),vlookup(value(J5),'Ship
List'!A$1:B$30,2,0),VLOOKUP(TEXT(J5,"00000"),'Ship
List'!A$1:B$30,2,0)),Vlookup(J5,'Ship List'!A$1:B$30,2,0))