cross reference two cells in one sheet against two cells in second sheet - return data from third cell

[osaben]

Hello,
Help please. I have myself all confused on how to accomplish the following.

Column H2/Sheet1 --- match data in last 4 characters in C2 & all of D2 against data in column B & C in Sheet2 and return the value in column D
Column I2/Sheet1 --- match data in I1 & D2 against data in column B & C in Sheet2 and return the value in Column D
Column J2/Sheet 1 --- match data in J1 & D2 against data in column B & C in Sheet2 and return value in column D

SO----when the formula is working right, the figures in Sheet1 with match those in Sheet2 (I color coded to help with explanation)

Thanks in advance for your help and expertise.

 

[osaben]

The formula is not looking at col E, wo which column has the trailing spaces?

Oh, My apologies. the trailing spaces are in column D, which is the year and is part of the formula.

 

[Fluff]

Ok, how about

=FILTER(Sheet2!$D$2:$D$1000,(Sheet2!$B$2:$B$1000=trim(D2))*(Sheet2!$C$2:$C$1000=RIGHT(C2,4)),"No match")

 

[osaben]

Ok, how about

=FILTER(Sheet2!$D$2:$D$1000,(Sheet2!$B$2:$B$1000=trim(D2))*(Sheet2!$C$2:$C$1000=RIGHT(C2,4)),"No match")

Yes! that worked!

Have a great day and thank you for your willingness to respond to my inquiries. I am thankful for you.

 

[Fluff]

You're welcome & thanks for the feedback.