Use XLOOKUP In Excel

Steps to use XLOOKUP in Excel

• Step 1: Select the cell where you want the result to appear, and type: =XLOOKUP(
• Step 2: Enter or select the lookup value — the value you want to search for.
• Step 3: Type a comma ,, then select the lookup array — the range of cells where Excel should search for the lookup value.
• Step 4: Type another comma ,, then select the return array — the range of cells that contains the values to return if a match is found.
• Step 5: You can include optional arguments then close the parenthesis ) and press Enter.
  • if_not_found: What to return if the value is not found (e.g., "Not Found")
  • match_mode: 0 for exact match (default), -1 or 1 for approximate matches, 2 for wildcard match
  • search_mode: 1 to search from first to last, -1 to search from last to first

Final formula structure:

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

Example:

=XLOOKUP(G3, A3:A17, B3:E17)

This means: Find the value in cell G3 within the range A3:A17, and return the corresponding row of data from the range B3:E17. If a match is found, it will return the related information such as name, designation, etc., depending on how the result is displayed.

Result: The cell will display the value that matches your lookup criteria from the specified return range.

Tip: If XLOOKUP returns #N/A, make sure the lookup value actually exists in the lookup array, and that both arrays have the same number of rows (if working vertically) or columns (if working horizontally).