Bring prices into an order list
PremiumIn C2, look up the price of the code in B2 in the price table E2:F5. Lock the table with $ so the formula can be filled down the order list: VLOOKUP(B2,$E$2:$F$5,2,FALSE).
| A | B | C | D | E | F | G | |
|---|---|---|---|---|---|---|---|
| 1 | Order | Code | Price | Code | Price | ||
| 2 | 1001 | C30 | A10 | 12 | |||
| 3 | 1002 | A10 | B20 | 35 | |||
| 4 | 1003 | C30 | C30 | 89 | |||
| 5 | 1004 | D40 | D40 | 24 | |||
| 6 | 1005 | B20 | |||||
| 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
The lookup value, B2, is relative, so each row of the order list looks up its own code. The table, $E$2:$F$5, is locked, so every row searches the same four codes. C30 costs 89; filled down, the other orders pick up 12, 89, 24 and 35.
Common mistake: Leaving the table relative. The first rows work, but further down the range has slid past the codes at the top and you get #N/A.
The answer
- C2
=VLOOKUP(1B2,2$E$2:$F$5,32,4FALSE)
In plain words: Find 1the code in B2 in the first column of 2the locked price table E2:F5 and return 3the price in its second column, 4only on an exact match.