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.

 

[ThatOneDude]

Blue - =INDEX(Sheet2!D:D, MATCH(1, ($D2=Sheet2!B:B)*(RIGHT($C2, 4)=Sheet2!C:C), 0))
Pink - =INDEX(Sheet2!D:D, MATCH(1, ($D4=Sheet2!B:B)*(RIGHT($C4, 4)=Sheet2!C:C), 0))
Green - =INDEX(Sheet2!D:D, MATCH(1, ($D6=Sheet2!B:B)*(RIGHT($C6, 4)=Sheet2!C:C), 0))
Peach - =INDEX(Sheet2!D:D, MATCH(1, ($D2=Sheet2!B:B)*($I$1=Sheet2!C:C), 0))
Yellow - =INDEX(Sheet2!D:D, MATCH(1, ($D2=Sheet2!B:B)*($J$1=Sheet2!C:C), 0))

 

[Fluff]

What version of Excel are you using?

I suggest that you update your Account details (or click your user name at the top right of the forum) so helpers always know what Excel version(s) & platform(s) you are using as the best solution often varies by version. (Don’t forget to scroll down & ‘Save’)

 

[osaben]

Blue - =INDEX(Sheet2!D:D, MATCH(1, ($D2=Sheet2!B:B)*(RIGHT($C2, 4)=Sheet2!C:C), 0))
Pink - =INDEX(Sheet2!D:D, MATCH(1, ($D4=Sheet2!B:B)*(RIGHT($C4, 4)=Sheet2!C:C), 0))
Green - =INDEX(Sheet2!D:D, MATCH(1, ($D6=Sheet2!B:B)*(RIGHT($C6, 4)=Sheet2!C:C), 0))
Peach - =INDEX(Sheet2!D:D, MATCH(1, ($D2=Sheet2!B:B)*($I$1=Sheet2!C:C), 0))
Yellow - =INDEX(Sheet2!D:D, MATCH(1, ($D2=Sheet2!B:B)*($J$1=Sheet2!C:C), 0))

appreciate your willingness to help. I am afraid that maybe my color coding created confusion.

I assume there would only need to be three different formulas:
1) Sheet1/Column H
2) Sheet1/Column I
3) Sheet1/Column J

When I copy the formulas down in each column, the data returned will vary based on the varying Fiscal years and Orgs.

 

[osaben]

What version of Excel are you using?

I suggest that you update your Account details (or click your user name at the top right of the forum) so helpers always know what Excel version(s) & platform(s) you are using as the best solution often varies by version. (Don’t forget to scroll down & ‘Save’)

Done. Thank you for the reminder.

 

[Fluff]

Thanks for that.
How about In H2 copied down

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

and in I2 copied down & across

=FILTER(Sheet2!$D$2:$D$1000,(Sheet2!$B$2:$B$1000=$D2)*(Sheet2!$C$2:$C$1000=I$1),"No match")

 

[osaben]

Thanks for that.
How about In H2 copied down

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

and in I2 copied down & across

=FILTER(Sheet2!$D$2:$D$1000,(Sheet2!$B$2:$B$1000=$D2)*(Sheet2!$C$2:$C$1000=I$1),"No match")

Getting closer.......the correct data is pulling into columns H-J for rows3, 5 & 7. But there is a No match for columns H-J for rows 2, 4 & 6).

In this example, There should be not instances where there is no match.

Thanks again.

 

[Fluff]

Check that you don't have any trailing spaces in col C of sheet1 & that you have actual numbers in col D

 

[osaben]

That was it!....there are trailing spaces in some of the cells in Column E.

I tried to use the find& replace option as well as the TRIM function and neither are working to remove the trailing spaces. any other suggestions?

please & thank you.

 

[Fluff]

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