• Hi All

    Please note that at the Chandoo.org Forums there is Zero Tolerance to Spam

    Post Spam and you Will Be Deleted as a User

    Hui...

  • When starting a new post, to receive a quicker and more targeted answer, Please include a sample file in the initial post.

Vlookup a number in a string of number?

I

Itshewe

New Member
Dear chandoos,

I have issue trying to solve a problem with vlookup:

A B C
00000055500000000 145155521542 David

I want a formula that finds the unik number in column A = "555" in column B = 1451*555*21542 and return the value C = "David"

I have tried to use Vlookup but it doesnt help.. any suggestions?
 
AliGW said:
Is it a works computer?

Explain in more detail what you want.
Click to expand...

upload_2018-12-28_11-20-49.png

As you can see there is *5555* in column A and similarly in Column B *5555*
I tried to use vlookup to get the value C *David* but it didnt work,,

Find function works without problem..
 
Itshewe said:
No worries have a great day!
Click to expand...

I was expecting you to explain in more detail ...

As Vletm has said, just go with the alternative you have already found. VLOOKUP is not going to help you here, whatever it is you are trying to do - I'm still not quite sure what that is.
 
FIND is working because it does a search within a string.
Vlookup requires
  1. a value to lookup (search key)
  2. a "lookup"-table to find this value in (best, and required for approximate match, ordered ascending/descending on the key column)
  3. a number column to get the return value (any column to the right of the search key)
  4. TRUE/FALSE to either find approximate match or exact match
So if in your case "555" should be found in both columns A and B, you need to have a lookup table with "555" sitting in the first column and "David" somewhere in one of the next columns.
Obviously if you'd have more keys to find, the positions of those values within the provided strings are more important then the values to look for. Using find in all cases would be a rather cumbersome, if not impossible solution.

Assuming - because 1 example is simply not enough for us to understand your query - the strings are a logical construction you could use MID() to find your search key and then use vlookup to fetch "David". It is a long shot...
 

Attachments

  • find_vlookup.xlsx
    9.3 KB · Views: 8
GraH - Guido said:
FIND is working because it does a search within a string.
Vlookup requires
  1. a value to lookup (search key)
  2. a "lookup"-table to find this value in (best, and required for approximate match, ordered ascending/descending on the key column)
  3. a number column to get the return value (any column to the right of the search key)
  4. TRUE/FALSE to either find approximate match or exact match
So if in your case "555" should be found in both columns A and B, you need to have a lookup table with "555" sitting in the first column and "David" somewhere in one of the next columns.
Obviously if you'd have more keys to find, the positions of those values within the provided strings are more important then the values to look for. Using find in all cases would be a rather cumbersome, if not impossible solution.

Assuming - because 1 example is simply not enough for us to understand your query - the strings are a logical construction you could use MID() to find your search key and then use vlookup to fetch "David". It is a long shot...
Click to expand...
Thanks a lot! I just realized that I forgot an imperative detail:

The idea is that 555 is already available and through that search from the string the unik ID in this case 555 in column B. So i tried =VLOOKUP(A1;$B$1:$C$1;2;) but get an error..

upload_2018-12-28_14-53-16.png
 
Itshewe,
Some wise people told be to always read the help assistant of a function first. Allow me to repeat that golden advise. As did Vletm before in #8. Him, being one of the wise people...
With your new information I'm even more confused now on what you want to accomplish. Search for 555, value in A, in B and return "David" that already is in C? Please do upload a sample file from a pc that allows it.

pecoflyer, your solution does not seem to work. I got #N/As all over the place. (I'm also using EU settings, so it is not the ";")
As you can see in the screenshot of @Itshewe the string is a number value (alignment is right, not left). What you try only works with text strings.
 
Itshewe said:
Thanks a lot! I just realized that I forgot an imperative detail:

The idea is that 555 is already available and through that search from the string the unik ID in this case 555 in column B. So i tried =VLOOKUP(A1;$B$1:$C$1;2;) but get an error..

View attachment 57243
Click to expand...
Maybe,

1] As per post #.11, the OP's picture layout

In D1, enter :

=VLOOKUP(1,IF({1,0},0+ISNUMBER(FIND(A1,B1)),C1),2,0)

And,

2] As per GraH - Guido's setup table (post #.10) and with adding the OP's post #.1 example text value: 00000055500000000 145155521542 in A7 and B7

My formula solution, in C2, copied down :

=IFERROR(VLOOKUP(2,IF({1,0},MMULT(0+ISNUMBER(FIND(I$3:I$7,A2:B2)),{1;1}),J$3:J$7),2,0),"Not Matching")

Regard
Bosco
 
Last edited:
bosco_yip said:
Maybe,

1] As per post #.11, the OP's picture layout

In D1, enter :

=VLOOKUP(1,IF({1,0},0+ISNUMBER(FIND(A1,B1)),C1),2,0)

And,

2] As per GraH - Guido's setup table (post #.10) and with adding the OP's post #.1 example text value: 00000055500000000 145155521542 in A7 and B7

My formula solution, in C2, copied down :

=IFERROR(VLOOKUP(2,IF({1,0},MMULT(0+ISNUMBER(FIND(I$3:I$7,A2:B2)),{1;1}),J$3:J$7),2,0),"Not Matching")

Regard
Bosco
Click to expand...
Bosco you are a legend! Many thanks :)
 
You must log in or register to reply here.
Back
Top