Only Return 1 Duplicate Value Based on If The Lowest Row# Duplicate Has Been Returned

Question

The title might be confusing. Long and short, I'm referencing dates in a specific column from sheet1 in comparison to the date on the calendar in sheet2. This is returning relative information from sheet1. Current formula is as follows: =IF(COUNTIF("SHEET1"!J:J,A4)=1,INDEX('SHEET1'!G:G,MATCH(A4,"SHEET1"!J:J,0)),"")

Date for sheet1 is in the J column, that cell needs to show the respective data from sheet1 column G. My problem stims from duplicate dates. I've added a hidden row# in sheet1 starting in 'sheet1'A2. How can I change this formula to return only matching data from the lowest row#?

I'm using Excel 2016. Screenshots for example:

(https://i.stack.imgur.com/T73nv.png)

(https://i.stack.imgur.com/Gmaw2.png)

Here is an example of what I'm hoping to achieve:

Sheet1:

Column A	Column B	Column C
1	1/1/2024	Data 1
2	1/1/2024	Data 2
3	1/2/2024	Data 3
4	1/2/2024	Data 4

Sheet2:

1/1/2024	1/2/2024
Data 1	Data 3
Data 2	Data 4

Answer 1

On 2016, you can use the non-exact match to get the final result. This is achieved by excel searching the range for the next larger value, then rolling back to the previous one. The caveat is your range has to be sorted in the ascending order, which it seems to be from your screenshot.

This modifies the false condition of your formula to search for the non-exact when there's more than 1 instance, but test to confirm it works as expected. Again, the importance comes from how your data is sorted.

=IF(COUNTIF("SHEET1"!J:J,A4)=1,INDEX('SHEET1'!G:G,MATCH(A4,"SHEET1"!J:J,0)),INDEX('SHEET1'!G:G,MATCH(A4,"SHEET1"!J:J,1))

You can also avoid the COUNTIF to further simplify the formula:

INDEX('SHEET1'!G:G,MATCH(A4,"SHEET1"!J:J,1)

If you're on 365, instead of using an INDEX/MATCH you can use XLOOKUP to define the search order which seems that it will simplify your problem.

The XLOOKUP is similar to how you'd use an index/match where you define the search array and return array, but it varies from other lookups where you can also define the "Search Mode", and you can use -1 to have it search from Last-to-First. This will return the last match for that date. It will also return 0 if there are no matches, you can change this to whatever result you desire.

=XLOOKUP( A4, J:J, G:G, 0, 0 , -1)

Answer 2

Ok it took a few youtube tutorials but I figured it out.

Problem 1 was me using 2 different sheets. Excel doesn't like complicated formulas also referencing multiple sheets.

Problem 2 was I didn't understand the final row reference in indexing. Instead of using 1 like you did, I used $A$4:A4 for cell A4, drag it down and A5 will always reference the next value.

Lastly, I switched to using the Aggregated formula.

Formula now goes: If - Index - Aggregate.

So instead of an exact match, I'm looking at row positions in conjunction with matching data. All of this combined fixed the issue for me and let me stage out my desired results where I wanted them. Here's the finished result: =if(rows(A$5:A5)<=$i9,INDEX($S:$S,(AGGREGATE(15,3,($V:$V=A4)/($V:$V=A4)*(ROW($V$1:$V$100)),ROWS($A$5:A5)))),"")

A4 is the date on the calendar, A5 is the first cell that should show the results. Column V is the list of dates in my data. Column S was the desired information relative to what date the container is expected to come in. I9 was the beginning of my CountIF formula, counting how many instances of the same date appeared. That way if we aren't expecting any trailers, no bogus data is listed.

Because of how the rows are referenced, you can manually change the row# at the end instead of dragging it. The data is essentially lined up together and the higher the number, the further in line you pick what data to show. $A$5:A5 will show the first result. $A$5:A6 will show the 2nd, $A$5:A7 will show the 3rd, etc. I hope my solution helps others in a similar place as me.