Price from whichever list the switch names (IF + lookup)
The problem
Problem statement
Two price lists live side by side — old in A2:B5, new in D2:E5 — and a switch cell decides which one to use: H2 says "New" or "Old", the SKU is in H1. Put the price in the Price cell H3.
One formula must read both the SKU and the switch; no IF in a helper column, and no copying prices around.
Problem data
Show the sheet as a table (selectable, copyable)
| A | B | D | E | G | H | |
|---|---|---|---|---|---|---|
| 1 | SKU | Old price | SKU | New price | SKU | SKU-103 |
| 2 | SKU-101 | 49 | SKU-101 | 52 | Price list | New |
| 3 | SKU-102 | 19.5 | SKU-102 | 21 | Price | |
| 4 | SKU-103 | 22.5 | SKU-103 | 24.99 | ||
| 5 | SKU-104 | 89 | SKU-104 | 95 |
Notes
IF picks a lookup, not just a value: each branch is a complete VLOOKUP against one table. Because the two tables are separate ranges, nothing needs to be re-sorted when prices change. INDEX + MATCH is the same idea with the position found explicitly, which reads better when the two tables have different column layouts.
How to approach
Your answer goes in H3. On submit we re-run your formula against 3 different sets of input, so a hardcoded number will fail — the formula has to follow the cells.
- Target cell is H3
- The switch in H2 chooses the table
- Each branch of the IF is a whole VLOOKUP
- Shape: =IF(?, VLOOKUP(?, ?, ?, ?), VLOOKUP(?, ?, ?, ?))
Official solutions
Show 2 solutions — try it yourself first
Approach 1: IF choosing between two VLOOKUPs
=IF($H$2 = "New", VLOOKUP($H$1, $D$2:$E$5, 2, FALSE), VLOOKUP($H$1, $A$2:$B$5, 2, FALSE))
With H2 = New the formula returns 24.99 from the new list; flip it to Old and the same cell shows 22.50. The absolute $ on H1/H2 keeps both references fixed if the formula is copied.
Approach 2: IF choosing between two INDEX MATCH pairs
=IF($H$2 = "New", INDEX($E$2:$E$5, MATCH($H$1, $D$2:$D$5, 0)), INDEX($B$2:$B$5, MATCH($H$1, $A$2:$A$5, 0)))
Same switch, but the return column is named directly instead of counted. Useful when the two lists do not sit in the same column order.
Function guide
FAQ
How do I look up from one of two tables?
Put a whole lookup in each branch: =IF(switch="New", VLOOKUP(k, new_tbl, 2, 0), VLOOKUP(k, old_tbl, 2, 0)).
Why not merge the two lists into one table?
Often you should. Keep them separate only when the lists have different schemas, or when the old one must stay frozen for historical reporting.