Webbo3 Data Analytics · Cohort 2 · Excel Track · Week 4, Live 1
VLOOKUP, XLOOKUP, INDEX and MATCH, and Lookup Errors
The workbook has three tabs of data, and they share codes. Today you pull a value from one tab into another, see exactly how the old way breaks, and use the new way that does not.
By the end of this class you can
- Explain what a lookup does, using a phone's contact list
- Pull a brand from the Product Catalogue with VLOOKUP, and say what each argument means
- Show two ways VLOOKUP breaks: an inserted column, and a value to the left
- Use XLOOKUP as the fix, including its built-in "not found" message
- Use INDEX and MATCH on older versions of Excel
- Handle #N/A with IFERROR, without hiding dirty data
Every sale in Sales Data carries a Product_ID, such as PRD-015. The Product Catalogue tab has one row per product ID: its name, category, brand, price, VAT and warranty. Nothing is copied between the two. A lookup is how one tab reads the other.
1. What a Lookup Is
Definition
A lookup finds a value in one column of a table and returns the value from another column on the same row. You give it something you know (a product ID) and it hands back something you need (that product's brand).
Real-Life Scenario
Your phone's contacts. You type "Mama" (what you know) and it shows her number (what you need). You do not scroll and read; the phone finds the row and hands back the other column. If you type "Mamma", it finds nothing: a lookup needs an exact match.
Why Not Just Type the Brand Into Sales Data?
Because the catalogue is maintained in one place. When a price changes, it changes once, on the Product Catalogue tab, and every lookup picks it up. Typing details into a thousand rows is how a business ends up selling at last year's price, and how names end up spelt ten different ways, as you saw in Week 2.
Three Ways to Look Something Up
| Function | Available in | In one line |
|---|---|---|
| VLOOKUP | Every version | Look in the first column of a table, return the column you count to |
| XLOOKUP | Excel 2021, Microsoft 365, Excel for the web | Look in one column, return another, by pointing at it |
| INDEX + MATCH | Every version | MATCH finds the row number; INDEX returns the value at that row |
2. VLOOKUP
Definition
VLOOKUP(lookup_value, table_array, col_index_num, range_lookup). Look for lookup_value in the first column of table_array, go across to column number col_index_num of that table, and return what is there. The V is for vertical: it searches down a column.
| Argument | For a product's brand | Meaning |
|---|---|---|
| lookup_value | C2 | The Product_ID on this sale |
| table_array | 'Product Catalogue'!$A$2:$G$21 | The whole catalogue, locked with $ so it does not move when filled. The tab name is in single quotes because it has a space in it |
| col_index_num | 4 | Return the 4th column of the table: Brand |
| range_lookup | FALSE | Exact match only. Always FALSE in this track |
Brand goes in column P, the first empty column after Zone.
Every sale now has its real brand, all 1,050 of them. Look for an iPhone 14 sale: its brand says Apple. In Week 2, cutting the first word off the product name could only ever say "iPhone". A lookup reads what is true; cutting text reads what happens to be written.
Leave the last argument out and VLOOKUP does an approximate match, which assumes the first column is sorted and can quietly return a different product's details. FALSE means "exact match or nothing".
How VLOOKUP Breaks, 1: Someone Inserts a Column
Procurement adds a Supplier column to the Product Catalogue, between Category and Brand. Excel stretches your table_array to include it, but the 4 in your formula is just a number. It still says "the fourth column", and the fourth column is now Supplier.
No error. No warning. Every brand has become a supplier's name. If this column fed a report by brand, the report would now be about suppliers, and nobody would notice until a manager asked why Apple had vanished.
How VLOOKUP Breaks, 2: The Answer Is to the Left
A manager asks: "what is the Product_ID of the Dell Inspiron 15?" Now you know the name and need the ID. But VLOOKUP only searches the first column of its table and only returns columns to its right. Product_Name is column B, Product_ID column A. VLOOKUP cannot do it without rearranging the catalogue.
Picture giving someone directions by counting: "the fourth door on the left". It works until someone builds a new door in the corridor. Then they walk confidently into the wrong room.
3. XLOOKUP: The Fix
Definition
XLOOKUP(lookup_value, lookup_array, return_array, if_not_found). Look for lookup_value in lookup_array and return the matching value from return_array. You point at the column to return instead of counting to it. It does an exact match unless you ask for anything else.
Real-Life Scenario
Directions by name instead of by counting: "the door marked Accounts". Build as many new doors as you like; Accounts is still Accounts.
The same brands. Now insert the same Supplier column. Excel moves the return range along with the Brand column, from D to E, because it points at the column itself:
Still brands. You did not touch the formula: Excel updated $D to $E for you when the column was inserted, because the reference pointed at the Brand column, not at "column 4".
Looking to the Left
PRD-007. XLOOKUP does not care which side the answer is on.
| VLOOKUP | XLOOKUP | |
|---|---|---|
| How you choose the result | A column number you count | The column itself |
| A column inserted in the table | Returns the wrong column, silently | Keeps working |
| Result to the left | Impossible | Fine |
| Exact match | Only if you remember FALSE | By default |
| Not found | #N/A | #N/A, or your own message as the 4th argument |
Picture the door marked Accounts. New doors appear, the corridor is repainted, but you walk straight to the right room every time because you were never counting.
4. INDEX and MATCH, for Older Excel
Definition
Two functions working as a pair. MATCH(lookup_value, lookup_array, 0) returns the position of the value in the list (the 0 means exact match). INDEX(array, position) returns the value at that position. Together they do what XLOOKUP does, and they work in every version of Excel.
Real-Life Scenario
A cinema: MATCH reads your ticket and says "seat 7"; INDEX walks along the row and points at seat 7.
PRD-015 is item 15 of the catalogue, and INDEX returns item 15 of the Brand column. The same brands. Like XLOOKUP, it points at columns rather than counting them, so an inserted column does not break it. If your Excel has XLOOKUP, use XLOOKUP; if not, this is your tool.
5. When the Lookup Finds Nothing
Definition
When the value is not in the lookup column, VLOOKUP, XLOOKUP and MATCH all return #N/A: "not available". There are two very different reasons, and they need different fixes.
Real-Life Scenario
You search your contacts for a name and get nothing. Either you never saved that person (a genuinely new contact), or you typed the name wrongly (a mistake to fix). The phone shows the same empty screen for both.
Four new sales arrive on a sheet called New Sales. Look carefully at their Product_IDs:
Two #N/A results, for two different reasons. Row 3: PRD-021 is a new product that is not in the catalogue yet. Row 4: PRD-017 is in the catalogue, but this cell has a space after the ID (the red dot), so it does not match exactly.
Fix 1: A Clear Message for Genuinely Missing Items
On older Excel, IFERROR(formula, value) does the same for VLOOKUP or INDEX/MATCH: it returns the formula's result, or the value you give if the formula produces any error. For example =IFERROR(VLOOKUP(B2,'Product Catalogue'!$A$2:$G$21,5,FALSE),"Not in catalogue").
Fix 2: Clean the Data, Do Not Hide It
Row 4 now says "Not in catalogue", which is false: PRD-017 is the JBL Bluetooth Speaker. The real problem is the trailing space, and the real fix is TRIM, from Week 2. Use the message for items that are genuinely missing; fix the data when the error comes from dirt.
Now the speaker finds its price and only PRD-021, which really is missing, says so. That message is now a to-do item: ask procurement to add PRD-021 to the catalogue.
Picture two people missing from your contacts: one you never saved, one you saved as "Mamma". You add the first; you fix the spelling of the second. You would never solve both by hiding the empty screen.
Overall Example: A Price Check on Every Sale
In Week 1 you spot-checked five prices against the catalogue by eye. Now check all of them at once:
All 1,050 sales say OK: every sale was charged exactly the catalogue price. That is the accuracy check from Week 1, finished properly. And a product missing from the catalogue? XLOOKUP returns the text "Not in catalogue", which does not equal G2, so the row says "Price differs": good enough to flag it for a person to look at. Delete column P when you finish.
Practical Exercises
Exercise 1: Very Easy
Use XLOOKUP to fetch each sale's Warranty_Months from the catalogue.
Exercise 2: Simple Application
Write the same lookup with VLOOKUP. Which col_index_num do you need?
Exercise 3: Combination
Use XLOOKUP with the price 18000 to find which product costs N18,000.
Exercise 4: A Small Real-World Problem
On the New Sales sheet, add a Line_Total column: Quantity times the looked-up price, showing "Check" instead of a number when the product is not in the catalogue.
Answers to the exercises
Exercise 1: =XLOOKUP(C2,'Product Catalogue'!$A$2:$A$21,'Product Catalogue'!$G$2:$G$21). Exercise 2: =VLOOKUP(C2,'Product Catalogue'!$A$2:$G$21,7,FALSE). Exercise 3: Logitech Wireless Mouse. Exercise 4: =IFERROR(C2*XLOOKUP(TRIM(B2),'Product Catalogue'!$A$2:$A$21,'Product Catalogue'!$E$2:$E$21),"Check").
Checkpoint
Q1. In =VLOOKUP(C2,'Product Catalogue'!$A$2:$G$21,4,FALSE), what does the 4 mean?
Return the fourth column of the table, counted from the left of the table: Brand.
Q2. Why does VLOOKUP return supplier names after a column is inserted?
The table grows to include the new column, but the column number stays 4, which is now Supplier.
Q3. Why does XLOOKUP survive the same insertion?
It refers to the return column itself, and Excel moves that reference when columns are inserted.
Q4. Why is the tab name written in single quotes?
Because "Product Catalogue" has a space in it.
Q5. A lookup returns #N/A because of a trailing space. Should you use IFERROR?
No. Clean it with TRIM. IFERROR would hide a fixable problem behind a false message.
Tick Before You Move On
- ☐ I can write VLOOKUP with FALSE and explain all four arguments
- ☐ I have seen VLOOKUP return the wrong column after an inserted column
- ☐ I can write XLOOKUP, look to the left, and use its fourth argument
- ☐ I can write INDEX and MATCH for older Excel
- ☐ I can tell a genuinely missing item from a dirty one, and fix each correctly
- ☐ I price-checked all 1,050 sales against the catalogue
Quick Recap
A lookup finds a value in one column and returns the value beside it, often from another tab. VLOOKUP counts columns, so an inserted column silently breaks it, and it cannot look left. XLOOKUP points at the column to return: it survives inserts, looks either way and has a not-found message. INDEX and MATCH do the same job in any version of Excel. #N/A means not found: give a message for genuinely missing items, and clean the data for dirty ones.
Final Scenario
In March, TechPlug's procurement team adds a Supplier column to the Product Catalogue. In April, the monthly report by brand says TechPlug sold nothing from Apple, Samsung or HP.
You open the report and click a brand cell: =VLOOKUP(C2,'Product Catalogue'!$A$2:$H$21,4,FALSE). The 4 now points at Supplier, so every "brand" is a supplier's name. You replace it with an XLOOKUP pointing at the Brand column, and the report is right again. Next time someone inserts a column, nothing will break.
Point at what you want. Never count to it.
Next, Week 4, Virtual 1: lookups into the Staff Directory on your own (zone and manager), and a Data Validation dropdown that stops anyone typing a category that does not exist.