Crashes, No Sound, or Screen Glitches?
Random freezes, missing sound and display glitches usually trace back to one bad driver. Find and replace yours safely.Free scan · under a minuteWindows Errors? Fix Them Before They Spread
Repair common Windows errors and clear accumulated junk for a smoother, more stable PC - no reinstall needed.Free scan · no reinstalliTechGuides is reader-supported. When you buy through links on our site, we may earn an affiliate commission. As an Amazon Associate I earn from qualifying purchases. Learn more
Excel lookup functions find a key—such as an employee ID, product code or invoice number—in a range and return a related value, position or reference. For most new formulas, use XLOOKUP when your Excel version supports it; use VLOOKUP for older-compatible workbooks, and use INDEX with MATCH or XMATCH when position-based flexibility is useful.
What does a lookup function do?
A lookup connects two pieces of related data. Suppose E2 contains P-102 and your product table has IDs in column A and prices in column C. A lookup searches for that ID, identifies its row, and returns the corresponding price.
| Product ID | Product | Price |
|---|---|---|
| P-101 | Keyboard | 49.99 |
| P-102 | Mouse | 24.99 |
In this example, E2 is the lookup value (or key), A2:A3 is the lookup range, and C2:C3 is the return range.
=XLOOKUP(E2,A2:A3,C2:C3,"Not found")Microsoft groups these tools under Lookup and reference functions. In everyday spreadsheet language, the term also includes combinations such as INDEX plus MATCH, and sometimes FILTER for returning multiple records.
Excel lookup functions at a glance
| Function or pattern | What it returns | Best use | Important limitation |
|---|---|---|---|
XLOOKUP |
A corresponding value or range | Most new exact or approximate lookups | Not natively available in Excel 2016 or 2019 |
VLOOKUP |
A value from a column in the same row | Simple vertical lookups in legacy workbooks | Lookup column must be the table’s leftmost column |
HLOOKUP |
A value from a row in the same column | Tables organized horizontally | Searches only the top row |
LOOKUP |
A corresponding value | Older vector or array formulas | Generally approximate and requires sorted data |
INDEX |
A value or reference at a position | Returning a result after calculating a position | Needs a row or column position |
MATCH |
A relative position | Legacy position finding | Returns a position, not the related value |
XMATCH |
A relative position | Modern position finding, reverse searches and two-way lookups | Needs a separate return function such as INDEX |
XLOOKUP: the modern default
XLOOKUP searches one range and returns the corresponding item from another. Its syntax is:
=XLOOKUP(lookup_value,lookup_array,return_array,[if_not_found],[match_mode],[search_mode])lookup_value: the key to find.lookup_array: the range or array to search.return_array: the range or array that supplies the result.if_not_found: optional replacement for#N/A.match_mode: exact, approximate or wildcard matching.search_mode: search direction or binary search.
Exact matching is the default, so an ID lookup can be written as:
=XLOOKUP(B2,E2:E100,F2:F100,"No match")Unlike VLOOKUP, XLOOKUP can return values to the left or right of the lookup range:
Recommended Free Tools
=XLOOKUP(E2,B2:B100,A2:A100,"Not found")It can search from the bottom upward, which is useful when the last record is wanted:
=XLOOKUP(F2,A2:A100,B2:B100,"Not found",0,-1)For thresholds, -1 means exact match or next smaller item, and 1 means exact match or next larger item:
=XLOOKUP(E2,A2:A100,C2:C100,, -1)Wildcard matching uses match mode 2, for example =XLOOKUP(E2,A2:A100,C2:C100,,2). Search modes 2 and -2 use binary search on ascending or descending sorted data; incorrect sorting can produce invalid results. See Microsoft’s XLOOKUP documentation for the complete argument behavior.
Version availability
XLOOKUP is available natively in Microsoft 365, Excel 2021, Excel 2024 and Excel for the web. Microsoft explicitly notes that it is not natively available in Excel 2016 or Excel 2019; those versions may open a workbook containing an XLOOKUP formula created elsewhere, but cannot be relied on to create or calculate it normally.
What’s actually slowing this PC down?
Pick the symptom - the matching free tool is one click away.
VLOOKUP: the familiar legacy option
VLOOKUP searches the first column of a table and returns a value from a column to its right.
=VLOOKUP(lookup_value,table_array,col_index_num,[range_lookup])For the product table, an exact lookup is:
=VLOOKUP(E2,$A$2:$C$100,3,FALSE)This searches for E2 in the first column of A2:C100 and returns the third column within that selected table. The dollar signs keep the table fixed when the formula is filled down.
Always specify FALSE (or 0) for IDs, codes and other exact keys. If the fourth argument is omitted, VLOOKUP uses approximate matching and assumes the first table column is sorted. The function cannot naturally look left, and a column index can become wrong after columns are inserted or deleted. Its syntax and limitations are documented by Microsoft at VLOOKUP function.
HLOOKUP and the older LOOKUP function
HLOOKUP
HLOOKUP is the horizontal counterpart to VLOOKUP. It searches the top row and returns a value from a specified row in that column.
Do these 3 things before closing this tab:
1Fix the driver behind crashes, sound loss and screen glitches2Repair Windows errors before they cause bigger problems3Scan for outdated or missing drivers - takes under a minute=HLOOKUP("March",A1:M3,3,FALSE)This searches row 1 for “March” and returns the value from row 3. For new workbooks, XLOOKUP can usually handle the same layout with separate lookup and return ranges. See Microsoft’s HLOOKUP documentation.
LOOKUP
The standalone LOOKUP function searches a one-row or one-column vector, or an array, and returns a corresponding item. It is an older, less explicit option: its usual behavior is approximate, the lookup vector generally must be sorted, and it has no dedicated not-found argument. It is therefore rarely the best choice for a new formula; do not treat it as interchangeable with XLOOKUP.
INDEX, MATCH and XMATCH
INDEX returns a position’s value
INDEX returns a value or reference at a specified row and, where applicable, column position. Details are in Microsoft’s INDEX documentation.
Rank #3
MATCH and XMATCH find positions
MATCH returns the relative position of an item:
=MATCH(E2,A2:A100,0)XMATCH is the newer position function. It defaults to exact matching and adds reverse, wildcard and approximate search modes:
Quick wins for a faster PC:
Scan for outdated or missing drivers - takes under a minuteDriver Scan →Clear out junk files and repair common Windows errorsFree Scan →=XMATCH(E2,A2:A100,0)Neither function returns the related price by itself. Pair one with INDEX:
=INDEX(C2:C100,MATCH(E2,A2:A100,0))=INDEX(C2:C100,XMATCH(E2,A2:A100,0))This pattern works when the lookup column is not to the left of the return column and is often more resilient than a hard-coded VLOOKUP column number. Microsoft explains the functions at MATCH and XMATCH.
Two-dimensional lookup
When both a row label and a column heading identify the result, use two position calculations:
=INDEX(B2:Z100,XMATCH(H2,A2:A100,0),XMATCH(H3,B1:Z1,0))Here H2 identifies a row and H3 identifies a column; the ranges must align with the data block. A nested modern alternative is:
=XLOOKUP(H2,A2:A100,XLOOKUP(H3,B1:Z1,B2:Z100))Returning several columns or rows
In dynamic-array versions, a multi-column return range can spill adjacent results:
=XLOOKUP(E2,A2:A100,C2:E100,"Not found")The cells where the result will spill must be empty. If the requirement is all records matching a key rather than one record, use FILTER, for example:
=FILTER(B2:D100,A2:A100=F2,"No matches")How to choose a lookup function
| Requirement | First choice | Alternative |
|---|---|---|
| New exact lookup | XLOOKUP |
INDEX + XMATCH |
| Return a value to the left | XLOOKUP |
INDEX + MATCH |
| Older Excel compatibility | VLOOKUP |
INDEX + MATCH |
| Horizontal table | XLOOKUP |
HLOOKUP |
| Position only | XMATCH |
MATCH |
| Threshold or band | XLOOKUP with an explicit match mode |
VLOOKUP or LOOKUP with correctly sorted data |
| Last matching record | XLOOKUP with search mode -1 |
More complex legacy patterns |
| All matching records | FILTER |
Multiple formulas or a redesigned table |
Use exact matching for employee IDs, product codes, invoice numbers, account numbers and other identifiers. Approximate matching is intentional for tax brackets, grades, shipping bands, commissions and discount thresholds; sort the threshold column in the direction required by the chosen function.
Independent reader supportYour contribution helps us test, update, and keep practical guides available for everyone.Common lookup errors and fixes
#N/A: no match was found
Check spelling, leading or trailing spaces, hidden characters, dates stored as text, number-versus-text differences, the selected range and the match mode. MATCH is not case-sensitive, so capitalization alone normally is not the cause.
Supply a not-found result directly:
=XLOOKUP(A2,F:F,G:G,"Not found")Or handle the error around a lookup:
=IFNA(XLOOKUP(A2,F:F,G:G),"Not found")Microsoft’s troubleshooting guide lists these causes at How to correct a #N/A error.
Text, numbers and hidden spaces
"00125" (text) and 125 (number) can look similar while comparing differently. Convert deliberately with =VALUE(A2) or format a number as text with =TEXT(A2,"00000"). Remove ordinary and nonprinting characters with =TRIM(CLEAN(A2)).
#REF!
In VLOOKUP, this commonly means the column index exceeds the table width. For example, =VLOOKUP(A2,F2:H100,4,FALSE) requests a fourth column from a three-column table.
#VALUE!
Check malformed arguments and ensure an XLOOKUP lookup array and return array have compatible dimensions.
Free tools Windows power users keep installed
One-click scans. No signup required.
#NAME?
Verify the function spelling, quotation marks around literal text and version support. An unsupported XLOOKUP commonly produces this error.
#SPILL!
A dynamic-array result cannot occupy its destination cells when something blocks the spill area. Clear those cells and avoid an unintended entire-column reference; in structured formulas, a single-cell reference or @ may be required.
Duplicates and unexpected first results
Most lookup formulas return the first matching record. If keys are not unique, decide whether you need the last record (use reverse-search XLOOKUP) or every record (use FILTER). A lookup does not automatically detect duplicate IDs.
Approximate results that are wrong
Verify that the threshold data is sorted, the final argument was not omitted accidentally, the approximate direction is correct, and boundaries are not duplicated or overlapping. Binary XLOOKUP search modes are especially dependent on correct sorting.
Compatibility and the retired Lookup Wizard
Choose formulas according to the oldest Excel edition that must open and calculate the workbook. Microsoft 365, Excel 2021, Excel 2024 and Excel for the web support the modern XLOOKUP/XMATCH family. Excel 2016 and Excel 2019 do not natively provide XLOOKUP, so use VLOOKUP, HLOOKUP or INDEX with MATCH when those editions are in scope.
Microsoft’s older guidance on lookup values with VLOOKUP, INDEX or MATCH also notes that the Lookup Wizard is no longer available. Build formulas directly rather than following obsolete menu instructions.
Frequently Asked Questions
Is XLOOKUP always better than VLOOKUP?
No. XLOOKUP is usually the better choice for new formulas in supported Excel versions, but VLOOKUP remains appropriate when a workbook must run in Excel 2016 or 2019 or when maintaining an established legacy model.
Is LOOKUP the same function as XLOOKUP?
No. LOOKUP is an older function with approximate, generally sorted-vector behavior and no explicit not-found argument; XLOOKUP has separate lookup and return ranges, exact matching by default and additional search controls.
Can a normal lookup return every duplicate?
Usually no. Traditional lookup functions return one item, commonly the first match. Use FILTER when the intended result is the complete set of matching records.
How can I make a lookup case-sensitive?
Ordinary XLOOKUP, VLOOKUP and MATCH comparisons are not case-sensitive. A case-sensitive design needs an advanced pattern using EXACT, such as an INDEX/MATCH array formula, with behavior depending on the Excel version.
Quick Recap
Product prices and availability are accurate as of the date/time indicated and are subject to change. Any price and availability information displayed on Amazon at the time of purchase will apply.
