1

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.

enter image description here

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
Improve this question

1 Answer 1

Reset to default
2

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

enter image description here

Improve this answer
Sign up to request clarification or add additional context in comments.

1 Comment

Got it. Thanks for the help.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.