Return specific value if two conditions are met

**livanoff** · 08-23-2011, 02:23 PM

I'm creating an attendance tracker based and I want to populate the cells in the Calendar tab with data from the Log tab.

Basically, in each cell, I want to return the code that matches both the date and the employee's name, if an entry for that employee on that date exists at all.

Sorry if I didn't explain very well - I've been fighting with Excel for 2 days and am not clear-minded at all when it comes to functions right now.

**NBVC** · 08-23-2011, 02:41 PM

Try:

=IFERROR(INDEX(Log!$C$2:$C$4,MATCH(1,INDEX((Log!$A$2:$A$4=B$1)*(Log!$B$2:$B$4=$A2),0),0)),"")

copied down and across.. adjust ranges to suit.

**ZENITH** · 08-23-2011, 02:57 PM

Hey NBVC do you know why =IF(LOOKUP($A2,Log!$B$2:$B$4,Log!$A$2:$A$4)=B$1,"Yes","No") wouldn't work for this? It seems to error out to 0 for 9/3/2011 but works for the others...

**NBVC** · 08-23-2011, 03:05 PM

Two things.

Lookup requires the lookup array to be listed in ascending alphabetic or numeric order to work properly and Lookup looks for the last time any of the lookup range cell values is equal to or smaller than the lookup value. So after sorting, it will look only for the last Jane Doe, not both.