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, modern replacement for VLOOKUP and INDEX/MATCH. Dive into our comprehensive guide to master its application.
Parameters
search_key: The value or cell reference to search for.lookup_range: The range of cells to search for thesearch_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 is0(exact match).[search_mode]: [Optional] The search mode to use. Default is1(search first to last).
Video Example
Step-by-Step Tutorial
- Using
XLOOKUPfor an exact match:- Example: If
A1:A5contains product names andB1:B5contains their prices,=XLOOKUP("Widget", A1:A5, B1:B5, "Not found")returns the price for"Widget". - Result: The matching price, or
"Not found"if"Widget"isn't inA1:A5.
- Example: If
Use Cases and Scenarios
- Data Lookup: Retrieve specific data based on a search key, similar to
VLOOKUPbut without column-index counting. - Index-Match Replacement: Replace complex
INDEX/MATCHformulas with a single, simpler function. - Approximate Matching: Find the closest match to the search key using
match_mode. - Dynamic Data Extraction: Extract relevant information from a range based on changing criteria.
Related Functions
VLOOKUP: Vertical lookup that searches down the first column of a range for a key.INDEX: Returns the content of a cell, specified by row and column offset.MATCH: Returns the relative position of an item in a range that matches a specified value.