Everything you ever wanted to know, explained in plain human terms, with real examples, and without making you cry in front of an Excel spreadsheet.
Every year, millions of people open Excel, stare at two separate tables, and think: “there must be a way to connect these things without copying them one by one.” There is. It’s called VLOOKUP. And for a few years now there’s also XLOOKUP, which does the same thing but without the embarrassing flaws. This is the guide nobody ever made you read before leaving you alone with a #N/A error and a ruined Tuesday afternoon.
VLOOKUP: the classic that never goes out of style (but throws tantrums)
VLOOKUP stands for Vertical Lookup. In practice: you give it a value, it goes and finds it in a column of a table, and returns the corresponding value from another column in the same row. Like a waiter who looks up your name on the reservation list and tells you which table you’re sitting at.
Simple in theory. In practice, as we’ll see, that waiter sometimes says #N/A and disappears into the kitchen.
The full syntax
VLOOKUP(lookup_value; table_array; col_index_num; [range_lookup])| Parameter | What it does | Example |
|---|---|---|
| lookup_value – required | The value you want to look up. Can be text, a number, or a cell reference. | A2 (the cell containing the employee code) |
| table_array – required | The table to search in. Important: the lookup column MUST be the first column (leftmost) of this selection. | B2:E500 or HRTable[#All] |
| col_index_num – required | The column number within the table from which to return the result. If the table has 4 columns and you want the third, type 3. | 3 (returns the value from the 3rd column) |
| range_lookup – optional | FALSE = exact match (almost always what you want). TRUE = approximate match (useful for brackets, e.g. taxes, score ranges). Default: TRUE, so always type FALSE if you’re not sure. | FALSE |
Classic trap
The range_lookup parameter defaults to TRUE. This means that if you forget it, Excel will perform an approximate match. On unsorted data, this produces wrong results without warning you. No error. Silently. Like a colleague who misunderstood the instructions but won’t admit it.
Basic VLOOKUP example
VLOOKUP(A2; SalaryTable; 3; FALSE)
→ Looks up the code in A2 in the first column of SalaryTable, returns the value from the 3rd columnPro Tip
Always use structured table references (Table1[Column]) instead of hard-coded ranges (B2:E500). If you add rows to the table, the formula updates automatically. If you use a hard-coded range, you’ll have to remember to update it manually — and you will definitely forget at the worst possible moment.
XLOOKUP: the younger sibling who turned out better
XLOOKUP arrived in 2019 with Microsoft 365 and fixed virtually all of VLOOKUP’s structural problems. It’s not just “a newer VLOOKUP” — it’s a completely redesigned function that works differently, more flexibly, and more intuitively.
If VLOOKUP is the waiter who searches the list from top to bottom and can only look to the right, XLOOKUP is the maître d’ who can look anywhere, tell you if you’re not on the list without making a scene, and even search starting from the end.
The full syntax
XLOOKUP(lookup_value; lookup_array; return_array; [if_not_found]; [match_mode]; [search_mode])| Parameter | What it does | Example |
|---|---|---|
| lookup_value – required | The value you want to find. Identical to VLOOKUP. | A2 |
| lookup_array – required | The column (or row) to search in. Doesn’t have to be the first one. Can be any column in the table. | ClientsTable[ID] |
| return_array – required | The column (or row) to return the result from. Completely separate from the lookup column. Can be to the left, to the right, anywhere. | ClientsTable[ClientName] |
| if_not_found – optional | What to return if the value is not found. If omitted, returns #N/A. With this parameter, you can return custom text, zero, or anything else. | "Not found" or 0 |
| match_mode – optional | 0 = exact match (default). -1 = exact or next smaller. 1 = exact or next larger. 2 = with wildcard characters (*, ?). | 0 |
| search_mode – optional | 1 = first to last row (default). -1 = last to first (useful if you want the most recent occurrence). 2 or -2 = binary search (on sorted data, much faster). | -1 (to find the most recent row) |
Pro Tip
The if_not_found parameter alone is worth switching to XLOOKUP. The old IFERROR(VLOOKUP(...); "Not found") is verbose and repeats the formula twice. With XLOOKUP you write it once, clean, readable — and your colleagues will look at you with respect.
XLOOKUP(A2; ClientsTable[ID]; ClientsTable[ClientName]; "Client not registered")
→ Looks up A2 in the ID column, returns the ClientName. If not found: "Client not registered"When to use one and when to use the other: 4 real examples
The short answer: always use XLOOKUP if your Excel supports it. But life is complicated, colleagues use older versions of Office, and sometimes there are legitimate reasons why VLOOKUP is still the right choice. Here they are.
VLOOKUP · Limitations
- The lookup column must be the first one (leftmost)
- References columns by number, not by name
- No native error handling
- Cannot search right to left
- Cannot find the last occurrence
XLOOKUP · Advantages
- Search in any column, return from any column
- Uses column names, not arbitrary numbers
- Built-in error handling with if_not_found
- Searches in both directions
- Finds the last occurrence with search mode -1
Example 1 · Library · VLOOKUP wins
File shared with colleagues on Excel 2016
The librarian has a catalogue with 8,000 books: ISBN code in column A, title in B, author in C, section in D. She needs to create a file that her part-time colleague — who uses Excel 2016 on the desktop computer in the basement — can open and use without errors.
XLOOKUP doesn’t exist in Excel 2016. If the librarian uses XLOOKUP, the colleague sees nothing but a #NAME? error on every cell. A potential diplomatic incident between floors. VLOOKUP wins here, full stop.
Solution · VLOOKUP (compatibility)
VLOOKUP(A2; Catalogue!A:D; 2; FALSE)
→ Looks up the ISBN in A2, returns the title (column 2). Works on any version of Excel.Pro Tip
If you’re not sure which version of Excel your colleagues are using, go to File → Save As and check for a compatibility warning. Or just ask them directly. It’s socially acceptable.
Example 2 · Medical Practice · XLOOKUP wins
The lookup column is not the first one
The administrator at a medical practice has a patient table: surname in column A, first name in B, tax ID in C, assigned doctor in D, date of last visit in E. She needs to look up patients by tax ID (column C) and return the doctor (column D).
With VLOOKUP, the lookup column must be the first one. The tax ID is in column C. So she would have to move the columns around — probably breaking other references — or use the classic INDEX+MATCH trick. Or simply use XLOOKUP, which doesn’t have this problem.
Solution · XLOOKUP (search on any column)
XLOOKUP(F2; PatientsTable[TaxID]; PatientsTable[AssignedDoctor]; "Patient not found")
→ Looks up the tax ID in F2 in the TaxID column, returns the doctor. No need to move columns.Example 3 · School · XLOOKUP wins
Finding the last occurrence (the most recent grade)
The school office keeps a grade register with thousands of rows: every time a teacher enters a grade, they add a row. The same student has dozens of rows. The principal wants to know the last grade entered for each student — i.e. the most recent one, which is the lowest occurrence in the table.
VLOOKUP always finds the first occurrence from the top. There is no native way to make it find the last one. With XLOOKUP, just set the search mode to -1: it searches from the last row upwards, so it finds the most recent occurrence.
Solution · XLOOKUP (last occurrence)
XLOOKUP(A2; GradeRegister[StudentID]; GradeRegister[Grade]; "No grade"; 0; -1)
→ Mode -1: searches from the last row. Returns the student's most recent grade.Pro Tip
The -1 mode in XLOOKUP is also useful in help desks and warehouses to find the latest transaction or the most recent status update on a ticket or order. Whenever you have time-based data with duplicates and want the latest entry, think XLOOKUP with mode -1.
Example 4 · Shop / Point of Sale · VLOOKUP wins
Simplified tables with a fixed structure
The store manager has a price table with product code in column A and price in column B. Two columns. He’s used it for years. The structure never changes. He sends it every week to three branches, all of which run different versions of Excel — including a tablet running Excel Mobile.
In this case VLOOKUP is perfectly adequate and universally compatible. There’s no column constraint to work around, no need for advanced error handling, no reverse search. Using XLOOKUP here adds nothing and risks breaking the file on older devices. Simplicity wins.
Solution · VLOOKUP (simplicity and compatibility)
VLOOKUP(A2; PriceList!A:B; 2; FALSE)
→ Looks up the product code, returns the price. Works everywhere, including Excel Mobile.“The right function isn’t always the newest one. It’s the one that solves the problem without creating new ones.”
XLOOKUP and VLOOKUP inside other functions: 3 examples
So far we’ve used lookup functions on their own. But Excel becomes truly powerful when you start using them as ingredients inside larger formulas. The looked-up value becomes the input for a calculation, a condition, or another function. These three situations will almost certainly come up for you.
Nesting 1 · HR Office · IF + XLOOKUP
Conditional bonus calculation based on employee level
The HR department needs to calculate the end-of-year bonus. The rule: if the employee’s level (retrieved from the HR table) is “Senior”, a 15% bonus is applied to the base salary. Otherwise, no bonus.
A naive approach: first make a column with XLOOKUP to retrieve the level, then make another column with IF that reads that column. Result: two columns, double the work, double the chance of making a mistake.
The correct approach: use XLOOKUP directly as an argument of IF. The IF function evaluates the result of XLOOKUP without you needing to see it in a separate cell.
Formula · IF(XLOOKUP(...))
IF(
XLOOKUP(A2; HRTable[ID]; HRTable[Level])= "Senior";
B2 * 1.15;
B2
)
→ If the level found is "Senior" → salary + 15%. Otherwise → salary unchanged.The formula updates automatically every time the HR table is modified. No manual copy-pasting. No helper columns. No Tuesday afternoon spent updating things by hand.
Pro Tip
This pattern — IF(XLOOKUP(...)="value"; action_if_true; action_if_false) — is one of the most useful patterns in all of Excel. Learn it by heart. Use it every time you need to make a decision based on data that lives in another table.
Nesting 2 · Pharmacy / Warehouse · SUMIF + VLOOKUP
Summing stock for all products belonging to a category
The pharmacy warehouse manager has a stock table with product code, quantity, and category. He needs to create a summary showing the total quantity for each category. But he doesn’t have the category directly in the right column — he needs to retrieve it from the product table for each item.
The solution is to use VLOOKUP as the criteria argument in SUMIF, or — even more elegantly — build a helper column with VLOOKUP and then use it as the criteria. Here we show the helper column approach, which is more readable and less error-prone.
Helper column · Retrieving the category
VLOOKUP(A2; ProductTable!A:C; 3; FALSE)
→ Retrieves the category for each product code. You now have the category in column D.Final formula · SUMIF on the category column
SUMIF(D:D; "Painkillers"; B:B)
→ Sums all quantities (column B) where the category (column D) is "Painkillers".Result: a dynamic category summary that updates every time the stock or product table changes. The warehouse manager no longer has to calculate anything by hand and can stop using the phone calculator he keeps hidden under the counter.
Nesting 3 · Help Desk · TEXT + XLOOKUP
Building automatic messages with data pulled from a table
The help desk operator needs to automatically generate a summary text for each ticket: “Ticket #1042 — Client: John Smith — Priority: High — Assigned to: Julia M.” The ticket ID is in column A, all other data is in a separate table.
The solution is to use multiple XLOOKUPs inside a CONCATENATE function (or directly with the & operator), building the string piece by piece with data retrieved from the table.
Formula · Building a message with nested XLOOKUP
"Ticket #"&A2&" — Client: "&
XLOOKUP(A2; Tickets[ID]; Tickets[Client]) &
" — Priority: "&
XLOOKUP(A2; Tickets[ID]; Tickets[Priority]) &
" — Assigned to: "&
XLOOKUP(A2; Tickets[ID]; Tickets[Operator])
→ Result: "Ticket #1042 — Client: John Smith — Priority: High — Assigned to: Julia M."Pro Tip
This approach is the foundation for building email templates, automated reports, or printable labels directly from Excel. Every time you find yourself copying data by hand to fill in the same text over and over, think of this pattern.
Other functions inside XLOOKUP: 3 examples
Now let’s go the other way: functions nested inside XLOOKUP. Instead of providing a static value to look up, you provide the result of a formula. This allows you to perform dynamic lookups, transform data before searching, or calculate the lookup value on the fly.
Inside LOOKUP 1 · Warehouse / Logistics · XLOOKUP with NETWORKDAYS
Calculating the expected delivery date based on supplier lead time
The logistics manager has a supplier table with supplier ID and lead time in days. For each order he wants to calculate the expected delivery date: order date + supplier lead time, skipping weekends and public holidays. All in a single cell, with no helper columns.
The solution: nest XLOOKUP as an argument of WORKDAY, which calculates a future working date by adding a number of working days to a start date. The days are pulled dynamically from the supplier table via XLOOKUP.
Formula · WORKDAY(date; XLOOKUP(supplier))
WORKDAY(
B2; ← order date
XLOOKUP(A2; Suppliers[ID]; Suppliers[LeadDays]; 0)
)
→ Adds the supplier's lead time working days to the order date. If the supplier doesn't exist: 0 days added.The if_not_found = 0 parameter of XLOOKUP ensures that new suppliers not yet in the table don’t generate errors — the delivery date simply matches the order date, implicitly signalling that data is missing.
Pro Tip
WORKDAY accepts an optional third parameter: a list of public holidays. If you have a column with national holidays (Christmas, bank holidays, etc.), add it there and the function will automatically skip them in its calculations.
Inside LOOKUP 2 · Sales Office · XLOOKUP with UPPER and TRIM
Finding a client even when the name has been typed incorrectly
The sales rep receives emails with client company names written in different ways: sometimes all caps, sometimes with extra spaces, sometimes with mixed capitalisation. The CRM table stores names in uppercase with no extra spaces. A direct lookup fails because "mario srl" ≠ "MARIO SRL".
The solution is to normalise the lookup value before passing it to XLOOKUP, using UPPER to convert everything to uppercase and TRIM to remove extra spaces. Both functions are nested as the first argument of XLOOKUP.
Formula · XLOOKUP(UPPER(TRIM(...)))
XLOOKUP(
UPPER(TRIM(A2));
CRM[ClientName];
CRM[SalesRep];
"Client not found in CRM"
)
→ Normalises the text in A2 before searching. " mario srl " becomes "MARIO SRL" and is found.Watch out
This only works if the data in the lookup column of the CRM table is also in uppercase with no extra spaces. If the data there is inconsistent too, you’ll need to apply the same normalisation to the lookup column as well — and at that point it’s better to clean the data at the source rather than doing acrobatics with formulas.
Inside LOOKUP 3 · School · XLOOKUP with TODAY and date comparison
Automatically finding the teacher on duty based on today’s date
The school office has a table with supervision duty dates and the name of the teacher assigned to each date. Every morning someone opens the file and wants to immediately know who is on duty today, without manually searching through the table.
The solution is to use the TODAY() function as the lookup value inside XLOOKUP. TODAY() always returns the current date, so the formula automatically finds the teacher on duty for the day the file is opened. Zero manual interaction.
Formula · XLOOKUP(TODAY(); ...)
XLOOKUP(
TODAY();
Duties[Date];
Duties[TeacherOnDuty];
"No duty scheduled for today"
)
→ Every time the file is opened, it automatically shows the teacher on duty for today.The if_not_found parameter handles weekends and unscheduled days without generating errors — essential if the file gets opened on a Saturday by an anxious deputy head.
Pro Tip
This same pattern — XLOOKUP(TODAY(); ...) — works for any table with dates: seasonal opening hours, prices that change by period, resource availability by date. It’s one of the most underrated and most useful patterns in Excel.
Summary: what to take away
If you had to explain all of this to a colleague in three minutes by the coffee machine, here’s what you’d say:
VLOOKUP is stable, universal, and works on any version of Excel. It has two major limitations: the lookup column must be the first one, and it doesn’t handle errors natively. It’s perfectly fine for simple tables and files shared with people using older versions of Excel.
XLOOKUP is the modern version without those limitations. It searches in any column, handles missing values with the if_not_found parameter, can search from the last row, and is much easier to read. Use it whenever your Excel supports it (Microsoft 365 and Excel 2021 onwards).
Nesting: both functions become far more powerful when you combine them with IF, SUMIF, TEXT, CONCATENATE as outer functions, or with TODAY, UPPER, TRIM, WORKDAY as inner arguments. Learn the patterns, not the individual formulas.
And the next time you see #N/A, don’t kick the computer. First check whether there’s an invisible space. It almost always is. Almost always.
Final Pro Tip
Select a cell with the value that isn’t being found, press F2 to enter edit mode, and look carefully at the edges of the text: if the cursor starts slightly to the right of the first character, there’s a leading space. Use TRIM and live happily ever after.