Purpose
By the end of this lesson, you will be able to retrieve data from another table using VLOOKUP, INDEX/MATCH, and XLOOKUP, and choose the right one for the situation.
Lesson Explanation
VLOOKUP searches for a value in the first column of a range and returns a value from a specified column to the right: =VLOOKUP(A2,Prices!A:C,3,FALSE) looks up the value in A2 within the first column of the “Prices” tab, and returns the value from the third column of that range. The final FALSE requests an exact match, which is almost always what business work requires.
VLOOKUP‘s major limitation: it can only look to the right of the column it searches, and it breaks if a column is inserted into the lookup range. INDEX/MATCH solves both problems: =INDEX(C:C,MATCH(A2,A:A,0)) finds the row where A2 matches in column A, then returns the value from that same row in column C – and it works in either direction.
XLOOKUP, available in newer versions of Excel, combines the simplicity of VLOOKUP with the flexibility of INDEX/MATCH: =XLOOKUP(A2,A:A,C:C) looks up A2 in column A and returns the matching value from column C, searching in either direction and handling missing matches more gracefully with an optional “not found” message.
Practice Questions
1. Write a VLOOKUP formula that looks up the value in cell A2 within the first column of the range Products!A:D, returning the value from the fourth column, with an exact match required.
View Answer
=VLOOKUP(A2,Products!A:D,4,FALSE)2. A VLOOKUP formula needs to find a value in a column that is to the LEFT of the column containing the lookup data. Will VLOOKUP work for this directly? What should be used instead?
View Answer
No – VLOOKUP can only look to the right. INDEX/MATCH or XLOOKUP should be used instead, since both can search and return values in either direction.
3. Write an INDEX/MATCH formula that finds the row where A2 matches in column A of a dataset, and returns the value from column E of that same row.
View Answer
=INDEX(E:E,MATCH(A2,A:A,0))4. A colleague inserts a new column into the middle of a VLOOKUP‘s lookup range, and every formula using that range starts returning wrong values. What caused this, and how would using INDEX/MATCH instead have prevented it?
View Answer
VLOOKUP uses a fixed column NUMBER (like 3) to find the return value, so inserting a column shifts what that number now points to. INDEX/MATCH references the actual return column directly, so it isn’t affected by columns inserted elsewhere.
5. Write an XLOOKUP formula that looks up A2 in column A and returns the corresponding value from column D.
View Answer
=XLOOKUP(A2,A:A,D:D)6. What does the final FALSE argument do in a VLOOKUP formula, and why does this lesson describe it as “almost always” needed in business work?
View Answer
It requires an exact match rather than an approximate one; approximate matches (the default if omitted or set to TRUE) can silently return the wrong row’s data, which is rarely what a business lookup actually needs.
7. A VLOOKUP formula returns the error #N/A. Name one likely cause.
View Answer
The lookup value doesn’t exist in the first column of the lookup range (or there’s a mismatch like extra spaces or different text formatting between the two values being compared).
8. A report needs to look up an employee ID in one table and return that employee’s name, which happens to be in a column to the LEFT of the ID column. Write a formula (using INDEX/MATCH) that accomplishes this, assuming IDs are in column B and names are in column A.
View Answer
=INDEX(A:A,MATCH(lookup_value,B:B,0))9. Why might XLOOKUP be preferred over VLOOKUP in a newer Excel file, even for a simple left-to-right lookup?
View Answer
It offers the same simplicity as VLOOKUP for straightforward cases, while also being more resilient to inserted columns and offering a cleaner way to handle values that aren’t found.
10. A VLOOKUP formula is written as =VLOOKUP(A2,B:D,1,FALSE). What is wrong with this formula, given that the intended lookup range starts in column B?
View Answer
The column index 1 would return the value from column B itself (the first column of the range), not a genuinely different column – this likely isn’t the intended result unless the goal really was to return the lookup value itself.
11. A dataset changes frequently, with rows being added, removed, and reordered. Which lookup approach – VLOOKUP with a hardcoded column number, or INDEX/MATCH – is generally more robust to this kind of change, and why?
View Answer
INDEX/MATCH is generally more robust, since it isn’t vulnerable to column-position shifts the way a VLOOKUP with a fixed column number is.
12. Why does this lesson recommend exact-match lookups (the FALSE argument, or XLOOKUP‘s default exact-match behavior) as the norm for business work, rather than approximate matches?
View Answer
Business data (IDs, names, categories) usually needs precise matching – an approximate match could silently return a similar but incorrect row, producing wrong figures in a report without any visible error.