sumproduct problem

[wrz]

Hey, the problem is that i want to use sumproduct based on 2 ranges to search from third range, the problem is that in the third range are codes which include text strings : TT9298473 so in the 1-st range are car registration numbers and in the second range are dates, if they match the formula should output the corresponding code from 3-rd range

Is there a way that sumproduct accepts text strings ???



My current formula looks like this :
=SUMPRODUCT(--(Kütusetabel!$C$3:$C$200=$A$1);--(Kütusetabel!$D$3:$D$200=$A4);(Kütusetabel!$K$3:$K$200))

Would someone PLEASE post a solution I really need the solution

 

[vane0326]

Hi barry houdini,

Just a slight modification to the formula:

=LOOKUP(1,1/((A1:A4=B10)*(B1:B4=C10)),C1:C4)

The formula would grab the first match not the last match.

Remember this,

In the world of Kung Fu, Speed determines the winner

Book555555.xls
	A	B	C	D	E	F
1	A1	B1	C1
2	A2	B2	C2
3	A2	B3	C3
4	A2	B2	C4
5
6
7
8
9		A1	B1
10		A2	B2
11
12
13				C1	C2
14
Sheet1

 

[Yogi Anand]

Hi vane0326:

Let us look at the following illustration ... this may clear up the situation when there is more than one match ...

Book1
	A	B	C	D	E	F
1	A1	B1	C1
2	A2	B2	C2
3	A2	B3	C3
4	A2	B2	C4
5	A1	B1	C7
6
7
8				FirstMatch	LastMatch
9		A1	B1	C1	C7
10		A2	B2	C2	C4
11
Sheet5

formula in cell D9 is an array formula ...

=INDEX($C$1:$C$5,MATCH(1,1/(($A$1:$A$5=B9)*($B$1:$B$5=C9)),0))

and the formula in cell E9 is ...

=LOOKUP(2,1/(($A$1:$A$5=B9)*($B$1:$B$5=C9)),$C$1:$C$5)

 

[Aladin Akyurek]

Hi, Aladin,
Wrz (OP) didn't confirm my table was OK
little typo ";" should be "," else I'm missing something
=INDEX(Kütusetabel!$K$3:$K$200,MATCH(1,(Kütusetabel!$C$3:$C$200=$A$1)*(Kütusetabel!$D$3:$D$200=$A4),0))

is this a good alternative or is it slower
(I like this syntax)
=INDEX(Kütusetabel!$K$3:$K$200,MATCH($A$1&$A4,Kütusetabel!$C$3:$C$200&Kütusetabel!$D$3:$D$200,0))
(confirm with Control-Shift-Enter)

best regards,
Erik

The OP appears to have semi-colon as list-separator.

SumProduct (and SumIf) can be sometimes called in a retrieval formula if the object of the retrieval is a number and there are no duplicate records. The OP wants to retrieve while the data is non-numeric. Also, there is ordinarily no need to invoke such formulas.

What I provided

{=INDEX(Kütusetabel!$K$3:$K$200;MATCH(1;(Kütusetabel!$C$3:$C$200=$A$1)*(Kütusetabel!$D$3:$D$200=$A4);0))}

fits the data structure the formula the OP posted implicates.

Is this INDEX formula slower?

Yes, it is slower than ordinary:

=INDEX(Kütusetabel!$K$3:$K$200;MATCH($A1&"#"$A4;Kütusetabel!$E$3:$E$200;0)

where E3 on Kütusetabel concatenates C3 and D3 with =C3&"#"&D3.

It's faster than:

=LOOKUP(2,1/((Kütusetabel!$C$3:$C$200=$A$1)*(Kütusetabel!$D$3:$D$200=$A4)),Kütusetabel!$K$3:$K$200)

which is an idiom to retrieve the last instance of the values of interest (see http://tinyurl.com/7ysq5 for an explanation).

INDEX/MATCH with MATCH's match-type set to 1 is as fast as LOOKUP. That is, 2 function calls against 1 does not make any difference. That is, the division that LOOKUP needs which explains the speed difference.

What would be the choice, given the data that you provided?

Book9
	A	B	C	D	E	F	G	H	I
1	Field-1	Field-2	Field-3	Concat			LookupValue-1	LookupValue-2	Result
2	A1	B1	C1	A1#B1		1)	A2	B2	C2
3	A2	B2	C2	A2#B2		2)	A2	B2	C2
4	A2	B3	C3	A2#B3		3)	A2	B2	C4
5	A2	B2	C4	A2#B2
6
Sheet1

If we want the first match:

1) =INDEX(C2:C5,MATCH(G2&"#"&H2,D2:D5,0))

which is supported by a concatenation range.

2) {=INDEX(C2:C5,MATCH(1,(A2:A5=G3)*(B2:B5=H3),0))}

If we want the latest match:

3) =LOOKUP(2,1/((A2:A5=G4)*(B2:B5=H4)),C2:C5)

The latter will come up with the only match (thus the first match so to speak) if there are no duplicate records.

Note that (3) does not need control+shift+enter for LOOKUP like SUMPRODUCT always operates on computed arrays. A formula that operates on computed arrays is by definition an array formula regardless whether it needs control+shift+enter or not.

Also be aware that replacing 2 with 1 would work with conditionals evaluating text arrays, it won't get you the first match in other cases.

 

[erik.van.geit]

Aladin,
this is science
a good start to write a book on this stuff !
(or did you write one already?)

kind regards,
Erik

 

[wrz]

Thnx a lot, wasn't so difficult at all, I'm just new in excel :P

 

[vane0326]

Wow!
Everbody is comming out of the wood work.