Fix a VLOOKUP that fails on a space
PremiumThe code in F2 was pasted from an email and ends with a space, so a plain VLOOKUP returns #N/A. In G2, look up the price with TRIM(F2) as the lookup value.
| A | B | C | D | E | F | G | H | |
|---|---|---|---|---|---|---|---|---|
| 1 | Code | Product | Price | Code | Price | |||
| 2 | A10 | Pen | 12 | C30 | ||||
| 3 | B20 | Notebook | 35 | |||||
| 4 | C30 | Stapler | 89 | |||||
| 5 | D40 | Folder | 24 | |||||
| 6 | ||||||||
| 7 | ||||||||
| 8 |
Premium lesson
Unlock VLOOKUP and every other lesson
This is a premium lesson. Premium lets you solve it and every other lesson, take the timed mock tests, and earn certificates you can share on LinkedIn.
Your first month for $9.50 $19
50% off your first month when you join today.
Then $19/month. Cancel any time, and get a full refund within 14 days if it isn't for you.
Why this works
To VLOOKUP, "C30 " with a trailing space and "C30" are different values, so the lookup finds nothing and returns #N/A. TRIM(F2) removes spaces at the start and end, and the code then matches row 4, where the stapler costs 89.
Common mistake: Retyping the code by hand. It fixes this one cell, but the next pasted code fails the same way.
The answer
- G2
=VLOOKUP(1TRIM(F2),2A2:C5,33,4FALSE)
In plain words: Find 1the code in F2 without extra spaces in the first column of 2A2:C5, and return 3the third column, the price, 4on an exact match only.