I'm working on two tables in one sheet, searching for text values (car types) for car numbers. Car numbers in both tables are trimmed and formatted as text (they are the same). '=VLOOKUP(B4,$F$4:$G$8,1,FALSE)' shows a value not available error. Since there are no spaces and values from both tables are formatted as text, what can be the reason for the error?
Excel VBA Course - From Beginner to Expert
200+ Video Lessons
50+ Hours of Video
200+ Excel Guides
Become a master of VBA and Macros in Excel and learn how to automate all of your tasks in Excel with this online course. (No VBA experience required.)
Vlookup value not available error
0
Answers
0
Selected Answer
Hi SoHo and welcome to the Forum
The error occurs since VLOOKUP will search for B4 in the leftimost column of the range $F$4:$G$8. That's column F where the value dos not exist.
Change the formula in D4 to:
=VLOOKUP(B5,$G$4:$G$8,1,FALSE)and the error will go.
If you want to return the value from current column F, swap columns F and G and use:
=VLOOKUP(B6,$F$4:$G$8,2,FALSE)to return the value from the second column when a match is found.
Altrenatively (to look backwards in a range), search the Tutorials section for the more flexible INDEX/MATCH.approach.