CODEWITHSUNDEEP
excelexcel-formula
Mads Hjorth
Mads Hjorth

Reputation: 447

VLOOKUP + MATCH and MAX value

Hi i am trying to do a VLOOKUP with match and i succesfully did. My formula looks like this:

VLOOKUP(A5;D:I;MATCH(B5;$A$1:$K$9;0);FALSE)

I know that VLOOKUP selects the first match, however i need it to pick the highest value. So my formula at the moment gives me value "100"(E5), when i need it to select value "300"(E6).

A    B              C           D       E      F       G    H       I

1    Company    Profession              1      2     3      4       5
2    A              2           A             534          531  
3    B              2           B      123          412     
4    C              1           C             213           
5    D              1           D      100          112     
6    D              1           D      300          231 
7    D              1           D      200          752     
8    E              3           E             123    55    431
9    F              4           F                                  112

Can you help me with this? I am absolutely lost. Thanks

Upvotes: 1

Views: 983

Answers (1)

Scott Craner
Scott Craner

Reputation: 152605

Try the following SUMPRODUCT() Formula:

=SUMPRODUCT(MAX(($E$2:$I$9)*(A5=$D$2:$D$9)*(B5=$E$1:$I$1)))

Or the following AGGREGATE() formula:

=AGGREGATE(14;6;($E$2:$I$9)/((A5=$D$2:$D$9)*(B5=$E$1:$I$1)),1)

Upvotes: 1

Related Questions