Getting Data from one spreadsheet into another

[Guest]

I have 2 spreadsheets. 1 contains a list of customers with full post code ie

ABC Ltd/123 High Street/ London/ N15 ABC

The post code in the final column could be either 6 digits (as above with
space in middle) or 5 digits ie B1 EDR, 7 digits ie WC1A BNM

I have another spreadsheet detailing a sales consultant with the postcodes
they cover ie

Sally Smith/ N15
Audrey Green/B12
Geoff Smith/B15

I need to get the Sales Consultants name into the Customer spreadsheet -
there are 8,500 lines so I need something automated rather than a manual task.

Can anyone help?

Many thanks - Elaine

 

[Guest]

A B C D

ABC Ltd/123 High Street/ London/ N15 ABC

Try this formula in cell E2
=INDEX(Sheet2!A:A,MATCH(LEFT(D2,SEARCH(" ",D2)-1),Sheet2!B:B,0))

Regards,
Stefi

 

[Guest]

in cell N2

=INDEX(Sheet2!B:B,MATCH(LEFT(H2,SEARCH(" ",H2)-1),Sheet2!G:G,0))

where sheet2 is your Consultants sheet's name, so replace it with the actual
name of this sheet, e.g. if the sheet name is "Consultants", then apply this:

=INDEX(Consultants!B:B,MATCH(LEFT(H2,SEARCH(" ",H2)-1),Consultants!G:G,0))


and of course drag down the formula as necessary!

Regards,
Stefi

„Elaine†ezt írta: