Look up a price in Excel with INDEX and MATCH
Type a SKU, get the product name and the price. The price list in this tutorial puts the SKU in the middle column — product name on the left, price on the right — so VLOOKUP cannot do the job: it only looks from left to right. INDEX and MATCH look in any direction.
| Checked | |
|---|---|
| Apps | Excel, Google Sheets, LibreOffice |
| Versions | LibreOffice 24.2.7.2 (recalculation) · Python 3.12.3 · openpyxl 3.1.2 |
| Read time | 7 minutes |
Measured result — from the recalculated file
| SKU HD-104 returns | Adjustable wrench, 250 mm |
| Unit price for HD-104 | $21.50 |
| Line total, quantity 3 | $64.50 |
| SKU ZZ-999 (not in the list) returns | Not in price list |
Download the spreadsheet: price-lookup.xlsx (5,652 bytes)
The file that was recalculated and checked. It contains the price list, the lookup cells, and a second lookup with a SKU that is not in the list.
1What you need
- Excel, Google Sheets, or LibreOffice. INDEX, MATCH and IFNA are covered below; the version note in step 7 matters if you share the file.
- The file above, or a blank workbook if you want to type the formulas in yourself.
2The price list
Rows 3 to 12 hold ten products. The SKU is the middle column on purpose: the product name sits to its left, where VLOOKUP cannot reach. Microsoft's own guidance puts it plainly: VLOOKUP can only look up a value from left to right. When the value you want is not to the right of the lookup column, use INDEX and MATCH instead (Look up values with VLOOKUP, INDEX, or MATCH — Microsoft Support).
| Product | SKU | Unit price |
|---|---|---|
| Claw hammer, 16 oz | HD-101 | $18.49 |
| Phillips screwdriver set, 6 pc | HD-102 | $24.99 |
| Tape measure, 8 m | HD-103 | $14.75 |
| Adjustable wrench, 250 mm | HD-104 | $21.50 |
| Utility knife + blades | HD-105 | $9.99 |
| Cordless drill, 18 V | HD-106 | $129.00 |
| Drill bit set, 21 pc | HD-107 | $32.49 |
| Level, 600 mm | HD-108 | $19.95 |
| Safety glasses, clear | HD-109 | $6.49 |
| Work gloves, large | HD-110 | $12.95 |
Put the SKU to look up in F2 and the quantity in F3. In the checked file, F2 holds HD-104 and F3 holds 3.
3MATCH finds the row
MATCH searches for a value in a range and returns its position — a row number, not the value itself. For HD-104 in the SKU range B3:B12, the position is 4.
=MATCH(F2, B3:B12, 0)The 0 at the end means exact match. Leave it out and MATCH does an approximate match, which needs sorted data and silently returns the wrong row when the data is not sorted. Always write the 0 for a lookup like this.
4INDEX returns the product name
INDEX takes a range and a row number and returns the value in that row. Nest MATCH inside it, and the row number comes from the search:
F5 =IFNA(INDEX(A3:A12, MATCH(F2, B3:B12, 0)), "Not in price list")The return range is the product column A3:A12 — to the left of the SKU column, where VLOOKUP cannot go. Result for HD-104: Adjustable wrench, 250 mm.
5INDEX returns the price
The same pattern, with the price column as the return range:
F6 =IFNA(INDEX(C3:C12, MATCH(F2, B3:B12, 0)), "Not in price list")Result for HD-104: $21.50. Then the line total multiplies the price by the quantity, but only if a price was actually found:
F7 =IF(ISNUMBER(F6), F6*F3, "—")Result for quantity 3: $64.50. The ISNUMBER test matters: without it, a missing SKU would try to multiply the text “Not in price list” by the quantity and return an error.
6A missing SKU gets a message, not an error
IFNA catches the #N/A that MATCH returns when the SKU is not in the list, and returns the text instead. The file has a second lookup block with ZZ-999 in F9 — a SKU that is not in the price list — and both cells return “Not in price list”:
F10 =IFNA(INDEX(A3:A12, MATCH(F9, B3:B12, 0)), "Not in price list")
F11 =IFNA(INDEX(C3:C12, MATCH(F9, B3:B12, 0)), "Not in price list")Change F2 to any SKU in the list and the name, price, and total update. Change it to a SKU that is not there and you get the message instead of #N/A.
7The XLOOKUP version, and where it does not work
In current Excel and Google Sheets, one function does the same job:
=XLOOKUP(F2, B3:B12, A3:A12, "Not in price list")XLOOKUP searches a range and returns the item for the first match, with a built-in answer for a missing value. But it is version-gated. Microsoft states XLOOKUP is not available in Excel 2016 and Excel 2019; it needs Excel for Microsoft 365, Excel 2021, or Excel 2024. Google Sheets supports it — Google's own function reference lists XLOOKUP with the same arguments.
LibreOffice does not: in the recalculation for this tutorial, LibreOffice 24.2.7.2 returned #NAME? for XLOOKUP. That is why the shipped file uses INDEX and MATCH — it calculates in Excel, Google Sheets and LibreOffice alike — and why the XLOOKUP formula above is shown as text, not stored in the file.
Traps
Forgetting the 0 in MATCH. MATCH(F2, B3:B12) without a match type does an approximate match and can return a row that has nothing to do with your SKU. Exact-match lookups need the 0.
Ranges of different heights. The lookup range and the return range must cover the same rows. INDEX(A3:A12, MATCH(F2, B3:B11, 0)) is off by one row at the bottom and will return the wrong product for the last SKU.
Invisible spaces in the SKU. A trailing space in the list or in the lookup cell means no match, and the formula returns “Not in price list” for a SKU that is really there. The fix is to clean the list once: put =TRIM(B3) in a helper column, copy it down, and paste the results back over the SKUs as values.
How this was checked
The workbook was built with openpyxl 3.1.2 (formulas only, no typed-in results), recalculated by LibreOffice 24.2.7.2 in headless mode, and read back with openpyxl in data-only mode. The expected values were checked against the price list itself: HD-104 is the fourth row, priced at $21.50, and $21.50 × 3 is $64.50. The missing-SKU branch was checked with ZZ-999 in a second lookup block in the same file, which returned “Not in price list” in both cells. XLOOKUP was tested the same way and returned #NAME? in LibreOffice 24.2.7.2, which is why it is not in the file.
Sources
- Look up values with VLOOKUP, INDEX, or MATCH — Microsoft Support — why VLOOKUP cannot look left, and the INDEX and MATCH pattern.
- XLOOKUP function — Microsoft Support — syntax and the note that XLOOKUP is not available in Excel 2016 and Excel 2019.
- XLOOKUP function — Google Docs Editors Help — XLOOKUP in Google Sheets, with the same arguments.