Avatar of newparadigmz
newparadigmz
Flag for United States of America asked on

Lookup formula with multiple criteria and dynamic column

What formula can produce these results? (that is fast, for a big data set)

lookup
Microsoft ExcelMicrosoft Office

Avatar of undefined
Last Comment
newparadigmz

8/22/2022 - Mon
Tom Farrar

Looks like you are just pivoting the data.  Couldn't a pivot table work?  I am guessing there is more to this...
Bill Prew

Here's an approach using INDEX and MATCH, not sure how well it will scale if you have an extremely large data set, but give it a try.

Not that these are array formulas so if you edit them you have to press CTRL+SHIFT+ENTER to accept the changes.

EE29174563.xlsx


»bp
ASKER CERTIFIED SOLUTION
Arana (G.P.)

THIS SOLUTION ONLY AVAILABLE TO MEMBERS.
View this solution by signing up for a free trial.
Members can start a 7-Day free trial and enjoy unlimited access to the platform.
See Pricing Options
Start Free Trial
GET A PERSONALIZED SOLUTION
Ask your own question & get feedback from real experts
Find out why thousands trust the EE community with their toughest problems.
byundt

Arana's approach (concatenating the lookup parameters) is going to be the fastest when you have a large lookup table. The speed will be up to a thousand times faster doing it that way when you use Office 365 compared to Excel 2016 perpetual (or earlier) because Microsoft is now indexing the lookup column.
This is the best money I have ever spent. I cannot not tell you how many times these folks have saved my bacon. I learn so much from the contributors.
rwheeler23
newparadigmz

ASKER
I see the point you are making, and understand, but how can it handle the new criteria around the funkiness of how they are using AMOUNT/NUMBER (different from original question before)

Also, a bit complicated as the data set comes from a database/data connection, which I now would need to "edit"post refresh with vba or something (still better than a crashing sheet)
newparadigmz

ASKER
This can be workable, but why am I getting any/wrong answers for the cells in red, they "should" be N/A.

almost working
byundt

You omitted the last parameter on the VLOOKUP. It should be either FALSE or 0, just before the rightmost parenthesis.

Without that parameter, Excel assumes your lookup table is sorted in ascending order by its first column and gives you an approximate match.
⚡ FREE TRIAL OFFER
Try out a week of full access for free.
Find out why thousands trust the EE community with their toughest problems.
newparadigmz

ASKER
omg, that's hilarious, my brain is fried. THANK YOU both soooo much....