To match a value in an Excel column and return a value from another column, use XLOOKUP with the search column as its lookup array and the result column as its return array. For example, with IDs in A2:A4, regions in B2:B4, and the ID to find in E2, use =XLOOKUP(E2,A2:A4,B2:B4,"Not found",0).
What “column match” means in XLOOKUP
The phrase can describe two different tasks. You might search down a vertical column for a value, such as a product code, and return information from the same record. Or you might search across a horizontal row of column headings, such as months, and return the value beneath the heading you choose. XLOOKUP handles both because its lookup array can be a row or a column.
In either case, XLOOKUP compares the lookup value with the lookup array, finds a match, and returns the item at the corresponding position in the return array. The lookup and return arrays need to line up: if you search 20 rows, the return range should contain the corresponding 20 rows. A lookup column does not have to be the leftmost column in a worksheet, and the return column can be to its left or right.
This is different from asking Excel to match an entire column letter, such as column C, against another column. Usually the useful operation is matching a value or a header within a column or row, not comparing worksheet columns as objects.
Find a value down a column
Suppose A2:A4 contains P-104, P-105, and P-106, while B2:B4 contains North, South, and East. If E2 contains P-105, the opening formula returns South. The fourth argument supplies a readable result when there is no match; the final 0 explicitly requests an exact match.
The equivalent formula without those optional arguments is =XLOOKUP(E2,A2:A4,B2:B4). XLOOKUP uses exact matching by default, so the shorter version also finds P-105 and returns South. Including an explicit not-found message can be useful in a report because it avoids showing a raw #N/A error when a searched value is absent.
To return a different field, keep the lookup array and change the return array. For example, a table might have product codes in column A, prices in column C, and stock status in column D. Searching A2:A100 and returning D2:D100 gives the matching status. The ranges should cover the same records and remain aligned; using A2:A100 with D3:D101 would shift the results by one row.
Match a column heading across a row
If the thing you need to match is a header, put the header row in the lookup array and the data row in the return array. For example, suppose B1:E1 contains Q1, Q2, Q3, and Q4, and B2:E2 contains 120, 135, 128, and 150. With Q3 in G1, use =XLOOKUP(G1,B1:E1,B2:E2,"Header not found",0). The result is 128.
This is a horizontal lookup: XLOOKUP searches left to right across B1:E1, then returns the value in the same relative position from B2:E2. The arrays must have matching widths. This pattern is handy when a report lets someone select a month, quarter, department, or other header and displays the corresponding figure.
Headers should be unique if each one is meant to identify a different field. If Q3 appears twice in the lookup row, a standard XLOOKUP returns the first matching Q3 from the left. It does not automatically know which duplicate heading you intended.
Use a row match and a column match together
A two-way lookup finds a record by one criterion and a field by another. Imagine employee names in A2:A4, month headings in B1:D1, and monthly figures in B2:D4. The entries are Ana (420, 390, 440), Ben (410, 375, 460), and Carla (500, 510, 495) for January through March. Put the requested month in G1 and employee in G2. Use =XLOOKUP(G2,A2:A4,XLOOKUP(G1,B1:D1,B2:D4),"Employee not found",0). If G1 is Feb and G2 is Ben, the result is 375.
The inner XLOOKUP finds the chosen month heading and returns the entire matching data column from B2:D4. The outer XLOOKUP searches the employee list and returns the figure at that employee’s position in the selected column. In this example, the heading and employee both need to exist. If the month heading is missing, the inner lookup returns #N/A; the outer formula’s not-found message covers a missing employee, not a missing heading.
XLOOKUP can also return several adjacent values at once. With the same employee table, if G2 contains Ben, use =XLOOKUP(G2,A2:A4,B2:D4) to return Ben’s entire January-to-March row: 410, 375, and 460. In current Excel versions, the results spill into neighboring cells automatically. Make sure those cells are empty; otherwise Excel displays a #SPILL! error.
Exact, approximate, and reverse matching
For ordinary labels, IDs, and headers, exact matching is usually what you want. XLOOKUP’s default match mode is exact, and specifying 0 makes that choice visible to anyone maintaining the formula. Exact matching does not mean matching is case-sensitive: standard XLOOKUP comparisons do not distinguish uppercase from lowercase. If A2 contains North, a search for north can match it.
The match mode can change what counts as a match:
- 0: exact match; this is the default.
- -1: exact match, or the next smaller item if there is no exact match.
- 1: exact match, or the next larger item if there is no exact match.
- 2: wildcard matching, including * for any number of characters and ? for one character.
Wildcard mode is useful for a search term that is incomplete, but it can return the first value matching the pattern rather than a unique exact record. A tilde is used to treat wildcard characters as literal characters in a search pattern. For approximate modes, check that the values are ordered in a way that makes the next-smaller or next-larger result meaningful.
XLOOKUP also has a search mode that controls the search direction. Its normal search starts at the first item and moves toward the last. A reverse search starts at the last item, which can help when a list contains repeated keys and the newest record is at the bottom. Binary search modes can be faster on large sorted lists, but the data must be sorted in the required order; using them on unsorted data can produce incorrect results.
Common column-match problems
A #N/A result usually means no matching value was found. Check for extra spaces, inconsistent spelling, or a mismatch between numbers stored as numbers and numbers stored as text. For instance, the text value “00125” is not necessarily the same as the numeric value 125, especially when leading zeros are meaningful. An explicit not-found result can make the outcome clearer, but it does not fix inconsistent source data.
- Wrong return value: confirm that the lookup and return ranges start and end on the same rows, or the same relative columns for a horizontal lookup.
- #VALUE! or unexpected results: verify that the lookup array is one row or one column and that the return array has a compatible shape.
- #SPILL!: clear cells in the output area if the formula is returning multiple results.
- First duplicate selected: remove or resolve duplicate keys, or use reverse search if the last occurrence is the intended one.
- Header not found: compare the actual heading text, including spaces, punctuation, and abbreviated month names.
When headers or records change frequently, convert the data range to an Excel table using Insert > Table. Table references can make formulas easier to read and help include newly added table rows. Even with a table, check that the selected lookup field is unique where uniqueness matters, and avoid silently relying on a first-match result when duplicate keys may indicate a data problem.
XLOOKUP compared with older lookup methods
XLOOKUP is generally easier to adapt than VLOOKUP for matching a value in a column. VLOOKUP traditionally searches the first column of its table range and returns a column to its right using a column number. XLOOKUP names the lookup and return arrays separately, so the return field can be on either side of the lookup field, and inserting a column is less likely to break the formula’s intended meaning.
For a horizontal search, HLOOKUP is an older option that searches the top row of a range. XLOOKUP uses the same basic idea of searching a row, but its lookup and return ranges are stated directly. INDEX and MATCH can also solve vertical, horizontal, and two-way lookup problems; those functions remain useful in workbooks that need compatibility with older Excel releases. The best choice depends on who will open the workbook and which Excel versions they use.
Excel version and workbook compatibility
XLOOKUP is available in Microsoft 365 and Excel 2021 or later, including current Excel for Windows, Mac, and the web. Its formula structure is the same across those current platforms. Excel 2019 and earlier do not support XLOOKUP, so a workbook containing the function may show #NAME? when opened in those versions. If a workbook must work in an older installation, choose a compatible alternative such as INDEX and MATCH, and check the workbook in the target version.
In some regional settings Excel uses semicolons rather than commas between formula arguments. The formulas here use commas, as in the standard English-language syntax. If Excel rejects a correctly written formula, regional list-separator settings may be the reason; the function names and lookup logic do not change.
Frequently Asked Questions
Can XLOOKUP match a value in any column? Yes. Set the column containing the search values as the lookup array and the aligned column containing the answers as the return array. The lookup column does not have to be the first column in the worksheet or table.
Can XLOOKUP search across column headings? Yes. Use the heading row as the lookup array and the data row, or matching data block, as the return array. This is a horizontal lookup, and the lookup and return ranges must line up.
Does XLOOKUP match exactly by default? Yes. Its default match mode is exact. You can include 0 as the match-mode argument to make exact matching explicit, or choose another mode when approximate or wildcard matching is genuinely needed.
How do I return a whole column of results? Search for the relevant header across the header row and use the corresponding multirow data block as the return array. XLOOKUP can return the selected column as a dynamic array in current Excel, provided the output cells are clear.
Why does XLOOKUP return the wrong duplicate? By default, XLOOKUP returns the first match it encounters. If you need the last match, change the search direction to search from last to first, or make the lookup key unique so the intended record is unambiguous.
Related
- Excel Data Model Measures: Add, Format, and Use One
- Excel Data Model: How to Append Tables with Power Query
- Excel Data Run Analysis: A Practical Runs Test
Try it: the Formula Explainer: paste any formula and see each function and reference in plain English.
More in Formulas & functions
- What-If Analysis in Excel: A Step-by-Step GuideTo add what-if analysis in Excel, open the Data tab and choose Forecast > What-If Analysis, then select…
- Excel MIN and MAX in the Same Formula: Examples and TipsYes. In Excel for Microsoft 365 on Windows, you can calculate both endpoints in one formula, such as…
- Lowercase Names in Excel: Formula, Fill Down, and ReplaceTo lowercase names in Excel, use the LOWER function in a helper column, then fill the formula down.…
- Excel REDUCE Function Examples With NumbersIf your numbers are in A2:A6, add them with =REDUCE(0,A2:A6,LAMBDA(acc,n,acc+n)). With 12, -3, 8, 0, and 5 in…
- Excel Attendance Sheet With Time: Setup and FormulasTo make an Excel attendance sheet with time, create columns for the date, employee, scheduled start, clock-in, clock-out,…
- SUMIFS in Excel: Count or Sum Matching Values?SUMIFS in Excel adds numeric values that meet one or more conditions; it does not normally count matching…
Sources: Function behaviour and menu paths: Microsoft Support documentation for Excel. Method & sources
Spotted an error in this guide? Let us know.