IF lookup

[vxs]

Hi All,
Pulling my hair out here googling vlookup, lookup, match index not sure which to use.

I wish to match the begining of the text in cell A1 against a list and it to return the relevant depot name in cell B1 for example.

Sometimes the begining of the text will be as short as 2 letters but sometimes due to similar depot names it will need to match it by a longer variable say by 6 characters

Example Data

Column A

MA00124645

MAN011344

LUT111356335

LUD111346467

<tbody>
</tbody>

Example Data To Match Against

MAN	MANCHESTER
MA	MAIDSTONE
LUD	LUDLOW
LUT	LUTON

<tbody>
</tbody>

So if Cell A1 = MA0011234555 it will know to return in B1 MAIDSTONE
and say
Cell A2 = MAN847626822 it will know to return in B2 MANCHESTER

As I said sometimes the strings in column A get quite long and the text needed at the begining of the string can be as shorts as 2 letters or 6 but these letters always begining at the start of the string of data so hopefully I can always do a lookup/match via this info if I can work out how to do the lookup.

Any help would be much appreciated please.

Thank you

 

[vxs]

Lovely, thanks for that Marcílio_Lobão that has worked.

Hopefully I don't come across any other issues as this formula seems to do the trick.

Thanks all for responding especially Marcílio_Lobão and FDibbins ray:

 

[FDibbins]

Happy to help

 

[Marcílio_Lobão]

vxs, Good afternoon.

Thanks for the feedback.

It was a pleasure to help you.

 

[Aladin Akyurek]

MA00124645	MAIDSTONE		LUD	LUDLOW
MAN011344	MANCHESTER		LUT	LUTON
LUT111356335	LUTON		MA	MAIDSTONE
LUD111346467	LUDLOW		MAN	MANCHESTER

<tbody>
</tbody>

Try to sort D:E on D ascending and invoke in B2...

=LOOKUP(9.99999999999999E+307,SEARCH($D$2:$D$5,$A2),$E$2:$E$5)

If "begins with" is relevant...

=LOOKUP(9.99999999999999E+307,SEARCH("|"&$D$2:$D$5,"|"$A2),$E$2:$E$5)