In this tutorial, you can find some XLOOKUP alternative formulas to use in Google Sheets.
At the time of writing this tutorial, this function is not available in Sheets.
Update: The function is available now! You can read it here – How to Use the XLOOKUP Function in Google Sheets.
Do we require the XLOOKUP function in Google Sheets?
The answer depends on your lookup requirement.
If you want to Lookup a search key in a lookup_array and return values from return_array, then XLOOKUP is not required.
I’m pointing to the first three arguments in this function.
XLOOKUP Syntax in Excel:
=XLOOKUP(lookup_value,lookup_array,return_array,[if_not_found],[match_mode],[search_mode])But if you want to use the XLOOKUP match modes and search modes in Google Sheets, then, in my opinion, we require this function.
Otherwise, we may want to depend on complex formulas to match what this function offers in Excel.
In this post, I have some XLOOKUP alternative formulas to use in Google Sheets. It even includes how to use various match_modes and search_modes.
Six Basic XLOOKUP Formula Alternatives in Google Sheets
We will apply six VLOOKUP formulas as alternatives to XLOOKUP in the below table.
Their purpose is basic lookups. So the alternative formulas will be enough. I mean, we don’t require XLOOKUP in Google Sheets to meet this requirement.
Exact Match Lookup_Value (Default Option)
Sample Data:
Please see column I in the Example Sheet “Basic” tab for the following formulas.
1. Exact match the name “David” in column B and return his score from column E.
=vlookup(G3,{B2:B9,E2:E9},2,0)lookup_value – G3
lookup_array – B2:B9
return_array – E2:E9
2. Reverse Lookup – Lookup the name “David” (exact match) in column B and return the corresponding date from column A.
=vlookup(G6,{B2:B9,A2:A9},2,0)lookup_value – G6 (“David”)
lookup_array – B2:B9
return_array – A2:A9
3. More than one column in return_array.
=ArrayFormula(vlookup(G10,{B2:B9,C2:E9},{2,3,4},0))lookup_value – G10 (“David”)
lookup_array – B2:B9
return_array – C2:E9
The formula matches the name and will return multiple values from the found row (columns C to E).
4. Multiple lookup_values.
=ArrayFormula(vlookup(G15:G16,{B2:B9,E2:E9},2,0))lookup_value – G15:G16 (G15 – “David”, G16 – “Helen”)
lookup_array – B2:B9
return_array – E2:E9
5. Combination of Points 3 & 4.
We can’t call it an XLOOKUP alternative in Google Sheets. Because the function in Excel doesn’t support this.
=ArrayFormula(vlookup(G20:G21,{B2:B9,C2:E9},{2,3,4},0))lookup_value – G20:G21 (G20 – “David”, G21 – “Helen”)
lookup_array – B2:B9
return_array – C2:E9
6. Lookup_values in Multiple Columns and Rows.
=ArrayFormula(vlookup(G24:G25&H24:H25,{A2:A9&B2:B9,E2:E9},2,0))lookup_value – G24:G25&H24:H25 (G24 – 21/06/21, G25 – 21/06/21, H24 – “David”, H25 – “Helen”,)
lookup_array – A2:A9&B2:B9
return_array – E2:E9
Match and Search Modes – The Game Changer
To meet our basic/usual XLOOKUP requirement, we have used Vlookup and Curly Brackets with or without the ArrayFormula function as above.
But the real game-changer in XLOOKUP is its match and search modes.
As far as I know, we need to depend on some complex formulas to meet that requirement in Google Sheets.
So I hope to get an XLOOKUP alternative function in Google Sheets. Then we can avoid writing complex and time-consuming formulas.
Here are my answers to XLOOKUP Match and Search Mode alternatives in Google Sheets.
XLOOKUP Function Match and Search Mode Alternatives in Google Sheets
If we categorize the said modes, we can come to the below 4.
1. Exact / Next Smallest – Last Value.
2. Exact / Next Smallest – First Value.
3. Exact / Next Largest – Last Value.
4. Exact / Next Largest – First Value.
Exact / Next Smallest – Last Value
In the above example, there are multiple lookup_values in E2:E4. Let’s take the value 21/6/21 in cell E4 for evaluation.
In the lookup_array A2:A17, there is no exact match of that value.
The next smallest value is 20/6/21 23:58 but appears thrice. In that, the first value in the return_array is 2.5, and the last value is 40.
Please see the above image to understand it.
The same is the case with the lookup_value in cell E3, which is 18/6/21. It’s also not available in the lookup_array.
The next smallest value is 17/06/21 23:58, which appears twice. In that, the first value is 25, and the last value is 2.
But regarding the lookup_value in E2, it appears in the lookup_array.
So the exact match of the value will be returned.
In that case, also, there are duplicates. The first value is 20, and the last value is 1.
In F2, we can use the below XLOOKUP alternative formula to return the exact or next smallest value in Google Sheets.
It will return the last value if there are duplicates of matching values in the lookup_array.
=ArrayFormula(vlookup(E2:E4,sort({A2:A17,C2:C17},1,1),2,1))lookup_value – E2:E4
lookup_array – A2:A17
return_array – C2:C17
Note 1:-
It’s a standard Vlookup formula.
Unlike the above BASIC examples, the last Vlookup argument, i.e., is_sorted is set to 1 here.
It requires a sorted range. So, I have used the SORT function within Vlookup.
VLOOKUP(search_key, range, index, [is_sorted])Note 2:- If your data is already sorted, then you can avoid using it. But I recommend using it irrespective of your data order.
Exact / Next Smallest – First Value
To get the exact or next smallest value and the first value in case of duplicates, we can use the following formula in Google Sheets.
Formula to use in cell G2 (please see the screenshot above).
=ArrayFormula(vlookup(E2:E4, sortn({A2:A17,C2:C17},9^9,2,1,1),2,1))lookup_value – E2:E4
lookup_array – A2:A17
return_array – C2:C17
Here instead of the SORT function, I have used SORTN.
Because in a table that contains duplicates, the Vlookup will only return the last value. That’s the default behavior.
So, the solution is to remove the last values (duplicates). Other than sorting, the SORTN does that part.
Exact / Next Largest – Last Value
It is the complicated part of the XLOOKUP alternative formulas in Google Sheets.
Because the Vlookup function, which we are using as the alternative to Xlookup above, doesn’t support the last value lookup in Google Sheets.
Here we should heavily modify the table (range) within Vlookup.
I have followed a similar approach earlier here – Nearest Match Greater Than or Equal to Search Key in Vlookup in Google Sheets.
My below formula logic is evolved on that concept.
I’m not attempting a formula explanation. When you use it, just modify the lookup_value, lookup_array, and return_array as per your data.
First, understand the exact or the next largest match and the last value concept.
Please see the below image.
The lookup_value in E3, i.e., 18/6/21, is not available in the lookup_array A2:A17. The next largest value available is 18/6/21 23.58. In that, the last value is 2.5.
I hope you can understand the above concept.
Here is the formula to apply in F2.
=ArrayFormula(ifna(vlookup(E2:E4,sort({A2:A17,C2:C17,row(A2:A17)},3,0),2,0),vlookup(E2:E4,{{1/0;index(SORTN(sort({A2:A17,C2:C17,row(A2:A17)},3,0),9^9,2,1,1),0,1)},{index(SORTN(sort({A2:A17,C2:C17,row(A2:A17)},3,0),9^9,2,1,1),0,2);if(,,)}},2,1)))lookup_value – E2:E4
lookup_array – A2:A17
return_array – C2:C17
Exact / Next Largest – First Value
Here is the final XLOOKUP alternative formula (cell G2) in Google Sheets. It follows an almost similar logic.
=ArrayFormula(ifna(vlookup(E2:E4,{A2:A17,C2:C17},2,0),vlookup(E2:E4,{{1/0;index(SORTN({A2:A17,C2:C17},9^9,2,1,1),0,1)},{index(SORTN({A2:A17,C2:C17},9^9,2,1,1),0,2);if(,,)}},2,1)))lookup_value – E2:E4
lookup_array – A2:A17
return_array – C2:C17
Similar: Alternatives to the XMATCH Function in Google Sheets.
Change the above references as per your data source.
That’s all. Thanks for the stay. Enjoy!
