Introduction
The XLOOKUP function, a modern lookup feature in Excel, is now also available in Google Sheets. However, exploring alternative formulas can help you understand the versatility of Google Sheets and allow you to perform similar lookups.
Although many Google Sheets users managed without XLOOKUP by using workarounds, these alternatives offer powerful options to replicate much of XLOOKUP’s functionality.
This guide provides several alternative formulas, covering exact matches, multiple returns, and advanced match modes.
Note: For simplicity, stick with XLOOKUP, as it makes lookups much easier.
XLOOKUP Formula Alternatives: Basic Exact Match Lookups
Below, we cover several alternatives to XLOOKUP for exact match lookups in Google Sheets.
Sample Data
Use the “Basic” tab in the Example Sheet to follow along. Copy the sheet to your Google Drive to practice each example.
1. Exact Match: Retrieve Value Based on Lookup
Task
Find the exact match of “David” in column B and return the score from column E.
Formula Comparison
- XLOOKUP Formula:
=XLOOKUP("David", B2:B9, E2:E9)
- Alternative with VLOOKUP:
=VLOOKUP("David", {B2:B9, E2:E9}, 2, 0)
2. Reverse Lookup: Retrieve Value from a Different Column
Task
Look up the name “David” in column B and return the corresponding date from column A.
Formula Comparison
- XLOOKUP Formula:
=XLOOKUP("David", B2:B9, A2:A9)
- Alternative with VLOOKUP:
=VLOOKUP("David", {B2:B9, A2:A9}, 2, 0)
Tip: The result might display as a serial number if the cell is not formatted as a date. To adjust, go to Format > Number > Date.
3. Return Multiple Columns in Result Array
Task
Return multiple columns (C to E) for the lookup value “David” in column B.
Formula Comparison
- XLOOKUP Formula:
=XLOOKUP("David", B2:B9, C2:E9)
- Alternative with ArrayFormula and VLOOKUP:
=ArrayFormula(VLOOKUP("David", {B2:B9, C2:E9}, {2,3,4}, 0))
4. Lookup with Multiple Values
Task
Retrieve scores for both “David” and “Helen” in column B.
Formula Comparison
- XLOOKUP Formula:
=ArrayFormula(XLOOKUP({"David"; "Helen"}, B2:B9, E2:E9))
- Alternative:
=ArrayFormula(VLOOKUP({"David"; "Helen"}, {B2:B9, E2:E9}, 2, 0))
5. Combination of Multiple Lookup Values and Multiple Return Columns
Task
Return multiple columns (C to E) for “David” and “Helen” in column B.
Formula Comparison
- XLOOKUP Formula (currently unavailable for 2D arrays):
Requires LAMBDA functions for 2D results. - Alternative with VLOOKUP and ArrayFormula:
=ArrayFormula(VLOOKUP({"David"; "Helen"}, {B2:B9, C2:E9}, {2,3,4}, 0))
Match and Search Modes in XLOOKUP Alternatives
XLOOKUP’s match and search modes are advanced features that make it versatile.
Below, we break down four types of match and search modes and demonstrate how to achieve them with formulas in Google Sheets.
Sample Data
The data set includes timestamps in column A, order numbers in column B, and order quantities in column C. This data can be viewed in the ‘Match and Search Modes – SMALL’ tab of the sample sheet linked above.
In all the following examples, the criterion will be a timestamp or date entered in cell E2.
1. Exact / Next Smallest – Last Value
Task
Return the quantity for a timestamp that either matches exactly or is the next smallest value in column A.
Formula Comparison
- XLOOKUP Formula:
=XLOOKUP(E2, A2:A17, C2:C17, , -1, -1)
- Alternative with VLOOKUP:
=VLOOKUP(E2, SORT({A2:A17, C2:C17}, 1, 1), 2, 1)
2. Exact / Next Smallest – First Value
Task
Return the quantity for a timestamp that matches exactly or is the next smallest, prioritizing the first occurrence.
Formula Comparison
- XLOOKUP Formula:
=XLOOKUP(E2, A2:A17, C2:C17, , -1, 1)
- Alternative with SORTN and VLOOKUP:
=VLOOKUP(E2, SORTN({A2:A17, C2:C17}, 9^9, 2, 1, 1), 2, 1)
3. Exact / Next Largest – Last Value
For this example, refer to the sample data in the ‘Match and Search Modes – LARGE’ tab in the sample sheet above. It’s similar to the previous sample data, with a few timestamps adjusted in column A.
Task
Find the next largest timestamp and return the last occurrence.
Formula Comparison
- XLOOKUP Formula:
=XLOOKUP(E2, A2:A17, C2:C17, , 1, -1)
- Alternative with IFNA, SORT, and SORTN:
=IFNA(VLOOKUP(E2, SORT({A2:A17, C2:C17, ROW(A2:A17)}, 3, 0), 2, 0),
VLOOKUP(E2, {{1/0; INDEX(SORTN(SORT({A2:A17, C2:C17, ROW(A2:A17)}, 3, 0), 9^9, 2, 1, 1), , 1)},
{INDEX(SORTN(SORT({A2:A17, C2:C17, ROW(A2:A17)}, 3, 0), 9^9, 2, 1, 1), , 2); IF(,,)}}, 2, 1))
4. Exact / Next Largest – First Value
Task
Find the next largest timestamp and return the first occurrence.
Formula Comparison
- XLOOKUP Formula:
=XLOOKUP(E2, A2:A17, C2:C17, , 1, 1)
- Alternative with IFNA and SORTN:
=IFNA(VLOOKUP(E2, {A2:A17, C2:C17}, 2, 0),
VLOOKUP(E2, {{1/0; INDEX(SORTN({A2:A17, C2:C17}, 9^9, 2, 1, 1), , 1)},
{INDEX(SORTN({A2:A17, C2:C17}, 9^9, 2, 1, 1), , 2); IF(,,)}}, 2, 1))
Conclusion
While XLOOKUP in Google Sheets offers powerful match and search options, these alternative formulas provide similar functionality.
In the examples above, we haven’t addressed XLOOKUP’s capabilities for wildcard matches and binary search modes.
Overall, XLOOKUP is significantly more versatile and efficient than its alternatives.
XLOOKUP Resources
- XLOOKUP Visible (Filtered) Data in Google Sheets
- XLOOKUP Nth Match Value in Google Sheets
- Nested XLOOKUP Function in Google Sheets
- VLOOKUP and XLOOKUP: Key Differences in Google Sheets
- XLOOKUP with Multiple Criteria in Google Sheets
- XLOOKUP for Multiple Column Results in Google Sheets
- XLOOKUP with Date and Time in Google Sheets
- XLOOKUP with Single IMPORTRANGE & LET in Google Sheets
- XLOOKUP and Offset Results in Google Sheets
- XLOOKUP in Merged Cells in Google Sheets
- Counting XLOOKUP Results with COUNTIFS in Excel and Google Sheets
- SUMIFS with XLOOKUP in Excel and Google Sheets
- Slicing Data with XLOOKUP in Google Sheets