lookup on similar text

[batkosta]

Hi guys,

In sheet 1 I've got column A with city names in the following format:

CITY HITS
London 30
Manchester 50
etc.

In sheet 2 I've got column A with city names but in a different format:

CITY SALES
London 10
London (Euston) 15
London (Picadilly) 8
Manchester (City Centre) 20
etc.

I would like to do a lookup on the range in sheet 2 and add the sum of sales by location next to the hits column in sheet 1. Somethin like that:

CITY HITS SALES
London 30 33
Manchester 50 20

I'm using lookups all the time but always with exact matches so I'm not sure what to do in this case. Also the cities in both ranges start with the city name as opposed to entries like 'Central London' or 'Soho (London)'. If there is no direct way of doing this, any suggestions on ways to achieve the solution are well appreciated.


Many Thanks,
K.

 

[BrianB]

Perhaps another column to extract the town with formula that finds the first bracket :-
=IF(ISERROR(FIND("(",A1,1)),A1,LEFT(A1,FIND("(",A1,1)-2))

 

[Oaktree]

Remember that you can use wildcards in sumif:

H2 =SUMIF(A:A,G2&"*",B:B) copied down
I2 =SUMIF(D:D,G2&"*",E:E) copied down

Book5
	A	B	C	D	E	F	G	H	I
1	List 1	Values 1		List 2	Values 2		Combined List	Sum of Values 1	Sum of Values 2
2	London	30		London	10		London	30	33
3	Manchester	50		London (Euston)	15		Manchester	50	20
4				London (Picadilly)	8
5				Manchester (City Centre)	20
6
Sheet1

 

[batkosta]

Brilliant, thank you.