Look up a value with multiple criteria. Theoretically should be a simple INDEX/MATCH!

This is not that difficult, I am certain... In fact, I am pretty sure I had the formula and then my computer died and now I can’t figure it out again.

I believe it should be a fairly straightforward INDEX/MATCH formula; I want to return a value from an array on a separate worksheet, using two criteria. More specifically, the two criteria are an employee name and a date and the value being returned would be the info on whether they worked that day, were off, were on vacation, etc. (See attached example)

What I thought was working, 30 seconds prior to my computer battery dying, was something along the lines of:

Please Login or Register  to view this content.

However, now that formula seems to be returning random values; values that are found in the array, but are not from the correct cell. I have left the formula in one column of the attached example so you can see what I mean.

I really appreciate this forum and thank you all in advance for your help!

P.S. I am using Excel 2007

Hmmm... I tried it, but the same problem remains- it appears to return random values from the array.

The excel file I attached was just a little snippet of the real spreadsheets, and I had selected to remove some columns/rows that did not pertain to the formula, but now I am wondering if it is these columns and rows that are interfering somehow? So I have reattached the file with the actual spreadsheets (minus anybody's last names, etc). Again, I would like the employees work status for the given days read from "Calendar" and returned to the appropriate cell in the "BF Schedule" sheet.

I appreciate your help and effort!

Originally Posted by tigeravatar

kcharles,

In Sheet1 cell B3, use this formula and copy over and down:
=INDEX(Sheet2!$B$2:$I$15,MATCH($A3,Sheet2!$A$2:$A$15,0),MATCH(B$2,Sheet2!$B$1:$I$1,0))

Thanks, tigeravatar!

That worked! And I believe I realize now why it wasn't working before -so I even learned something

Thanks again