lookup on similar text

batkosta

New Member
Joined
May 27, 2005
Messages
33
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.
 

Some videos you may like

Excel Facts

Can you AutoAverage in Excel?
There is a drop-down next to the AutoSum symbol. Open the drop-down to choose AVERAGE, COUNT, MAX, or MIN

BrianB

Well-known Member
Joined
Feb 17, 2003
Messages
8,127
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

MrExcel MVP
Joined
Jun 20, 2002
Messages
7,988
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
ABCDEFGHI
1List 1Values 1List 2Values 2Combined ListSum of Values 1Sum of Values 2
2London30London10London3033
3Manchester50London (Euston)15Manchester5020
4London (Picadilly)8
5Manchester (City Centre)20
6
Sheet1
 

Watch MrExcel Video

Forum statistics

Threads
1,119,268
Messages
5,577,102
Members
412,768
Latest member
klig
Top