Forum Discussion
Advanced Excel Formula discussion - BEST way of 'vlookup' with multiple columns
I tried above formulae on about 1 million of rows with data and 500 same formulae to recalculate. From above XLOOKUP() and INDEX/MATCH gives approximately the same, INDEX/SMALL ~65% slower and VLOOKUP 2 times slower.
Didn't test the recent from Peter, but I don't think BYROW() will be faster than simple XLOOKUP().
IMHO, most optimal, as Peter suggested, to work with structured table, when we have no problems with calculating of dynamic ranges.
If structured table is not suitable for some reasons, I'd simply take the range with some gap. Don't think that real data is on million rows range.
I tried above formulae on about 1 million of rows with data and 500 same formulae to recalculate. From above XLOOKUP() and INDEX/MATCH gives approximately the same, INDEX/SMALL ~65% slower and VLOOKUP 2 times slower.
Didn't test the recent from Peter, but I don't think BYROW() will be faster than simple XLOOKUP().
IMHO, most optimal, as Peter suggested, to work with structured table, when we have no problems with calculating of dynamic ranges.
If structured table is not suitable for some reasons, I'd simply take the range with some gap. Don't think that real data is on million rows range.
PeterBartholomew1 SergeiBaklan Thank you for the answer!
I guess there is a problem of Table when changing source data.If I copy ENTIRE Sheet of new pet to cover the table, the table extend to 1048576 like below
I know someone might suggest that do not copy entire sheet, but it's actually a bad habit IMO. For example when there are fewer new pets like below, I would have to remember to manually delete some old pets
SergeiBaklan what do you mean by "take the range with some gap"? Do you mean formulas like 1:10000?
Sorry, missed recent posts. Yes, I mean like A1:A10000. On ten thousand rows it will be much faster that on million ones.
Hi Sergei
I haven't given much thought to dynamic ranges since the advent of Tables.
The old use of OFFSET was volatile and somewhat expensive.
I wonder what the performance of using XMATCH to search a 'semi-infinite' range to return the table dimension and then using TAKE to pick out the relevant 'finite' ranges would be like?
= LET( dimension, XMATCH(TRUE, ISTEXT(nameRng),,-1), name, TAKE(nameRng, dimension), color, TAKE(descriptionRng, dimension, 1), pet, TAKE(descriptionRng, dimension,-1), match, (pet=petReq)*(color=colorReq), XLOOKUP(1, match, name, , ,-1) )Of course, I prefer not to show such formulas on a worksheet for fear of 'scaring the natives', so I would most likely wrap it within a Lambda for simplicity!
= FinalMatchλ(colorReq, petReq)
Somewhere along the line, I expect the user to take responsibility and declare what regions of the worksheet are committed to holding the data model and what is to be regarded as mere graffiti generated by Larry the cat as he traversed the keyboard.
The Table is one of the more valuable features of Excel because it expands automatically to include new manual data input. Power Query is better if you are bringing in fresh data as an ETL (extract/ transform/ load) activity.
Pasting over existing named ranges will convert them to #REF! errors so they would need to be reworked to apply to the new data.
- Thank you for the response Peter. I might stay with XLOOKUP now and start to learn Power Query
