Generate a lookup in three steps
- Describe the result: “Find the price for the product ID in A2 using Products column A, and return column C.”
- Enter the references: lookup cell A2, table range Products!$A$2:$C$4, return column 3. Choose Excel or Google Sheets.
- Check and paste: confirm the formula’s references and test a known product before filling down.
The tool is linked above and requires no spreadsheet connection. Optional details are especially helpful when a description could refer to more than one column.
Example input and expected formula
| Row | A: ID | B: Product | C: Price |
|---|---|---|---|
| 2 | P-101 | Notebook | 4.50 |
| 3 | P-204 | Stapler | 12.00 |
| 4 | P-318 | Markers | 7.25 |
=VLOOKUP(A2,Products!$A$2:$C$4,3,FALSE)Put P-204 in Orders!A2 and paste this into Orders!B2. The result is 12.00. A2 is the search value; Products column A is searched because it is the first column of the selected range. The number 3 returns the price in the third column. FALSE makes the match exact.
The $ markers lock the catalog when you fill the result down. A missing product returns #N/A. Duplicate IDs return the first match rather than a total; decide whether your catalog should have unique IDs.
Choose a formula for the job
| Your task | Formula to learn | Generated here? |
|---|---|---|
| Return a value to the right of an ID | VLOOKUP with FALSE | Yes |
| Return a value to the left | XLOOKUP or INDEX MATCH | No — manual guide |
| Count rows with one condition | COUNTIF | No — manual guide |
| Add amounts with several conditions | SUMIFS | No — manual guide |
For example, =COUNTIF(B2:B6,"Open") counts matching status cells. It does not fetch the description next to the first Open status. =SUMIFS(C2:C6,B2:B6,"Paid") adds matching amounts. Those tasks need their own functions rather than a different return-column number.
Avoid the two common reference mistakes
First, start the table at the column containing the ID. If IDs are in F, select F:H rather than A:H. Second, count the return column within that range: H is column 3 of F:H, not column 8. For an exact lookup from F to H, use:
=VLOOKUP(A2,$F$2:$H$100,3,FALSE)Use the troubleshooting guide if the result is missing, from the wrong column, or unexpectedly close to the requested ID.