May 25 2021 05:29 AM
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:
May 25 2021 06:22 AM
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.
May 25 2021 06:43 AM
May 25 2021 07:20 AM
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))
May 25 2021 12:41 PM
May 25 2021 12:45 PM
In your screenshot, the rules are text values. How would they be zero?
May 25 2021 01:02 PM
May 25 2021 01:05 PM
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.
May 25 2021 01:55 PM
May 25 2021 02:28 PM
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).
May 25 2021 08:20 PM
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?
May 26 2021 04:31 AM
See the attached sample workbook.
May 26 2021 08:56 AM
May 26 2021 11:45 AM
SolutionMay 26 2021 07:47 PM
Jun 02 2021 06:56 AM
Jun 03 2021 04:03 PM
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.
Jun 07 2021 07:14 AM
May 26 2021 11:45 AM
Solution