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.
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)Hi Peter,
Yes, XMATH on ISTEXT work perfect for this particular case, assuming we have no mix of numbers and texts within the range.
As for the OFFSET. We may use INDEX/COUNTA instead if we afraid volatile functions, but both don't work in this case. OFFSET itself - yes, that's volatile function. TODAY is also volatile function. Both are extremely fast, see nothing wrong to use them in general, but all depends on concrete scenario.
Wrapping in lambda - yes, of course, it looks better. But with changing the logic for the debugging and testing we need to return back to simple LET.
Agreed, it is not the volatile function itself that is the problem; it is any computationally intensive calculations that depend on it.
Wrapping in lambda
Spreadsheets, by their nature, conflate two very different roles, that of the developer and that of the end-user. The end-user does not need to see how the calculation is performed, though testing that it delivers correct results may be good (an auditor may wish to see the function definition together with any available documentation).
Developers have different requirements and may well need to decompose the formula to test units of functionality within it independently.
Thank you. The limitations of formulas like 1:10000 are:
(1) I have to tell every user that the formula overflow at a certain number, which they will never remember(2) When user delete cells or lines, the formula will also change even though the cell is locked
(3) I can force 1:10000 by formulas like INDIRECT or OFFSET, but the grammar is bad. It will be difficult to maintain and upgrade
In conclusion, with help from all of you, I've realized that this issue does not have a perfect soluation in Excel formula for now. I will try VBA and Power Query.
Thank you again.
