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...
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))
- This works great. Would you know a way to update this to also filter out any "Rule" that equals zero?
In your screenshot, the rules are text values. How would they be zero?