Index Match Formula Please Help

Question

Hello everyone,
I've been trying to create an Excel formula and for some reason, the formula isn't cooperating. The objective of the formula is to match an employee ID from a list of IDs in two worksheets, then match two dates from the same two worksheets, and return a value from a range of dates found on the worksheet named EMPProd1 to a cell located on the second worksheet named Log Book.

Below are two separate formulas that I've tried so far and in theory, they should work, but for some reason, they don't. Can someone please help me?

=INDEX(EMPProd1,MATCH('Log Book Temp.'!BC1,EMPProd1[[#All],[RACF]],MATCH(EMPProd1[[#All],[RACF]],0)

=INDEX(Table2[#All],MATCH(BC1,Table2[RACF])*(BD33,Table2[#Headers]),0)

Thank you,

Brian P.

Answer 1

Yes, sorry about that. These are the two formulas that I've tried so far.

=INDEX(EMPProd1,MATCH('Log Book Temp.'!BC1,EMPProd1[[#All],[RACF]],MATCH(EMPProd1[[#All],[RACF]],0)

=INDEX(Table2[#All],MATCH(BC1,Table2[RACF])*(BD33,Table2[#Headers]),0)

Thanks again,

Brian P.

Answer 2

Yes, sorry about that. These are the two formulas that should work but I'm not sure why they won't.

=INDEX(EMPProd1,MATCH('Log Book Temp.'!BC1,EMPProd1[[#All],[RACF]],MATCH(EMPProd1[[#All],[RACF]],0)

=INDEX(Table2[#All],MATCH(BC1,Table2[RACF])*(BD33,Table2[#Headers]),0)

Thanks again,

Brian P.

Answer 3

@Brian Piedlow ,

Could you please use the array formula as a workaround?
In general array formulas, we may just use "&" to combine 2 lookup_values and lookup_arraies.

Here are 2 simple worksheets according to you description.

To get the value which match the same ID and Date, please enter the formula =INDEX(EMPProd1!$D$2:$D$13,MATCH('Log BooK'!$A2&'Log BooK'!$B2,EMPProd1!$A$2:$A$13&EMPProd1!$B$2:$B$13,0)), press Enter+Shift+Ctrl.

Any misunderstanding, please feel free to let me know.

---

If an Answer is helpful, please click "Accept Answer" and upvote it.
Note: Please follow the steps in our documentation to enable e-mail notifications if you want to receive the related email notification for this thread.