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.
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.
