Vlookup in Schedule

[todmac]

I have a spreasheet that looks like the attached. The date is merged into 2 cells, with the daytime and afternoon shifts mated to that, and then the available number of people beside it.

I want to perform a lookup on this data from another sheet, however I need to look up the date AND if it is Daytime or Afternoon shift.

Can someone please help with which formula(s) I would use.

Thanks.

[NBVC]

The recommendation is not to have Merged Cells, and instead list the date in every cell in column A, then you can use:


=SUMPRODUCT(--(A3:A6=I2),--(B3:B6=J2),D3:D6)

where I2 and J2 contain the Date and Shift to look up, respectively.

If you must have the date column that way, then add a helper column, e.g. in E3: =LOOKUP(9.999E+307,A$3:A3) copied down and then use

=SUMPRODUCT(--(E3:E6=I2),--(B3:B6=J2),D3:D6) with same conditions above.

[todmac]

Brilliant! Thank you so very much!