Results 1 to 5 of 5

Thread: A (seems) simple "vlookup" complicating my life

  1. #1

    A (seems) simple "vlookup" complicating my life



    Register for a FREE account, and/
    or Log in to avoid these ads!

    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

  2. #2
    Super Moderator NBVC's Avatar
    Join Date
    May 2011
    Location
    Mississauga, Canada
    Posts
    1,489
    Articles
    0
    Excel Version
    Excel 2016
    Try:

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

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


  3. #3
    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.

  4. #4
    Super Moderator NBVC's Avatar
    Join Date
    May 2011
    Location
    Mississauga, Canada
    Posts
    1,489
    Articles
    0
    Excel Version
    Excel 2016
    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).


  5. #5
    YES!!!!
    It does.
    You rock NBVC!
    THANK YOU LOADS!!
    PS: How do I rate this post as GREAT and put it as "Solved" ???

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •