XLOOKUP problems with Duplicates

[jet0005]

I need to match the gallons from Sheet 1 to the correct amount on Sheet 2 in the attached spreadsheet. XLOOKUP works until it hits a duplicate amount, then it gives the first gallon data that matches rather than the correct one. Sheet 2 contains many more amount cells than Sheet 1 so many of them will not have corresponding gallon data. What is a better way to do this?

[Gregb11]

I don't know how you're getting the Amount #'s when the Gallons aren't found, but otherwise, this seems to work:

On Sheet 1, Cell B3 and copy down:

=INDEX(FILTER(Sheet2!$A$2:$A$143,Sheet2!$B$2:$B$143=Sheet1!A3,""),COUNTIFS($A$3:A3,A3))

[wk9128]

Could worksheet or tab name "Sheet2" be placed in B2:B143, the most expected result?

[jet0005]

I should clarify: Sheet 1 is my raw data. I need the formula in the GAL column of Sheet two. Hope that helps.

[wk9128]

worksheet or tab name : Sheet2

Cell B2 formula , Drag down

Please Login or Register  to view this content.

[Gregb11]

In B2, copy down, then how about this?

=IFERROR(INDEX(FILTER(Sheet1!$A$3:$A$61,Sheet1!$B$3:$B$61=Sheet2!A2),COUNTIFS($A$2:A2,A2)),0)

[jet0005]

These both appear to have worked. Thank you for the help! Out of curiosity, what was your thought process behind this, and using the formulas you did, rather than XLOOKUP?

[Gregb11]

Because you didn't want the first match. If you wanted the first match, the XLOOKUP would be the way to go. But because you wanted to skip the first match if it was a repeat number, we had to do it the way we did.

[jet0005]

I have an add-on question. In my real spreadsheet there are several instances of the value 1.00 in the Gallon column from Sheet 1. I would like to show nothing or "" whenever there is a 1.00 value in the Gallon raw data. Is there a way we could add this logic into the formulas above?

[Gregb11]

Well, quickly I would just do this:

=LET(a,IFERROR(INDEX(FILTER(Sheet1!$A$3:$A$61,Sheet1!$B$3:$B$61=Sheet2!A4),COUNTIFS($A$2:A4,A4)),0),IF(a=1,"",a))

[wk9128]

@jet0005 You're Welcome. Glad to help . Thank You for the feedback and rep.