Date Range Large Index/Match Duplicate
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 |
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.
- Codeigniter parentheses in dynamic Active Record query
- How to send cart data in hidden field and fetch it in codeigniter controller
- Sorting an array of objects by a property
- Sorting objects in array
- Codeigniter Insert Array to Database
- How to display an array of values in a table with javascript
- C# ToArray conversion merged into one line with resulting array element change
- accessing values in an ARRAY STRUCT in Google Bigquery
- Sort an object array by a column
- Push a new object into a sorted objectArray at the correct position to avoid re-sorting
- How do you natsort() by a column of an array of objects?
- How to create JSON Schema for an array of dictionaries with different type values
- Naturally sort a flat array without preserving original keys
- Create array of values from a dictionary of array of objects filtered by property
- How can I sort an alphanumerical Multidimensional Array in php?
- Naturally sort the rows of a 2d array by a column of alphanumeric values
- Sort an array of alphanumeric strings naturally
- Sort an array of strings by their 3rd character, then 4th, then 2nd
- Sort array based only on trailing numbers in strings
- Custom sort a flat array of scalar values so that integers are given top priority
- sort() prints 1 instead of the sorted array
- How to naturally sort a flat array of filenames (some ending with numbers, e.g. "-2")?
- Is there a shortcut way to return an array sorted in C#
- How to implement multiple custom sorting rules based on hyphen-delimited substrings?
- How can I access data of object stored in an array?
- How to include OR in Array formula where ONLY SOME criteria need to be TRUE - Excel
- How to fix 'Private field must be declared in an enclosing class'
- How can I write a Zig function that can accept and return strings?
- Search for groups of cells that contain values, calculate the difference, place result in adjacent column
- Including more commands if CSV column is not empty