Index and Matching

I have 7 worksheets. 6 that have data labeled animal names on here like cat and mouse and then sheet 1 which is a summary tab. Each of the animal worksheets have dates along row 8 that should math to the dates on sheet 1 in column C. I want the word in C4 : research & development, to be found on the animal worksheets and then the ABSOLUTE VALUE of whatever is in the matching cell to be brought back that corresponds to the year.
Ideally, i can use this chart and replicate it for a ton of other items like sales & marketing or general & administrative. it is not the case that every worksheet has all these items so then it should just be blank (not an error message) and it is not the case that every row matches across these animal name files - things can be located on different rows. I divide the formula by 100 because i want to format as a %. I tried a formula that did not work - I'm hoping its a small fix: =INDEX(INDIRECT("'" & D3 & "'!1:1048576"), MATCH(C4, INDIRECT("'" & D3 & "'!A:A"), 0), MATCH(TEXT($C$6, "mm/yyyy"), INDIRECT("'" & D3 & "'!10:10"), 0))/100

feel free to use a completely different formula too as I'm not that proficient yet with advanced formulas.

You didn't give us expected answers, so this is a guess!!

D5, copied across and dnown:

=IFERROR(INDEX(INDIRECT("'"&D$3&"'!1:1048576"),MATCH($C$4,INDIRECT("'"&D$3&"'!A:A"),0),MATCH($C5,INDIRECT("'"&D$3&"'!8:8"),0))/100,"")

NOTE... The purple cells in the raw data sheets have a new formula to ensure that the dates match. Simply subtracting 365 falls over whn there's a leap year.

Do you mean this?

=IFERROR(ABS(INDEX(INDIRECT("'"&D$3&"'!1:1048576"),MATCH($C$4,INDIRECT("'"&D$3&"'!A:A"),0),MATCH($C5,INDIRECT("'"&D$3&"'!8:8"),0))/100),"")

You're welcome. Thanks for letting us know that you got an answer.

---

Please take a moment and consider clicking the "Add Reputation" button at the foot of any of the posts of anyone who helped you reach a solution here today.

Finally, if that takes care of your original question, please click on "Thread Tools" from the menu link (just above the first post in the thread) and mark this thread as SOLVED.