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.
Sorry, missed recent posts. Yes, I mean like A1:A10000. On ten thousand rows it will be much faster that on million ones.
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.