Excel VBA Course
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.)

View Course

Vlookup value not available error

0

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?

Answer
Discuss

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.

Discuss

Discussion

Thanks for selecting my answer Soho 
John_Ru (rep: 4312) Nov 7, '22 at 1:49 pm
Thank you so much for the detailed answer. I figured it out already, but couldn't find the reason why it didn't work for the first column. Now I know.
SoHo106 (rep: 2) Nov 7, '22 at 1:51 pm
Add to Discussion


Answer the Question

You must create an account to use the forum. Create an Account or Login