Forum Discussion

qazzzlyt's avatar
qazzzlyt
Brass Contributor
Jun 03, 2023
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...
excel
Formulas and Functions
office 365
  • SergeiBaklan's avatar
    SergeiBaklan
    Jun 03, 2023

    qazzzlyt 

    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.

HansVogelaar's avatar
HansVogelaar
MVP
Jun 03, 2023

qazzzlyt 

Or

 

=INDEX(C2:C1000, MATCH(1, (A2:A1000="Brown")*(B2:B1000="Dog"), 0))

 

If you do not have Microsoft 365 or Office 2021, confirm by pressing Ctrl+Shift+Enter.

Resources