So you can use xlookup to reference another tab within the same sheet as follows: =xlookup(A1,Tab1!C:C,Tab1!D:D) Or you can refer to an entirely different spreadsheet by using importrange like this: =xlookup(A1,importrange("spreadsheet_url","Tab1!C:C"),importrange("spreadsheet_url","Tab1!D:D"))
No, it still only accepts a single search key (e.g. E4) on its own. To use a range of search keys, you still have to wrap it with the ArrayFormula e.g. =ArrayFormula(XLOOKUP(E4:E6,A4:A13,C4:C13))
It works with array formulas. The XLOOKUP on its own only accepts a single search key (e.g. E4). To lookup a range, you have to wrap it with the ArrayFormula e.g. =ArrayFormula(XLOOKUP(E4:E6,A4:A13,C4:C13)) Don't know how the speed compares to a VLOOKUP on big datasets, but that might be a topic for a future video.
Excellent Explanation and demo, New to Google sheets, way better than working it out for myself from the help. Thanks,
Ben, thank you very much for the very useful content. A very necessary and timely video. Happy New Year!
You're welcome! Happy New Year to you too!
Thank you!
Hi Ben, this is great, but could you show how to use this to lookup values in another Tab sheet within the Google Sheet? Thank you.
So you can use xlookup to reference another tab within the same sheet as follows:
=xlookup(A1,Tab1!C:C,Tab1!D:D)
Or you can refer to an entirely different spreadsheet by using importrange like this:
=xlookup(A1,importrange("spreadsheet_url","Tab1!C:C"),importrange("spreadsheet_url","Tab1!D:D"))
Helpful, thank you! Can XLOOKUP incorporate what before would be an array formula/Vlookup
No, it still only accepts a single search key (e.g. E4) on its own. To use a range of search keys, you still have to wrap it with the ArrayFormula e.g. =ArrayFormula(XLOOKUP(E4:E6,A4:A13,C4:C13))
Ben, how does the XLOOKUP function play with the ARRAYFORMULA function? And in terms of speed over large recordsets, would VLOOKUP be faster?
It works with array formulas. The XLOOKUP on its own only accepts a single search key (e.g. E4). To lookup a range, you have to wrap it with the ArrayFormula e.g. =ArrayFormula(XLOOKUP(E4:E6,A4:A13,C4:C13))
Don't know how the speed compares to a VLOOKUP on big datasets, but that might be a topic for a future video.
thanks 12:40
1:55 If you've ever created a Google sheet with more than 50,000 of rows you'll know why this feature is hilarious.
I live in hope that it'll get better for larger datasets! ;)