How can I use the XLOOKUP function in Google Sheets?

How can I use the XLOOKUP function in Google Sheets?

By /

The XLOOKUP function in Google Sheets is a powerful tool that allows users to easily search for and retrieve data from a specific range in a spreadsheet. This function can be used in a variety of ways, such as finding the value of a specific cell, searching for data in multiple columns, and even performing approximate matches. By following the proper syntax and using the appropriate parameters, users can effectively utilize the XLOOKUP function to quickly and accurately extract data from their Google Sheets. This function is especially useful for organizing and analyzing large sets of data, making it an essential tool for efficient and effective spreadsheet management.

XLOOKUP function

The XLOOKUP function 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.

XLOOKUP for BigQuery

Cross lookup. Returns the values in the data column at the position where a match was found in the search column.

Sample Usage

=XLOOKUP("Apple",table_name!fruit,table_name!price)

Syntax

XLOOKUP(search_key,lookup_range,result_range,missing_value,match_mode)

  • search_key: The value to search for. For example, 42, "Cats", or B24.
  • search_column: The column to consider for the search.
  • result_column: The column to consider for the result.
  • missing_value: [OPTIONAL – #N/A by default] The value to return if no match is found.
  • match_mode: [OPTIONAL – 0 by default] The manner in which to find a match for the search_key.
    • 0: For an exact match.
    • 1: For an exact match or the next value that is greater than the search_key.
    • -1: For an exact match or the next value that is lesser than the search_key.
    • 2: For a wildcard match.

Tip:search_mode isn’t supported in XLOOKUP for BigQuery.

Sample Usage

XLOOKUP("Apple", A2:A, E2:E) to replace VLOOKUP("Apple", A2:E, 5, FALSE)
XLOOKUP("Price", A1:E1, A6:E6) to replace HLOOKUP("Price", A1:E6, 6, FALSE)
XLOOKUP where match column is to the right of the output column
XLOOKUP("Apple", E2:E7, A2:A7). The VLOOKUP equivalent is VLOOKUP("Apple", {E2:E7, A2:A7}, 2, FALSE)

Syntax

XLOOKUP(search_key, lookup_range, result_range, missing_value, match_mode, search_mode)
  • search_key: The value to search for. For example, 42, "Cats", or B24.
  • lookup_range: The range to consider for the search. This range must be a singular row or column.
  • result_range: The range to consider for the result. This range’s row or column size should be the same as the lookup_range, depending on how the lookup is done.
  • missing_value: [OPTIONAL – #N/A by default] The value to return if no match is found.
  • match_mode: [OPTIONAL – 0 by default] The manner in which to find a match for the search_key.
    • 0 is for an exact match.
    • 1 is for an exact match or the next value that is greater than the search_key.
    • -1 is for an exact match or the next value that is lesser than the search_key.
    • 2 is for a wildcard match.
  • search_mode: [OPTIONAL – 1 by default] The manner in which to search through the lookup_range.
    • 1 is to search from the first entry to the last.
    • -1 is to search from the last entry to the first.
    • 2 is to search through the range with binary search. The range needs to be sorted in ascending order first.
    • -2 is to search through the range with binary search. The range needs to be sorted in descending order first.

Notes

  • If result_range is more than one row or column, then the output will be the entire row/column at the index a match was found in the lookup_range.

Examples

Lookup table for all examples.

Lookup table for all examples.

 

XLOOKUP for Total amount sold with match_mode and search_mode omitted and missing argument specified.

 XLOOKUP for Total amount sold with match_mode and search_mode omitted and missing argument specified.

 
XLOOKUP for Total amount sold with match_mode = 0 and search_mode = 1 and -1.

XLOOKUP for Total amount sold with match_mode = 0 and search_mode = 1 and -1.

 
XLOOKUP for Total amount sold with match_mode = 1 and -1 and search_mode omitted.

XLOOKUP for Total amount sold with match_mode = 1 and -1 and search_mode omitted.

 
XLOOKUP using horizontal matching and returning an entire column.

XLOOKUP using horizontal matching and returning an entire column.

Related functions

  • XMATCH
  • VLOOKUP
  • HLOOKUP

Cite this article

stats writer (2024). How can I use the XLOOKUP function in Google Sheets?. PSYCHOLOGICAL SCALES. Retrieved from https://scales.arabpsychology.com/stats/how-can-i-use-the-xlookup-function-in-google-sheets/

stats writer. "How can I use the XLOOKUP function in Google Sheets?." PSYCHOLOGICAL SCALES, 29 Jun. 2024, https://scales.arabpsychology.com/stats/how-can-i-use-the-xlookup-function-in-google-sheets/.

stats writer. "How can I use the XLOOKUP function in Google Sheets?." PSYCHOLOGICAL SCALES, 2024. https://scales.arabpsychology.com/stats/how-can-i-use-the-xlookup-function-in-google-sheets/.

stats writer (2024) 'How can I use the XLOOKUP function in Google Sheets?', PSYCHOLOGICAL SCALES. Available at: https://scales.arabpsychology.com/stats/how-can-i-use-the-xlookup-function-in-google-sheets/.

[1] stats writer, "How can I use the XLOOKUP function in Google Sheets?," PSYCHOLOGICAL SCALES, vol. X, no. Y, ص Z-Z, June, 2024.

stats writer. How can I use the XLOOKUP function in Google Sheets?. PSYCHOLOGICAL SCALES. 2024;vol(issue):pages.

Download Post (.PDF)

Related terms:

  1. How to Use XLOOKUP to Retrieve Data from Another Sheet in Google Sheets
  2. How can I use XLOOKUP in Google Sheets? Can you provide an example?
  3. How to Easily Use XLOOKUP in Google Sheets: A Step-by-Step Guide
  4. How to Use XLOOKUP to Retrieve Data from Another Sheet in Google Sheets
  5. How to Perform Wildcard Searches with XLOOKUP in Excel
  6. How to Sum Matches with XLOOKUP in Excel
  7. How do I use XLOOKUP with an IF statement in Excel?
  8. How can I use XLOOKUP in VBA? Can you provide some examples?
  9. How can I use XLOOKUP with multiple criteria in Excel?
  10. How can XLOOKUP be used to return all matches in Excel?
Slide Up
x
PDF
Scroll to Top