 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:

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. 17 Replies

Re: Help with multiple conditional statements in Excel

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.

Re: Help with multiple conditional statements in Excel

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?

Re: Help with multiple conditional statements in Excel

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))

Re: Help with multiple conditional statements in Excel

This works great. Would you know a way to update this to also filter out any "Rule" that equals zero?

Re: Help with multiple conditional statements in Excel

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

Re: Help with multiple conditional statements in Excel

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"?

Re: Help with multiple conditional statements in Excel

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.

Re: Help with multiple conditional statements in Excel

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.

Re: Help with multiple conditional statements in Excel

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).

Re: Help with multiple conditional statements in Excel

@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?

Re: Help with multiple conditional statements in Excel

See the attached sample workbook.

Re: Help with multiple conditional statements in Excel

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 (Occasional Contributor)
Solution

Re: Help with multiple conditional statements in Excel

See the new version.

Warning: it doesn't handle ties.

Re: Help with multiple conditional statements in Excel

Thanks again! This works perfectly!

Re: Help with multiple conditional statements in Excel

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.

Re: Help with multiple conditional statements in Excel

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.

Re: Help with multiple conditional statements in Excel

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