We want the price of each product in B2:B7. The answers go in D2:D7.
keyboard Typing formulas is easiest on a computer with a keyboard.
| A | B | C | D | E | |
|---|---|---|---|---|---|
| 1 | Order | Product | Quantity | Price | |
| 2 | #1657 | Wireless Mouse | 2 | ||
| 3 | #1659 | Bookshelf | 1 | ||
| 4 | #1667 | Filing Cabinet | 11 | ||
| 5 | #1663 | USB-C Hub | 12 | ||
| 6 | #1681 | Desk Organiser | 5 | ||
| 7 | #1672 | Monitor Stand | 9 | ||
| 8 | |||||
| 9 | |||||
| 10 | |||||
| 11 | |||||
| 12 | |||||
| 13 | |||||
| 14 | |||||
| 15 |
Put the sheet name and an exclamation mark in front of the cells: Prices!A2:C11 means cells A2 to C11 on the Prices sheet. A reference without a sheet name always means the sheet the formula is on.
No. While typing the formula, click the sheet's tab at the bottom and select the cells. Excel writes Prices!A2:C11 for you. Press Enter to finish, and you're taken back to the sheet you started on.
Wrap it in single quotes: 'Price List'!A2:C11. Clicking the tab adds the quotes for you.
Filling down moves the cells, even on another sheet: Prices!A2:C11 becomes Prices!A3:C12 one row down. Prices!$A$2:$C$11 stays put. Press F4 while the cursor is on the range.
XLOOKUP work the same way?Yes. Both arrays need the sheet name: =XLOOKUP(B2,Prices!$A$2:$A$11,Prices!$C$2:$C$11).
Join two Excel files on a shared column such as an ID or email: with XLOOKUP, with Power Query, or in your browser with no formulas. Handles missing and duplicate keys.
Know someone fighting with a spreadsheet? Send them this page.