sumproduct problem

[Norbert Jaeger]

Hi there,

I've got following constellation:
A1 to C4 is a little database
in A6, I put in a name like CAIRO
in A7, I put in a description like CA M 4


Whenever CA is part of the description, the formula must give me C817
as result in cell A9. My problem is, that 'CA' is only a part of the
description.


A B C
1 CAIRO PB M 6 C816
2 CAIRO CA M 4 C817
3 CAIRO PB M 3 C816
4 CAIRO CA M 2 C817
5
6 CAIRO
7 CA M 4
8
9 =SUMPRODUCT(((A1:A3=A6)*(B1:B3=A7))*C1:C3)

Can anyone help me please?

Thanks a lot

Norbert

 

[Frank Kabel]

Hi
try the following array formula (entered with CTRL+SHIFT+ENTER):
=INDEX(C1:C4,MATCH((A1:A4=A6)*(B1:B4=A7),0))

 

[Tom Ogilvy]

=INDEX(C1:C3,SMALL(IF((A1:A3=A6)*(LEFT(B1:B3,2)=LEFT(A7,2)),ROW(C1:C3)),1),1
)

Entered with Ctrl+Shift+Enter rather than just enter since this is an array
formula.