Forum Discussion
Solved
Advanced Excel Formula discussion - BEST way of 'vlookup' with multiple columns
What is the name of the first Brown Dog? This question seems easy, but I have been looking for the best soluation in Excel formula for years, and still can't find a perfect one. Would appreciat...
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.
=VLOOKUP(E2&F2,CHOOSE({1,2},A2:A8&B2:B8,C2:C8),2,0)Or with VLOOKUP and dynamic search criteria in cells E2 an F2. Enter the formula with ctrl+shift+enter if you don't work with Office 365 or Excel 2021.