Index and Match I Presume...Maybe

[AirgasRob]

A B C D E
1 01/01/09 02/01/09 03/01/09
2 TEST1 TODAY1 1 2 3
3 TEST2 TODAY2 4 5 6
4 TEST1 TODAY2 7 8 9

Criteria:
A5 = "TEST1"
A6 = "TEST2"
A7 = "02/01/09"

If row = A5(Column A) and A6(Column B), intersect on column = A7 (Row 1).
Need the answer to return as "8".

 

[NBVC]

A B C D E
1 01/01/09 02/01/09 03/01/09
2 TEST1 TODAY1 1 2 3
3 TEST2 TODAY2 4 5 6
4 TEST1 TODAY2 7 8 9

Criteria:
A5 = "TEST1"
A6 = "TEST2"
A7 = "02/01/09"

If row = A5(Column A) and A6(Column B), intersect on column = A7 (Ro
1).
Need the answer to return as "8".

Try

=SUMPRODUCT(($A$2:$A$4=A5)*($B$2:$B$4=A6)*($C$1:$E$1=A7),$C$2:$E$4

--
NBV

Where there is a will there are many ways.

'The Code Cage' (http://www.thecodecage.com

 

[T. Valko]

Is there a typo in your criteria?

Criteria:
A5 = "TEST1"
A6 = "TEST2"
A7 = "02/01/09"

Is A6 supposed to be TODAY2?

If so, here's one way...

Array entered** :

=INDEX(C2:E4,MATCH(1,(A2:A4=A5)*(B2:B4=A6),0),MATCH(A7,C1:E1,0))

** array formulas need to be entered using the key combination of
CTRL,SHIFT,ENTER (not just ENTER). Hold down both the CTRL key and the SHIFT
key then hit ENTER.

 

[StonyfieldRob]

I should change my name to TypoRob =)

Thank you very much that is exactly what I wanted.

 

[T. Valko]

You're welcome. Thanks for the feedback!