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)

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,613 questions

1. 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)

Thanks again,

Brian P.

2. 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)

Thanks again,

Brian P.

3. 27,461 Reputation points
2020-10-25T12:48:43.737+00:00

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.