SheetFX
≡
<< Go back to all functions

XLOOKUP

The XLOOKUP function in Google Sheets returns the values in the result range based on the position where a match was found in the lookup range. If no match is found, it returns the closest match. With its versatile capabilities, the XLOOKUP function is a valuable tool for various lookup scenarios.

Function Syntax and Parameters

Syntax: XLOOKUP(search_key, lookup_range, result_range, missing_value, [match_mode], [search_mode])

Parameters:

  • search_key: The value or cell reference to search for.
  • lookup_range: The range of cells to search for the search_key.
  • result_range: The range of cells from which to retrieve the results.
  • missing_value: The value to return if no match is found.
  • [match_mode]: [Optional] The match mode to use. Default is 1.
  • [search_mode]: [Optional] The search mode to use. Default is 1.

Use Cases and Scenarios

The XLOOKUP function can be used in various scenarios, including:

  1. Data Lookup: Retrieve specific data based on a search key.
  2. Index-Match Replacement: Replace complex INDEX-MATCH formulas with a simpler solution.
  3. Approximate Matching: Find the closest match to the search key in a range.
  4. Dynamic Data Extraction: Extract relevant information based on changing criteria.

Related Articles