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
--
Elaine

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


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
--
Elaine

Hi Stefi

It doesn't seem to work, although my cells are different -

In the customer sheet the postcode is in column H and I need to put the
sales consultants name in column N.

On the consultants sheet the postcode is in column G and the sales
consultants name is in column B

Many thanks - Elaine
--
Elaine


"Stefi" wrote:


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


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
--
Elaine

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:

Hi Stefi

It doesn't seem to work, although my cells are different -

In the customer sheet the postcode is in column H and I need to put the
sales consultants name in column N.

On the consultants sheet the postcode is in column G and the sales
consultants name is in column B

Many thanks - Elaine
--
Elaine


"Stefi" wrote:


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


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
--
Elaine