SOLVED

Help with multiple conditional statements in Excel

Copper Contributor

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: 

  1. Find a certain “Vendor Name” in column A. 
  2. Based on the “Vendor Name” selected in column A, determine the highest “Total” in column D. 
  3. Based on the highest “Total” in column D find the respective “Rule” in column E. 

 

gtsmith2_0-1621945573465.png

 

 

17 Replies

@gtsmith2 

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?

@gtsmith2 

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?

@gtsmith2 

In your screenshot, the rules are text values. How would they be zero?

Sorry I should have been more thorough in the example. There are some "Rules" that are blank in my actual data set that appear as zero when I run your formula. Does that make sense?

Also, do you know a way that this formula could be modified to calculate the 2nd, 3rd, 4th and 5th "Rule" with the highest "Total"?

@gtsmith2 

To exclude empty rules:

 

=INDEX($E$2:$E$200, MATCH(1, ($E$2:$E$200<>"")*($A$2:$A$200=G2)*($D$2:$D$200=MAX(IF(($E$2:$E$200<>"")*($A$2:$A$200=G2), $D$2:$D$200))), 0))

 

again confirmed with Ctrl+Shift+Enter.

I'll have to think about your other question.

Thank you so much!
Regarding my second question I'm thinking that the AGGREGATE function might work but I have not had any luck with it.

@gtsmith2 

Assuming that the first formula is in row 2 (for example in H2):

 

=IFERROR(INDEX($E$2:$E$200, MATCH(1, ($E$2:$E$200<>"")*($A$2:$A$200=$G$2)*($D$2:$D$200=LARGE(IF(($E$2:$E$200<>"")*($A$2:$A$200=$G$2), $D$2:$D$200), ROW()-1)), 0)), "")

 

Fill down to row 6 (or however far you want).

 

@Hans Vogelaar

 

The formula posted at 1:05pm works perfectly. The last formula posted for some reason I cannot get to work. It does not error but when the function is entered/run nothing populates in the cell. Any suggestions? 

@gtsmith2 

See the attached sample workbook.

This works perfectly! Thank you again!

Based on having this setup I am thinking I would like to add some conditional logic on the "Category" and "Type" columns. Would that be a possible enhancement to the formulas provided in the sample workbook above? Thanks again!
best response confirmed by gtsmith2 (Copper Contributor)
Solution

@gtsmith2 

See the new version.

Warning: it doesn't handle ties.

Thanks again! This works perfectly!
Hi Hans, so while I was finalizing my spreadsheet I noticed that the ties were a larger and more frequent problem than I first thought. Do you know of a way for the formulas that you created to handle ties. Basically some additional conditional logic that says if the "rule" is repeated then go to the next "rule" with the same total.

@gtsmith2 

See the attached version. I inserted a helper column that adds a very small disturbance to the Total amounts. You can hide this column if you wish.

Thank you for the help on this! This modification helps tremendously!
1 best response

Accepted Solutions
best response confirmed by gtsmith2 (Copper Contributor)
Solution

@gtsmith2 

See the new version.

Warning: it doesn't handle ties.

View solution in original post