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.
 

Excel Facts

Create a chart in one keystroke
Select the data and press Alt+F1 to insert a default chart. You can change the default chart to any chart type

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
8,052
Office Version
  1. 365
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
 
Master Excel Bundle

Excel contains over 450 functions, with more added every year. That’s a huge number, so where should you start? Right here with this bundle.

Forum statistics

Threads
1,163,544
Messages
5,832,372
Members
430,128
Latest member
ojl987

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top