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.
Since you say 365. Firstly, for me source data should be held as a Table to avoid referencing unused parts of the worksheet. In this instance, I also used defined names
attributes
= Table1[[Color]:[Pet]]
name
= Table1[Name]
required (range that evaluates to)
= {"Brown", "Dog"}
Matching columns of the table returns an array of TRUE/FALSE and then applying AND by row identifies the candidate rows that include the first match.
= LET(
ANDĪ», LAMBDA(x, AND(x)),
matches, attributes=required,
criterion, BYROW(matches, ANDĪ»),
XLOOKUP(TRUE, criterion, name, "Not found")
)
XLOOKUP can return the first or last match. Further modification is possible if you wish omitting "Brown" to return "Max" as the first dog of any color.