Lookup values in vertical or horizontal ranges

=XLOOKUP(lookup_value, lookup_array, return_array, [if_not_found], [match_mode], [search_mode])

---

XLOOKUP is a modern and more flexible replacement for the VLOOKUP, HLOOKUP and LOOKUP functions.

XLOOKUP is only available to Excel 365 subscribers.

---

XLOOKUP can find values in vertical or horizontal ranges, offers exact and approximate matches, supports wildcards, can return an if_not_found value and can search data from the first or last value.

XLOOKUP defines 'what to lookup', 'where to lookup', 'the array or range to return', 'the if_not_found value to return', 'whether to return an exact or approximate match' and 'whether to search from the first or last value'.

For example, =XLOOKUP("South Asia",C3:C12,B3:B12,"Not found",0,-1) will look for the lookup_value "South Asia" in the lookup_array in column C and will then return the exact matching result from the return_array in column B. "Not found" would be returned if there was no exact matching lookup_value (instead of the default #N/A error). The 0 indicates an exact match is required. The -1 indicates the search_mode should be last-to-first. IFNA could be used to trap and handle #N/A errors.

---

XLOOKUP and exact searches.

In the examples below, the XLOOKUP function is used to return exact matches for:

• a value to the right of the lookup_value (like VLOOKUP)
• a value to the left of the lookup_value AND searching last-to-first
• an array of 4 pieces of data.

---

XLOOKUP and approximate searches.

In the examples below, the XLOOKUP function is used to return approximate matches for:

• a value to the left of the lookup_value using the match_mode -1 for 'Exact match or next smaller item'
• a value when comparing the lookup_value to a range of values
• an array of 2 pieces of data using a named range.

---

Syntax

=XLOOKUP(lookup_value, lookup_array, return_array, [if_not_found], [match_mode], [search_mode])

The XLOOKUP function consists of at least 3 arguments:

1. lookup_value. Required. The value to be looked for.
2. lookup_array. Required. The array or range in which to find the lookup_value.
3. return_array. Required. The array or range in which to find the return value.
4. [if_not_found]. Optional. The value to return if no match is found. If [if_not_found] is missing, #N/A is returned when a valid match is not found.
5. [match_mode]. Optional. The type of match to apply:
   • 0 - Exact match. If none found, return #N/A. This is the default.
   • -1 - Exact match. If none found, return the next smaller item.
   • 1 - Exact match. If none found, return the next larger item.
   • 2 - A wildcard match for *, ?, and ~.
6. [search_mode]. Optional. How to search for the return value:
   • 1 - Start at the first item. This is the default.
   • -1 - Start at the last item (reverse search).
   • 2 - Perform a binary search that relies on lookup_array being sorted in ascending order. If not sorted, invalid results will be returned.
   • -2 - Perform a binary search that relies on lookup_array being sorted in descending order. If not sorted, invalid results will be returned.

---

Try it now!

• Enter your formulas into the grey cells.

---

Scope

XLOOKUP defines 'what to lookup', 'where to lookup', 'the array or range to return', 'the if_not_found value to return', 'whether to return an exact or approximate match' and 'whether to search from the first or last value'.

Exact:	=XLOOKUP("South Asia",C3:C12,B3:B12,"Not found",0,-1) || result is a value from return_array column B to the left of the lookup_array in column C. "Not found" is returned if there is no exact match to the lookup_value "South Asia". The search is started from the last item (search_mode = -1).
Approximate:	=XLOOKUP(F8,B11:B14,pc_growth,,-1,2) || result is a value from from the named range "pc_growth". There is no [if_not_found] value. The match_mode is set to -1 or 'Exact match or next smaller item'. search_mode is set to 'Binary search (sorted ascending order)'.
Array:	=XLOOKUP("Japan",B3:B12,C3:F12) || result is a 4 column array from the return_array columns C:F. Ensure you have adequate space for an array response otherwise the #SPILL! error is returned.
Vertical Lookup:	=XLOOKUP(C3,G6:G12,E6:E12) || result is a value from the return_array E6:E12 based off an exact match of the lookup_value from cell C3 in the vertical lookup_array G6:G12.
Horizontal Lookup:	=XLOOKUP(C3,C5:G5,C6:F12) || result is a value from the return_array C6:F12 based off an exact match of the lookup_value from cell C3 in the horizontal lookup_array C5:G5.

Caveats

• XLOOKUP uses a lookup array and a return array, whereas VLOOKUP uses a single table array followed by a column index number.
• To use XLOOKUP between 2 or more workbooks, all workbooks must be open, otherwise the #REF! error is returned.
• lookup_value
  • XLOOKUP supports wildcards (* ?) for partial matches.
• lookup_array
  • XLOOKUP can perform vertical and horizontal matches.
  • XLOOKUP supports exact and approximate matches.
  • XLOOKUP supports multiple criteria. =XLOOKUP(1,(C3:C6="James")*(D3:D6="Jones")*(DAY(E3:E6)=4),F3:F6) will return the first record in the <return_array F3:F6 where the lookup_value matches "James" AND "Jones" AND the 4th day in any month in the various lookup_ranges.
  • The lookup_array must have a range compatible with the return_array argument, otherwise the #VALUE! error is returned.
• return_array
  • The return_array can be to the left or right of the lookup_array.
  • XLOOKUP can return vertical or horizontal arrays with multiple values, unlike VLOOKUP which can only return one value. Beware arrays spilling into other cells and potentially triggering a #SPILL error.
  • Nesting XLOOKUP functions enables a 2-way lookup, both vertical and horizontal.
  • The return_array must have a range compatible with the lookup_array argument, otherwise the #VALUE! error is returned.
• if_not_found
  • XLOOKUP returns the #N/A error when a match is not found.
  • The optional argument if_not_found can be used to override the #N/A error. Enclose any text in quotes. An empty string "" will display nothing instead of #N/A.
• match_mode
  • XLOOKUP will perform an exact match (0) by default whereas VLOOKUP defaults to approximate match.
  • #N/A is returned if there is no match. Use the if_not_found argument or the IFNA function to trap and handle #N/A errors. The #N/A error is an indication of missing or misspelt values, incorrect ranges or that the range_lookup is set to exact instead of approximate.
• search_mode
  • XLOOKUP will start matching from the first value by default.
  • XLOOKUP can search data starting from the first or the last value.
  • Note that for binary searches (2 or -2), data must be sorted, either in ascending (2) or descending (-2) order. If data is not sorted, the binary search may return erroneous results.

---

Common issues

• Incorrect value returned: Check to see if search_mode is set to binary search and sort data accordingly.
• #N/A: If match_mode is 0, an exact match can not be made.
• #REF!: XLOOKUP may be referencing another workbook that has not been opened.
• #VALUE!: lookup_array and return_array may not have compatible ranges.
• #NAME?: text values might be missing quotations.
• #SPILL!: return_array is likely trying to write over other cells with existing content.

---

Related Functions

• VLOOKUP to lookup values by row in a vertical table or range.
• HLOOKUP to lookup values by column in a horizontal table or range.
• INDEX and MATCH to lookup values in any direction.
• OFFSET to lookup values in any direction.

---

Functions Category

• Lookup & Reference

 

---