A (seems) simple "vlookup" complicating my life

albita09

New member
Joined
Jan 30, 2014
Messages
13
Reaction score
0
Points
0
Good afternoon everybody!

I am trying to analyse the data of a spread sheet into another and for that I ned to Vlookup cells and paste the values in the columns 3,4,5.
Seems eacy for now.

The problem is that I am looking for a strim of characters that need to be extracted frkm the place where I am rearching.

That is

I have =VLOOKUP(B4...) I have to find the value in B4 in the column E in another tab. However column E will not find the value it self but only when you split the character in that column E.

That is

=VLOOKUP(B4;....) where B4 is 00201

I am looking for that value in a column that has the following strings:
0040200000
0060300000
0020113411
0070500000
0340210540
0360311035


So, you must think, "You will never find it" sure. But I need to find it in the first 5 characters of those strings. See red

0040200000
0060300000
0020113411
0070500000
0340210540
0360311035

When it find it I just want the Vlookup to place a 1 in it, and not any value in the following or same columns.

You must also know that the value in B4 is actually the joint of: B4&D3, but that expression works inside the vlookup.

This is the furthest I have gotten but I cannot make it work:

=VLOOKUP(B4&$D$3;mid('SMS-Based Data Analysis'!$K$4:$K$142;1;5);1;FALSE)

Thank you very much in advanced!

PS: I have looked of rsimilar posts in the forum but I did not find the asnwers therefore I am openng a new threat :)
 
Try:

=VLOOKUP(B4&"*";'SMS-Based Data Analysis'!$K$4:$K$142;1;FALSE)

the * is a wildcard character that can be used in VLOOKUP
 
Dear NBVC,

Thank you very much for your answer.

I have tried what you said, but as I was afrais it is not quite what I need.

Actually the part you see in red now:

=VLOOKUP(B4&$D$3;mid('SMS-Based Data Analysis'!$K$4:$K$142;1;5);1;FALSE)

Works perfectly. It joins to cells creating a string such as "00201". And, it looks for it.

The problem come in the bunch/range of data where the search is happening

That range looks like this:

0040200000
0060300000
0020113411
0070500000
0340210540
0360311035


Therefore I want the vlookup to search only in the three first characters of each string. Therefore I tried the mid you see now in green:

=VLOOKUP(B4&$D$3;mid('SMS-Based Data Analysis'!$K$4:$K$142;1;5);1;FALSE)

But it does not work. I have also tried to put the mid in another position:

=VLOOKUP(B4&$D$3;'SMS-Based Data Analysis'!mid($K$4:$K$142;1;5);1;FALSE)

But it does not work either.

I hope I made myself clear now. Thank you so much for taking the time to asnwer and looking into my problem.

At least, I ahve learnt that this * is a wildcard in Excel ;)

A.
 
Are you sure that this doesn't work?

=VLOOKUP(B4&$D$3&"*";'SMS-Based Data Analysis'!$K$4:$K$142;1;FALSE)

This will search only the first 5 digits in 'SMS-Based Data Analysis'!$K$4:$K$142 so you don't need to use the mid function (which, by the way doesn't work in VLOOKUP as you tried).
 
YES!!!!
It does.
You rock NBVC!
THANK YOU LOADS!!
PS: How do I rate this post as GREAT and put it as "Solved" ???
 
Back
Top