Forum Discussion
Solved
Help with multiple conditional statements in Excel
Below is an example of a much larger dataset that I am working with. What I am having trouble figuring out is how to write a formula that does the following:
- Find a certain “Vendor Name” in column A.
- Based on the “Vendor Name” selected in column A, determine the highest “Total” in column D.
- Based on the highest “Total” in column D find the respective “Rule” in column E.
17 Replies
Let's say the vendor name you're looking for is in cell G2.
The rule you want is returned by
=INDEX($E$2:$E$200, MATCH(1, INDEX(($A$2:$A$200=G2)*($D$2:$D$200=MAXIFS($D$2:$D$200, $A$2:$A$200, G2)), , ), 0))
Adjust the ranges if needed. the formula can be filled down if you have other vendor names in G3, G4 etc.
- I don't seem to have the MAXIFS function in my version of Excel. Is there any way to use the AGGREGATE function in place of the MAXIFS function?
Try the following array formula, confirmed with Ctrl+Shift+Enter:
=INDEX($E$2:$E$200, MATCH(1, ($A$2:$A$200=G2)*($D$2:$D$200=MAX(IF($A$2:$A$200=G2, $D$2:$D$200))), 0))
