Your table has the code in the third column and the price in the first. VLOOKUP cannot return data to the left and you do not want to reorder the client’s file.
How to use INDEX MATCH Formula step by step
1. Choose the formula
INDEX with MATCH for any version, or XLOOKUP if you have Microsoft 365 or Excel 2021. There are also variants with your own error message and with two criteria.
2. Give the columns
Type the value you are after, the column where it lives and the column with the result. References come out pinned with $.
3. Copy and drag
Paste the formula in the first row and drag it down. With two criteria, remember to confirm it with Ctrl+Shift+Enter if your Excel predates 2021.
The Step Count: Traditional Friction vs. KorexTools
Reordering columns so VLOOKUP can work (4 steps)
- Copy the search column to the front.
- Move the result column to the right.
- Adjust the formulas that relied on the order.
- Check the whole file again.
With the KorexTools “INDEX MATCH Formula” builder (3 steps)
- You pick the variant and the two columns.
- You enter the value you are after.
- You copy the formula and drag it.
Looking left and searching by two columns is where VLOOKUP falls short
VLOOKUP only looks to the right of the search column and breaks when you insert a new column. With INDEX and MATCH you separate the two jobs: MATCH finds the row and INDEX returns the value from whichever column you choose, wherever it sits. That is why the pair survives changes to the table.
XLOOKUP does the same with a single function and has a built-in “not found” message, but it only exists in recent versions. To search on two conditions at once you multiply two comparisons inside the lookup, and to keep the last match XLOOKUP supports a reverse search mode.
Which variant to pick
- •Workbook shared with old versions: INDEX and MATCH.
- •Microsoft 365 or Excel 2021 only: XLOOKUP, which is shorter.
- •Two columns that must match at once: the two-criteria variant.
Codes in A, city in B, price in C; searching for the code in E2
| Variant | Formula | What it returns |
|---|---|---|
| INDEX and MATCH | =INDEX($C$2:$C$100,MATCH(E2,$A$2:$A$100,0)) | The price for the code |
| XLOOKUP | =XLOOKUP(E2,$A$2:$A$100,$C$2:$C$100) | The same price, shorter |
| Two criteria | =XLOOKUP(1,($A$2:$A$100=E2)*($B$2:$B$100=F2),$C$2:$C$100) | The price of that code in that city |
Common Mistakes and How to Avoid Them
- ⚠️Leaving the third MATCH argument at 1 and getting random results.
- ⚠️Forgetting Ctrl+Shift+Enter on the two-criteria variant with old Excel.
Frequently Asked Questions
Why use INDEX and MATCH if VLOOKUP exists?
Because it does not depend on column order: you can return data from the left and the formula does not break when columns are inserted.
Does the two-criteria variant need Ctrl+Shift+Enter?
In Excel before 2021 yes, because it works with arrays. In Microsoft 365 and Excel 2021 pressing Enter is enough.
What does approximate match mean?
It finds the band a value belongs to, like a step in a price list. The search column must be sorted from lowest to highest.
How do I do INDEX and MATCH with three criteria?
Generate the two-criteria option and add a third factor to the multiplication inside MATCH: one more comparison in brackets, with its column, multiplied by the earlier ones. Every extra criterion is one more comparison; in Excel before 365 you confirm it with Ctrl, Shift and Enter.
Is there a simple INDEX and MATCH example?
With codes in A2:A100, prices in C2:C100 and the sought code in E2, the exact-match option returns =INDEX(C2:C100,MATCH(E2,A2:A100,0)). MATCH finds the row position and INDEX brings the price from that position.
What I would do if I were in your shoes
«When a table can grow or change columns, look up by header and not by column number.» — DataKorex Engineering Team.