Forum Discussion
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 appreciate if any one could make some improvement.
Preconditions:
1: Obviously, length of rows can increase
2: There might be blank values so COUNTA, FILTER<>"" does not work
3: No VBA allowed
Current solutions I have:
Solution 1:
=XLOOKUP("BrownDog",A:A&B:B,C:C)
Disadvantage:
A:A&B:B will make excel very slow
Solution 2:
Add an auxiliary column/sheet with some =@A:A&@B:B, then use vlookup/xlookup
Disadvantage:
It will overflow, if there are too many pets
Solucation 3:
Get last cell in A:A with LOOKUP(2,1/(A:A<>""),SEQUENCE(1048576)), then use let/offset/index. There are some other alternatives to get last cell but their nature is similar
Disadvantage:
(1) Slow, although it's somehow faster than solution 1
(2) The grammer is ugly
Looking forward to better ways...Thank you!
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.
13 Replies
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.
PeterBartholomew1 SergeiBaklan Thank you for the answer!
I guess there is a problem of Table when changing source data.If I copy ENTIRE Sheet of new pet to cover the table, the table extend to 1048576 like below
I know someone might suggest that do not copy entire sheet, but it's actually a bad habit IMO. For example when there are fewer new pets like below, I would have to remember to manually delete some old pets
SergeiBaklan what do you mean by "take the range with some gap"? Do you mean formulas like 1:10000?
Sorry, missed recent posts. Yes, I mean like A1:A10000. On ten thousand rows it will be much faster that on million ones.
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.
=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.
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.
=INDEX($C$2:$C$8,SMALL(IF(($A$2:$A$8="brown")*($B$2:$B$8="dog"),ROW($A$1:$A$7)),1))You can try this formula. Enter the formula with ctrl+shift+enter if you don't work with Office 365 or Excel 2021.
