help with lookup

[Brian Teer]

Hi,

I have a spreadsheet with a table similar to the following:

Date Category Jones Smith Williams
1/15/04 Planned Hours 10 20 30
1/15/04 Actual Hours 8 25 20
1/31/04 Planned Hours 15 20 30
....

I would like to be able to look up the Planned Hours for Smith for
1/15/04, meaning I need 3 criteria to get to the single cell. I don't
see how to do this with LOOKUP, HLOOKUP, VLOOKUP, MATCH, etc.

Can anyone help with this? Thanks in advance.

 

[Domenic]

Hi Brian,

Try...

=INDEX(C2:E4,MATCH(1,(A2:A4=DATE(2004,1,15))*(B2:B4="Planned
Hours"),0),MATCH("Smith",C1:E1,0))

....entered using CONTROL+SHIFT+ENTER.

Hope this helps!

 

[Frank Kabel]

Hi
try
=SUMIF(A1:A100,DATE(2004,1,15),OFFSET(B1:B100,0,MATCH("Smith",C1:X1,0))
)