IF lookup

vxs

Board Regular
Joined
Dec 10, 2008
Messages
61
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

MANMANCHESTER
MAMAIDSTONE
LUDLUDLOW
LUTLUTON

<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
 
Last edited:

Excel Facts

VLOOKUP to Left?
Use =VLOOKUP(A2,CHOOSE({1,2},$Z$1:$Z$99,$Y$1:$Y$99),2,False) to lookup Y values to left of Z values.
You would normally use something like this...
Your data...
A​
B​
12​
MANMANCHESTER
13​
MAMAIDSTONE
14​
LUDLUDLOW
15​
LUTLUTON

What you want...
A​
B​
2​
MA00124645MANCHESTER
3​
MAN011344MANCHESTER
4​
LUT111356335LUDLOW
5​
LUD111346467LUDLOW

B2=VLOOKUP(LEFT(A2,2)&"*",$A$12:$B$15,2,0)

However, because sometimes it looks like you want to match on the 1st 2 characters, and sometimes on 3?
If you are only using 2 if the 3rd char is a number, then maybe this...
=VLOOKUP(LEFT(A2,IF(ISNUMBER(--MID(A2,3,1)),2,3))&"*",$A$12:$B$15,2,0)
But this still fails when you have a MA? and a MAN
 
Upvote 0
Thanks for replying.

yes sometimes the string of data can be 2 letters then numbers and sometimes it can be up to 5 letters then numbers and unfortunately I don't have control over the string/data that gets sent to me.

So at the moment I have to manually type in the column next to it, the depot name. Was trying to find a way to automate it.

Thankfully there is some logic to it as MA will always be MAIDSTONE and MAN always MANCHESTER.

Your table above where you say "What you want..." is confusing me a little as it should = something like this:


A​
B​
2​
MA00124645MAIDSTONE
3​
MAN011344MANCHESTER
4​
LUT111356335LUTON
5​
LUD111346467LUDLOW


<tbody>
</tbody>



 
Upvote 0
Yes, I know that is what you want, but because of the order in which your base data is in, (Manchester before Maidstone), searching for either MA* or MAN will always bring up Manchester for both. If you could swap them around, then it should work - for THAT example
 
Upvote 0
vxs, Good evening.

I'm sure a more elegant formula will appear than mine.
But while that does not happen, make sure this formula meets your need.

I ask permission to use the same layout as the master FDibbins.
Dear FDibbins, greetings from Brazil.


A​
B​
12​
MANMANCHESTER
13​
MAMAIDSTONE
14​
LUDLUDLOW
15​
LUTLUTON

<tbody>
</tbody>



A​
B
2​
MA00124645Formula
3​
MAN011344
4​
LUT111356335
5​
LUD111346467

<tbody>
</tbody>

Try to use:

B2 -->
=VLOOKUP(LEFT(A2, MIN(FIND({0,1,2,3,4,5,6,7,8,9}, A2&"0123456789"))-1),$A$12:$B$15, 2, FALSE)

Copy it down.

Please, tell us if it worked for you.
I hope it helps.
 
Upvote 0
Hi,

Before applying vlookup we much separate char from alpha numeric string , try below mentioned code

=VLOOKUP(SUBSTITUTE( SUBSTITUTE( SUBSTITUTE( SUBSTITUTE(SUBSTITUTE( SUBSTITUTE( SUBSTITUTE( SUBSTITUTE( SUBSTITUTE(A2,"1",""),"2",""), "3",""),"4",""),"5",""), "6",""),"7",""),"8",""),"9",""),D2:D7,2,FALSE)
 
Upvote 0
FDibbins / Marcílio_Lobão

Thank you very much for your replies.

I have managed to use the formula posted by Marcílio_Lobão and edit it a little to fit my specific needs and where my data is. The only thing I really need now is to remove the #N/A when there is no data on D2 so the spreadsheet looks clean.

=VLOOKUP(LEFT(D2, MIN(FIND({0,1,2,3,4,5,6,7,8,9}, D2&"0123456789"))-1),'Depot Codes'!$A$1:$B$25, 2, FALSE)

if you could help with that, that would be great. Apologies for the delayed reply to your answer, different timezones and all :)

Thanks again
 
Upvote 0
vxs, Good evening.

Thanks for your feedback.

"
...The only thing I really need now is to remove the #N/A when there is no data on D2 so the spreadsheet looks clean...."

Try to use:

=
IF(D2="", "", VLOOKUP(LEFT(D2, MIN(FIND({0,1,2,3,4,5,6,7,8,9}, D2&"0123456789"))-1),'Depot Codes'!$A$1:$B$25, 2, FALSE))

I hope it helps.

 
Upvote 0

Forum statistics

Threads
1,213,546
Messages
6,114,251
Members
448,556
Latest member
peterhess2002

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
Back
Top