Vlookup in a range and summarize each total

[david_cc]

Hello guys,

Can someone help me with this case?

I want to sum all impressions per item in the LOOKUP list. The Input Name often contain others words which can be before or after the city name. Also there can be a difference in capital letter and small letter for city names. Please see the example:

[TABLE="width: 689"]
<tbody>[TR]
[TD]INPUT[/TD]
[TD]Impressions[/TD]
[TD][/TD]
[TD]LOOKUP[/TD]
[TD]OUTPUT[/TD]
[/TR]
[TR]
[TD]keyword 1 + Paris[/TD]
[TD="align: right"]2[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]keyword 2 + Paris[/TD]
[TD="align: right"]1[/TD]
[TD][/TD]
[TD]Paris[/TD]
[TD="align: right"]10[/TD]
[/TR]
[TR]
[TD]keyword 3 + Paris[/TD]
[TD="align: right"]3[/TD]
[TD][/TD]
[TD]Bordeaux[/TD]
[TD="align: right"]17[/TD]
[/TR]
[TR]
[TD]keyword 4 + Paris[/TD]
[TD="align: right"]4[/TD]
[TD][/TD]
[TD]Marseile[/TD]
[TD="align: right"]15[/TD]
[/TR]
[TR]
[TD]keyword 1 + Bordeaux[/TD]
[TD="align: right"]3[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]keyword 2 + Bordeaux[/TD]
[TD="align: right"]3[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]keyword 3 + Bordeaux[/TD]
[TD="align: right"]5[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]keyword 4 + Bordeaux[/TD]
[TD="align: right"]6[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]keyword 1 + Marseile[/TD]
[TD="align: right"]3[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]keyword 2 + Marseile[/TD]
[TD="align: right"]4[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]keyword 3 + Marseile[/TD]
[TD="align: right"]5[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]keyword 4 + Marseile[/TD]
[TD="align: right"]3[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]

Looking forward to some replies

best!

 

[63falcondude]

Try

Excel 2010
	A	B	C	D	E
1	INPUT	Impressions		LOOKUP	OUTPUT
2	keyword 1 + Paris	2
3	keyword 2 + Paris	1		Paris	10
4	keyword 3 + Paris	3		Bordeaux	17
5	keyword 4 + Paris	4		Marseile	15
6	keyword 1 + Bordeaux	3
7	keyword 2 + Bordeaux	3
8	keyword 3 + Bordeaux	5
9	keyword 4 + Bordeaux	6
10	keyword 1 + Marseile	3
11	keyword 2 + Marseile	4
12	keyword 3 + Marseile	5
13	keyword 4 + Marseile	3
Sheet4

Cell Formulas
Range	Formula
E3	=SUMIF(A:A,"*"&D3&"*",B:B)

 

[david_cc]

Try

Excel 2010

	A	B	C	D	E
INPUT	Impressions	LOOKUP	OUTPUT
keyword 1 + Paris
keyword 2 + Paris	Paris
keyword 3 + Paris	Bordeaux
keyword 4 + Paris	Marseile
keyword 1 + Bordeaux
keyword 2 + Bordeaux
keyword 3 + Bordeaux
keyword 4 + Bordeaux
keyword 1 + Marseile
keyword 2 + Marseile
keyword 3 + Marseile
keyword 4 + Marseile

<colgroup><col style="width: 25pxpx"><col><col><col><col><col></colgroup><thead>
</thead><tbody>
[TD="align: center"]1[/TD]

[TD="align: right"][/TD]

[TD="align: center"]2[/TD]

[TD="align: right"]2[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]3[/TD]

[TD="align: right"]1[/TD]
[TD="align: right"][/TD]

[TD="align: right"]10[/TD]

[TD="align: center"]4[/TD]

[TD="align: right"]3[/TD]
[TD="align: right"][/TD]

[TD="align: right"]17[/TD]

[TD="align: center"]5[/TD]

[TD="align: right"]4[/TD]
[TD="align: right"][/TD]

[TD="align: right"]15[/TD]

[TD="align: center"]6[/TD]

[TD="align: right"]3[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]7[/TD]

[TD="align: right"]3[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]8[/TD]

[TD="align: right"]5[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]9[/TD]

[TD="align: right"]6[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]10[/TD]

[TD="align: right"]3[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]11[/TD]

[TD="align: right"]4[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]12[/TD]

[TD="align: right"]5[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]13[/TD]

[TD="align: right"]3[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

</tbody>

Sheet4​

[TABLE="width: 85%"]
<tbody>[TR]
[TD]Worksheet Formulas[TABLE="width: 100%"]
<thead>[TR="bgcolor: #DAE7F5"]
[TH="width: 10px"]Cell[/TH]
[TH="align: left"]Formula[/TH]
[/TR]
</thead><tbody>[TR]
[TH="width: 10px, bgcolor: #DAE7F5"]E3[/TH]
[TD="align: left"]=SUMIF(A:A,"*"&D3&"*",B:B)[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[/TR]
</tbody>[/TABLE]

[/Nice Thanks a lot! Now I have the case that some city names look quite the same. For example:

Saint Malo
Saint Malon sur mel
Saint malo en donziois

How can I change the formula to only SUM the total of every seperate city?

Thanks in advance!

 

[63falcondude]

Nice Thanks a lot! Now I have the case that some city names look quite the same. For example:

Saint Malo
Saint Malon sur mel
Saint malo en donziois

How can I change the formula to only SUM the total of every seperate city?

Thanks in advance!

As far as I know, in this application, there is no way to tell "blahblah Saint Malo blahblah" apart from "blahblah Saint Malo sur mel blahblah" in one formula.