XLOOKUP Formula

Use the XLOOKUP function to find things in a table or range by row.

In its simplest form, the XLOOKUP function says:
=XLOOKUP(lookup_value, lookup_array, return_array, [if_not_found], [match_mode], [search_mode])

• lookup_value: The value to search for.
• lookup_array: The array or range to search.
• return_array: The array or range to return.
• [if_not_found] : (Optional) Where a valid match is not found, return the [if_not_found] text you supply.
• [match_mode]: (Optional) Specify the match type:
  0 – Exact match. If none is found, return #N/A. This is the default.
  -1 – Exact match. If none is found, return the next smaller item.
  1 – Exact match. If none is found, return the next larger item.

Example
Find an employee’s name based on his employee ID

• Type “=XLOOKUP(”
• Click “fx” in the formula bar.
• In “lookup_value”, select cell E2.
• In “lookup_array“, select the range from B2 to B8.
• In “return_array“, select the range from A2 to A8.
• In “[if_not_found]“, Type “0”.
• In “[match_mode]“, Type “0”.
• Click on “OK”