How to use XLOOKUP Function in Google Sheets (+Template)- 3 Easy Simple Ways

Reading Time: 4 minutes

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

search_key – The value you want to search for.

lookup_range – The range to find a match; either a single column or row.

result_range – The range to pick the result from; should match lookup range.

missing_value – Optional; fallback value if no match is returned

match_mode - Optional; type of match; 0 is exact match.

search_mode – [Optional] 1 is default. Search mode which is first-to-last order of searching. -1 searches last entry to first entry.

Sample Usage
=XLOOKUP(E2,A2:A11,C2:C11)
//searches for value E2 in range A2:A11, matches it (say ‘A4’), then shows as output the corresponding value in the range C2:C11 (‘C4’)

Sample Spreadsheet for reference here

Table of Contents

What is XLOOKUP Function?

XLOOKUP Function is basically used in Excel to look up the value of a particular entity. Say, you have a list of all the employees in your company and you would like to get information regarding an employee. Now, instead of searching manually through the list, you can simply use the XLOOKUP Function to obtain all the required information.

Why XLOOKUP Function?

So basically, using XLOOKUP Function, finding the values in a table or a range of values by row and returning the corresponding value in the column becomes an easy task.

Figure 1: XLOOKUP Function in Excel 365

Hence, looking at the alternatives for this quite important function in Google Sheets is beneficial.

How to use XLOOKUP Function in Google Sheets?

So the Syntax for XLOOKUP Function is:

=XLOOKUP(lookup_value, lookup_array, return_array)

Now, we will try using alternative methods to use XLOOKUP Function in Google Sheets.

For example, we have the following data for the list of employees.

Figure 2: Employees data to use XLOOKUP Function in Google Sheets

Using FILTER Function

The FILTER Function in Google Sheets is the closest to the XLOOKUP Function.

Syntax for FILTER Function

=FILTER(range, condition 1, [condition 2, .])

So the range here refers to the range where the value you want exists, and condition 1 is the range of the value you are looking up for.

Now, you can create a DROPDOWN in Google Sheets for the Name of employees.

Figure 3: Using DROPDOWN Function to select the value to use XLOOKUP Function on

Next, select a cell and add the following Syntax for the Employee Number in cell G6:

=FILTER(C3:C9, A3:A9 = G4)

Hence, the spreadsheet would now look like this.

Figure 4: Using FILTER Function instead of XLOOKUP Function in Google Sheets

You can simply use the FILTER Function to replace the XLOOKUP Function in Google Sheets.

Using VLOOKUP

You can always use VLOOKUP Function In Google Sheets in place of the XLOOKUP Function. XLOOKUP Function is basically an advanced version of the VLOOKUP/HLOOKUP Function.

The major difference between HLOOKUP and VLOOKUP Function is that HLOOKUP looks for the value horizontally in a row whereas VLOOKUP looks for a value vertically in a column.

Syntax for VLOOKUP Function

=VLOOKUP(search_key, range, index, [is_sorted])

So, first, you need to add the cell corresponding to the value you are looking for, followed by the total range of the data, i.e., A3:D9 in our case, and then the column number from where you want the value to return, followed by a true/false value depending on whether to get an exact match of the value or not.

Hence, the Syntax for the VLOOKUP Function becomes:

=VLOOKUP(G4,A3:D9,3,false)

Figure 5: Using VLOOKUP Function instead of XLOOKUP Function in Google Sheets

Using the INDEX and MATCH Function In Google Sheets

You can also use the INDEX and MATCH functions in Google Sheets to get the required information.

This can be simply achieved by Indexing the entire range where you are looking for the value to be returned and then matching the row using the lookup value in cell G4.

The Syntax for MATCH Function is as follows:

=MATCH(search_key, range, search_type)

The Syntax for the INDEX Function is as follows:

=INDEX(reference, [row], [column])

Syntax for INDEX/MATCH Function

=INDEX(C3:C9, MATCH(G4,A3:A9,0))

Here, you are indexing column C where the employee numbers are stored, and then you match the value of G4(i.e. Our lookup value) with the range of A3:A9 which contains the names of your employees, and a 0 to indicate looking for an exact match.

Figure 6: Using INDEX/MATCH Function instead of XLOOKUP Function in Google Sheets

Conclusion

XLOOKUP Function proves to be a very useful tool when trying to look for a particular value in a huge amount of data. Thankfully, there are many alternatives available to using XLOOKUP Function in Google Sheets.

Hope this article helped you!

See Also

Want to know more formulas and functions in Google Sheets? Look at our definitive guide on Google Sheets which covers hundreds of such topics here. Enjoy reading!

How to use the CHOOSE Function in Google Sheets: Learn how to use the CHOOSE Function in Google Sheets in three simple examples.

How to use the SORTN Function in Google Sheets: Learn about the SORTN Function in Google Sheets in this 5 minutes easy guide.

How to Rank data in Google Sheets using the RANK Function: Learn how to use the RANK Function in Google Sheets in just two easy steps.

How to use XLOOKUP Function in Google Sheets (+Template)