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.
It comes up in this mock test: Excel assessment (Indeed format)
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.