Lookup Value With Multiple Conditions in Excel

[Andy Nguyen]

Any Excel expert here can help?
I have a table in one of my spreadsheet that I'd like to look up the maximum value and other thing using Match, Index, redirect, etc. I tried QueryTable but the performance is really slow.

These are makeup values to demonstrate the idea.

ID    Price       [I]Date[/I]        Dealer 
[LEFT]  
2406    78    5/13/2008    Lehman Brothers 
2406    79    5/13/2008    Bear Stearns 
2406    77    5/13/2008    Citigroup 
2548    89    5/13/2008    Deutsche Bank 
2551    81    5/13/2008    Deutsche Bank 
1330    81    5/14/2008    Morgan Stanley 
1330    86    5/14/2008    Lehman Brothers 
1667    94    5/14/2008    Morgan Stanley 
1367    71    5/14/2008    Bear Stearns 
1367    69    5/14/2008    Morgan Stanley

Parameters to find max value of a given id on a given date
[/LEFT]
ID = 2406
Date = 5/13/2008
This should result in 79

Also, search parameters to find dealer name corresponding to the result above
ID = 2406
Date = 5/13/2008
This should result in Bear Stearns

 

[MikeK]

Andy,

Take a look at this link:

http://www.ozgrid.com/Excel/sum-if.htm

Using SUMPRODUCT has helped me in the past with your exact issue.

-Mike

 

[Andy Nguyen]

MikeK,
I actually used one of the threads there yesterday to do a lookup with 3 conditions
Vlookup Based On Two Conditions (columns) - Excel Help & VBA Help
I end up with a formula that looks like this
=INDIRECT("DEALERS_DATA!$P"&SUMPRODUCT((DEALERS_DATA!$N$11:$N$10000=$B14)*
(DEALERS_DATA!$R$11:$R$10000=$B$9)*(DEALERS_DATA!$S$11:$S$10000=$I$12)*
ROW(DEALERS_DATA!$P$11:$P$10000))))

I'm able to do a lookup but I want to know how to extend that look up to find the maximum value. I only posted here cause I couldn't find anything on ozgrid

 

[sjoo]

How about my solution?

 

[Andy Nguyen]

sjoo,
For the MAX value, I found out that I can use DMAX. It's a great way to search through table just you would database.

As for the dealer name, your solution will not be optimal because you are only matching the price. You also need to match by date and by ID. Think about the case where you have several dealers providing the quote for the same ID. I tried to extend your function to include 2 more MATCH, one for ID and one for date but I have a returning reference error.

I also look at DGET function but it needs a unique value.

 

[sjoo]

DGET() function

Andy,

You're right, the second is wrong answer for some cases. I think DGET() function is suitable for this case as you said. Because I had to hurry up going a chinese academy yesterday, I had not enought time to think over it.

This morning , I tried the 2nd problem with DGET() function