A good alternative to VLOOKUP in Google Sheets is XLOOKUP. XLOOKUP offers several advantages over VLOOKUP, including the ability to search for data in a column to the left of the return column (which VLOOKUP cannot do without a workaround), more straightforward syntax, and improved performance in certain scenarios.
The basic syntax of XLOOKUP is:
XLOOKUP(search_key, search_range, return_range, [if_not_found], [match_mode], [search_mode])
Where:
search_key: The value you're looking for.
search_range: The range where you expect to find the value.
return_range: The range from which to return a value.
[if_not_found]: Optional. What to return if search_key is not found. Default is #N/A.
[match_mode]: Optional. Specifies the match type. Default is 0 for an exact match.
[search_mode]: Optional. Specifies the search mode. Default is 1, which searches from first to last.
Follow the steps below to use XLOOKUP as an alternative to VLOOKUP in Google Sheets.
Ensure your data is organized appropriately. You'll need at least two columns: one for the search range and one for the return range.
For example, suppose you have a list of employees and their corresponding ID numbers and email addresses. You want to find the email address of an employee with a specific ID:
Click on the cell where you want the search result to appear and start typing your XLOOKUP formula.
In our example, if you want to find the email of the employee with ID 102, the XLOOKUP formula would be:
=XLOOKUP(102, A2:A4, C2:C4, "Not Found")
102 is the search_key, the ID you're looking for.
A2:A4 is the search_range, where the formula looks for the ID.
C2:C4 is the return_range, from which the formula returns the email address.
"Not Found" is what the formula will return if it doesn't find the ID.
After entering this formula in a cell, it should return jane@example.com, which is the email address corresponding to employee ID 102. If the ID wasn't found in the range, it would return Not Found.
We hope that this article has helped you and given you a better understanding of XLOOKUP as a VLOOKUP alternative on Google Sheets. If you enjoyed this article, you might also like our articles on how to use the MMULT function in Google Sheets and how to change the page orientation in Google Sheets.
