• Hi All

    Please note that at the Chandoo.org Forums there is Zero Tolerance to Spam

    Post Spam and you Will Be Deleted as a User

    Hui...

  • When starting a new post, to receive a quicker and more targeted answer, Please include a sample file in the initial post.

Using 2 sets of criteria to return a value based on its max value

frankieB

New Member
Hi Guys,

New guy to the forum but not new to excel. I enjoyed searching for answers as I read some of your formulas. Really impressive.

I have a formula that I can't figure out for the life of me. Here's the logic and below is a screen shot for visual help.

  • Requires two sets of criteria (data in column A and B)
  • I need to return the 'Account Type' (column C)
  • The 'Account Type' I need to be returned is the one who has the maximum 'Gross' value (column D)
I know my formula isn't complete but I've been having issues with it for two reasons:
  • I can't use the whole column, only the last row (The data will increase/decrease in quantity, so I have to update the formula manually- e.g I have to update the row count- the ... $D$10 part)
  • I can't set the two criteria I need without getting an error
My Formula:

=INDEX(BSN!$E:$E,MATCH(MAX(MMULT($D$3:$D$10{1})),MMULT($D$3:$D$10,{1}),0))


The Visual:

upload_2018-10-18_21-13-52.png
 

Attachments

  • Excel Formula Help.PNG
    Excel Formula Help.PNG
    45.5 KB · Views: 3
upload_2018-10-19_12-55-39.png

1] In H3, enter :

=IFERROR(INDEX(C$3:C$10,MATCH(AGGREGATE(14,6,D$3:D$10/(A$3:A$10=F3)/(B$3:B$10=G3),1),D$3:D$10,0)),"")

2] In I3, enter :

=SUMIFS(D:D,A:A,F3,B:B,G3,C:C,H3)

Regards
Bosco
 

Attachments

  • 2CriteriaLookup.xlsx
    11.6 KB · Views: 7
Back
Top