The results area is finding the largest top 4 costs in column A within the date range =IFERROR(LARGE(IF(Sheet1!$D$5:$D$4935>=$A$2,IF(Sheet1!$D$5:$D$20<=$B$2,Sheet1!$E$5:$E$20)),1),0)and then using index/match in column B =IFERROR(INDEX(Sheet1!F:F,MATCH(A5,Sheet1!E:E,0)),0) to reference that cost and pull which type of fruit. The problem is when you have duplicate costs it will scan and grab the first cost it sees and not check to see if it is within the date range. I tried If statements and I cannot get it to work. I only have excel 2019 capabilities.
| Date Range | |||||
|---|---|---|---|---|---|
| 3/10/25 | 3/14/25 | ||||
| Results | Date | Cost | Type | ||
| $15,000 | Berry | 3/5/25 | $1,000 | Apple | |
| $5,000 | Kiwi | Should be Grape | 3/6/25 | $2,000 | Orange |
| $2,000 | Orange | Should be Banana | 3/11/25 | $2,000 | Banana |
| $0 | 0 | 3/2/25 | $5,000 | Kiwi | |
| 3/1/25 | $200 | Melon | |||
| 3/14/25 | $15,000 | Berry | |||
| 3/13/25 | $5,000 | Grape |
The results area is finding the largest top 4 costs in column A within the date range =IFERROR(LARGE(IF(Sheet1!$D$5:$D$4935>=$A$2,IF(Sheet1!$D$5:$D$20<=$B$2,Sheet1!$E$5:$E$20)),1),0)and then using index/match in column B =IFERROR(INDEX(Sheet1!F:F,MATCH(A5,Sheet1!E:E,0)),0) to reference that cost and pull which type of fruit. The problem is when you have duplicate costs it will scan and grab the first cost it sees and not check to see if it is within the date range. I tried If statements and I cannot get it to work. I only have excel 2019 capabilities.
| Date Range | |||||
|---|---|---|---|---|---|
| 3/10/25 | 3/14/25 | ||||
| Results | Date | Cost | Type | ||
| $15,000 | Berry | 3/5/25 | $1,000 | Apple | |
| $5,000 | Kiwi | Should be Grape | 3/6/25 | $2,000 | Orange |
| $2,000 | Orange | Should be Banana | 3/11/25 | $2,000 | Banana |
| $0 | 0 | 3/2/25 | $5,000 | Kiwi | |
| 3/1/25 | $200 | Melon | |||
| 3/14/25 | $15,000 | Berry | |||
| 3/13/25 | $5,000 | Grape |
1 Answer
Reset to default 2Use AGGREGATE()
=IFERROR(INDEX(F:F,AGGREGATE(15,7,ROW($E$6:$E$11)/(($D$6:$D$11>=$A$2)*($D$6:$D$11<=$B$2)*($E$6:$E$11=A5)),COUNTIF($A$5:A5,A5))),"")
This makes sure we are using the same date range as well as the value. The COUNTIFS() is to deal with duplicate cost values in the top range.
A couple of notes. Use the full column of the return in the INDEX, that way you can use ROW in the AGGREGATE. In the AGGREGATE use only the range desired and not full column refs.
Pay attention to the COUNTIFS range, It anchors on the first cell in the return and the range grows as it is copied down, that is by design.