vlookup not working

  • Thread starter Thread starter Guest
  • Start date Start date
G

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