We want the department of every name in G2:G7, with one formula copied down H2:H7.
keyboard Typing formulas is easiest on a computer with a keyboard.
| A | B | C | D | E | F | G | H | I | |
|---|---|---|---|---|---|---|---|---|---|
| 1 | Employee | Department | Salary | Employee | Department | ||||
| 2 | Tariq Li | Finance | 63000 | Yusuf Dubois | |||||
| 3 | Nadia Patel | Sales | 41000 | Quinn Evans | |||||
| 4 | Daniel Fischer | IT | 38000 | Isla Kowalski | |||||
| 5 | Quinn Evans | Sales | 61000 | Elif Walsh | |||||
| 6 | Jamal Novak | HR | 32000 | Jamal Novak | |||||
| 7 | Isla Kowalski | Finance | 28000 | Tariq Li | |||||
| 8 | Kofi Patel | HR | 68000 | ||||||
| 9 | Yusuf Dubois | Finance | 74000 | ||||||
| 10 | Sam Okafor | Marketing | 45000 | ||||||
| 11 | Rosa Jensen | Finance | 59000 | ||||||
| 12 | Chloe Patel | Operations | 71000 | ||||||
| 13 | Elif Walsh | Marketing | 71000 | ||||||
| 14 | |||||||||
| 15 | |||||||||
| 16 | |||||||||
| 17 |
When you copy a formula down, Excel moves its references down too. That's what you want for the lookup value (G2 becomes G3), but not for the table: A2:C13 would become A3:C14 and miss the first row.
A $ locks the part after it. $A$2:$C$13 stays exactly where it is wherever you copy it.
While typing a reference, press F4 to add the $ signs.
VLOOKUP returning #N/A even though the values match, the wrong value, or breaking when you drag it down? The 9 usual causes and the exact fix for each.
Know someone fighting with a spreadsheet? Send them this page.