Log In
Register
Home
January 26, 2020
Offline
Is it possible to use xlookup when the lookup array is a spilled array resulting from a vstack formula (essentially treating the spilled array as a table)? If so, how? The use case is my first attempt at working vstack into my workbook rather than appending tables in Power Query.
As I think about this, my question, more broadly, is how do you reference columns of a spilled array in a formula? This would apply to lookup ranges, sum ranges, etc.
Thanks in advance for any wisdom!
See Answer
July 16, 2010
Offline
Hi Tom,
Yes, you can feed the result of VSTACK into XLOOKUP, but you'd have to FILTER the VSTACK to only return the relevant column for XLOOKUP's lookup_array argument, and again for the return_array argument.
In which case, it might be easier/better/quicker for Excel to use VLOOKUP with VSTACK since you only need to provide the array once.
If you supply an example Excel file with some dummy data and your desired result, we can help you further.
Mynda
Trusted Members
Moderators
November 1, 2018
Offline
You can also use INDEX supplying 0 as the row number argument - for example:
=INDEX(I1#,0,3)
returns the third column of the spilled array starting in I1.
January 26, 2020
Offline
Thank you, Mynda and Velouria. Using INDEX for the lookup and return array arguments works like a champ and seems a little easier.
On a related note, do you have any general guidance as to when to use vstack vs power query? I can imagine power query is the answer when signficant shaping and cleaning is needed. But in cases where you have relatively clean and simple appends, are there circumstances that suggest one of the other method is preferred? My previous question is a case in point. I wasn't thinking ahead to how my stacked data was going to be used further, so I wasn't thinking about how the lack of structured references, as with tables, could be an issue.
July 16, 2010
Offline
Hi Tom,
I was assuming you were using VSTACK inside the lookup e.g.:
=XLOOKUP(lookup_value, INDEX(VSTACK(...),,1), INDEX(VSTACK(...),,2) )
or
=XLOOKUP(lookup_value, FILTER(VSTACK(...), ...), FILTER(VSTACK(...), ...) )
in which case these are very inefficient because the VSTACK is being calculated twice.
However, if you're using it to reference an already spilled VSTACK like Velouria's example, then that's fine. Just something to keep in mind if your file gets slow.
In terms of Power Query vs VSTACK:
Use VSTACK:
- if you need instant updates (i.e. don't want to have to click 'refresh' to get new data)
- small/quick solutions
Use Power Query:
- lots of data
- back compatibility with users not on 365 (i.e. those who don't have VSTACK)
- for analysis in PivotTables or the Data Model because you can load the data directly to the Pivot cache or data model which makes for a faster and smaller file.
Hope that helps.
Mynda
Answers Post
1 Guest(s)
