In this article:

VLOOKUP Alternative on Google Sheets: What To Use in 2024

May 8, 2024

XLOOKUP as an Alternative to VLOOKUP in Google Sheets

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.

XLOOKUP Syntax

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.

How to Use XLOOKUP in Google Sheets

Follow the steps below to use XLOOKUP as an alternative to VLOOKUP in Google Sheets.

Step 1: Prepare Your Data

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:

vlookup alternative google sheets

Step 2: Enter Your XLOOKUP Formula

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.

google sheets vlookup alternative

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.

Get Google Sheets productivity and automation tips delivered straight to your inbox
Thank you! Your submission has been received!
Oops! Something went wrong while submitting the form.
We'll email you 1-3 times a week — and never share your information.

Work less, automate more!

Use Lido to connect your spreadsheets to email, Slack, calendars, and more to automate data transfers and eliminate manual copying and pasting. View all use cases ->