newparadigmz

asked on

# Lookup formula with multiple criteria and dynamic column

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

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

EE29174563.xlsx

»

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

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

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.

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)

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)

ASKER

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.

Without that parameter, Excel assumes your lookup table is sorted in ascending order by its first column and gives you an approximate match.

ASKER

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