Key Excel Functions · XLOOKUP and VLOOKUP
Excel Lookup Functions (XLOOKUP, VLOOKUP, INDEX/MATCH)
What are core Excel lookup functions?
- A lookup function links two tables through one key value
- XLOOKUP is convenient but may not be available in every version
- Most errors come from the state of the key value, not the function
Learning lookup functions does not mean memorizing syntax. Most Excel work for HR and training managers is linking two tables through a single key value: attaching training completion records using the employee ID from the HR roster, filling in department and job grade on an attendee list, or pulling evaluation grades into a salary-contract table. Copy and paste by eye, and at a scale of 300 people a few rows will inevitably be off. Lookup functions are a tool for accuracy more than for speed.
VLOOKUP takes the form =VLOOKUP(lookup value, table range, column number, 0) and searches the leftmost column of the table and returns a value from the nth column to the right. If the last argument isn’t set to 0 (FALSE), it runs an approximate match and returns a similar value. If you insert a column in the middle of the table, the column number shifts and returns the wrong value. INDEX/MATCH takes the form =INDEX(return column, MATCH(lookup value, lookup column, 0)) and specifies the lookup column and the return column separately, so it can look up to the left and holds up when columns are inserted.
XLOOKUP takes the form =XLOOKUP(lookup value, lookup range, return range, value to show if not found). Exact match is the default, there is no direction restriction, and you set the not-found message inside the function. However, it works only in Microsoft 365 and relatively recent versions, so first check the Excel version of the person receiving the file. Opened in an older version, the formulas turn into errors. If you exchange files with several companies, INDEX/MATCH is the safest common denominator.
In practice, the most common failure is not the function but the state of the key value. If an employee ID is stored as a number in one table and as text in the other, or a name has a trailing space, any function returns 'not found.' If you use names as the key, the lookup returns only the first person among people with the same name, with no error shown. Use a unique value such as an employee ID as the key, and get in the habit of checking the error count and total row count of the result against the source. That matters more than which function you choose.
Jaryong KooMarketing PhD and authority in AI, data analytics, and personal branding
John KimAuthor of B2B Marketing Design and CEO of Nanum Consulting| Category | VLOOKUP | INDEX/MATCH | XLOOKUP |
|---|---|---|---|
| Basic form | =VLOOKUP(lookup value, table range, column number, 0) | =INDEX(return column, MATCH(lookup value, lookup column, 0)) | =XLOOKUP(lookup value, lookup range, return range) |
| Lookup direction | Only to the right of the lookup column | Either direction | Either direction |
| Default match mode | Approximate match (enter 0 for an exact match) | Specify exact match with 0 in MATCH | Exact match |
| When a column is inserted | Column numbers can shift and break the result | Low impact | Low impact |
| When no match is found | Shows an error (must be wrapped in IFERROR) | Shows an error (must be wrapped in IFERROR) | Custom text set via the fourth argument |
| Supported versions | Nearly all versions | Nearly all versions | Microsoft 365 and other recent versions |
VLOOKUP has been used since the early days of spreadsheets, and the INDEX and MATCH combination became a common workaround for its limits. Microsoft added XLOOKUP later to fix the pain points of both, so it may be unavailable depending on your Excel version.
Consider a hypothetical case. The training team at Company A, a food manufacturer with 680 employees, had to merge the HR roster with 2,300 LMS completion records ahead of the year-end training report. The previous year, an assistant manager spent two days copying and pasting, and after the report went out, the completion rates for 3 departments turned out to be wrong.
- A training team associate looked up departments by name from the completion records, and all 11 employees who shared a name were filled in with the first person’s department. The team lead said, "Let’s redo it by employee ID instead of name."
- When we switched to employee IDs, 140 records came back as 'not found.' The IDs downloaded from the LMS were stored as text, and after converting them to numbers, the errors dropped to 6.
- The remaining 6 were employees who left during the year. The assistant manager entered "Confirm resignation" as the fourth argument of XLOOKUP so that a reason would appear instead of a blank cell.
- After the file was sent to an affiliate’s HR team, the reply came back: "All the formulas show errors." Their copy of Excel was an older version, so the assistant manager rebuilt the formulas with INDEX/MATCH and sent it again.
- The team lead had a check cell placed at the top of the sheet that compares the lookup result count against the source count. After confirming that all 2,300 records matched on both sides, the team passed the file on for the report.
- Compilation time dropped from two days to 3 hours, and post-report correction requests went from 3 to 0. The same file was reused as-is for the next quarter’s results.