Index Match Formula Please Help

Brian Piedlow 6 Reputation points
2020-10-19T18:47:36.167+00:00

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.

Excel Management
Excel Management
Excel: A family of Microsoft spreadsheet software with tools for analyzing, charting, and communicating data.Management: The act or process of organizing, handling, directing or controlling something.
1,649 questions

3 answers

Sort by: Most helpful
Most helpful Newest Oldest
  1. Brian Piedlow 6 Reputation points
    2020-10-20T12:12:23.797+00:00

    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.

    0 comments
  2. Brian Piedlow 6 Reputation points
    2020-10-20T12:15:05.877+00:00

    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.

    0 comments
  3. Emily Hua-MSFT 27,546 Reputation points
    2020-10-25T12:48:43.737+00:00

    @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.

    34843-捕获1.png

    34809-捕获2.png

    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.

    0 comments