You have an orders sheet and a price sheet, and the product code is not in the first column of the price list. VLOOKUP complains, and counting columns by hand fails you every time someone inserts one.
How to use XLOOKUP Formula step by step
1. Choose the case
Plain lookup, with a notice if it is missing, with several columns at once, approximate upward or downward, by part of the text, bottom-up or with two criteria.
2. Point at the columns
Type the cell with the sought value, the column holding it and the one you want back. They can sit on another sheet and in any order.
3. Copy the formula
Paste it into your sheet. It needs Excel 2021 or Microsoft 365; on older versions use VLOOKUP or INDEX with MATCH.
The Step Count: Traditional Friction vs. KorexTools
Finding a price by scanning the list (4 steps)
- Open the price sheet.
- Run your eyes down the code column.
- Copy the price shown beside it.
- Repeat for each order on the list.
With the KorexTools “XLOOKUP Formula” builder (3 steps)
- You choose the kind of lookup.
- You give the key column and the result column.
- You copy the formula and drag it down your orders.
What XLOOKUP fixes about VLOOKUP is that you no longer count columns
XLOOKUP takes three pieces: what you look for, the column to look in and the column to bring the result from. Because they are two separate columns, you can return a value sitting to the left of the key, which VLOOKUP cannot do, and it does not break when you later insert or delete columns in the table.
It also adds four options of its own: a text for when nothing is found, a match mode that allows the nearest approximate value or wildcards, and a search direction that lets you start from the end to keep the last occurrence. If you hand it a multi-column range as the result, it returns all those columns at once.
Which option to pick
- •A value that must exist: the plain lookup.
- •A list with gaps: add the notice text.
- •Price bands or grades: approximate to the next smaller.
- •A repeated customer where you want the latest order: search from the end.
Code in A2; codes in Sheet2!A:A, prices in Sheet2!C:C
| Case | Formula | What it returns |
|---|---|---|
| Plain | =XLOOKUP(A2,Sheet2!A:A,Sheet2!C:C) | The code’s price |
| With a notice | =XLOOKUP(A2,Sheet2!A:A,Sheet2!C:C,"Not found") | A notice if missing |
| Last occurrence | =XLOOKUP(A2,Sheet2!A:A,Sheet2!C:C,"Not found",0,-1) | The lowest row’s value |
Common Mistakes and How to Avoid Them
- ⚠️Looking up a key that has a trailing space.
- ⚠️Using approximate match on an unsorted table when the case calls for sorting.
Frequently Asked Questions
Why does my Excel not recognise XLOOKUP?
XLOOKUP has existed since Excel 2021 and Microsoft 365. In Excel 2019 or earlier it shows #NAME?; there you are better off with VLOOKUP or the INDEX and MATCH pair.
How do I return several columns with one formula?
Choose the several-columns option and give a wide range, for example B:D. In Microsoft 365 the results spill by themselves into the cells to the right.
How do I search with two conditions?
The two-criteria option joins both columns with a separator and looks for the pair. It works with no helper columns and is handy when a customer repeats across branches.
What I would do if I were in your shoes
«A lookup that returns the wrong value is worse than one that fails: always test a key that does not exist.» — DataKorex Engineering Team.