VLOOKUP for content, not value

[Traima]

Hi everyone,

I have a question about how to extend the use of my vlookup-formula.

I have a table with different values like this:

C1:4001T, SEBA
C2:T4001
C3:5820S
C4:ALBT, 5820, BTAL

I need to match the numbers (4001, 5820) with another table, but so far I
haven't found the solution. I've tried to format the values to lookop only
the four last numbers or the four first number. That will do for the three
top examples. But for my last example, I'm not able to match 5820 with my
other table.

Is it possible to adjust the vlookup-formula to search for content in a
cell, rather than the value of it? Or is there another formula to use in this
case.

Thanks

Traima

 

[Pete_UK]

What exactly are you trying to do? You can use wildcard characters
with VLOOKUP, so you could start like this:

=VLOOKUP("*"&E1&"*", table_range ... etc

where E1 could contain 4001.

However, VLOOKUP will only find the first match like that, whereas you
seem to want to find the other matches as well.

So, I repeat, what are you trying to do? Give us the bigger picture.

Hope this helps.

Pete

 

[Traima]

Hi Pete,

Thanks for your reply. I'll try to explain:

I have to tables.

My first table contains information of sales. Each row in my table can
contain more than one product sold (rarely more than three). The different
products are separated by the comma, but all in the same cell (in my previous
examples).

My problem is that not everyone type the product correct (the numbers 4001),
they sometime add a letter in front of or after the product code.

In my second table I have the price for each product. I need to match the
products sold with the price of the product.

So in this case I need to look up whichever product containing the numbers
4001 to find the correct pris for the product.

(I know that the salessystem is terrible and awful and makes me wanna quit
my job, but this is how it works..)

Did my explanation give you any further information?


Traima

 

[Don Guillett]

I think I would use a FIND or FINDNEXT macro to do this.

If desired, send your file to my address below along with this msg and
a clear explanation of what you want and before/after examples.

 

[Pete_UK]

Yes, your explanation did give me some more information, but a
solution to your problem is not really any clearer to me. Even if you
were to separate out the entries in C1:C4 to separate columns (which
you could do using Data | Text to Columns using comma as the
delimiter), you will still be stuck with the spurious entries - you
would have T4001 in one cell and 4001T in another cell from your
example. You could have a formula that extracted only the digits out
of this, but it is clear from your example that you have codes that
don't involve digits, so this would not be universally applicable.

Perhaps you could train people to only enter valid data, and to do so
in separate cells. If the data entry was in separate cells, then you
could apply a data validation drop-down to each cell linked to your
sales codes, in order to ensure that the data entry was valid. This
would make your pricing task much easier.

Hope this helps.

Pete

 

[Traima]

Hi Pete,

It would actually help me a lot if I were able to use a formula to extract
only the digits out of the cell. Would you provide me with this formula,
please? How will I be able to extraxt "5820" from the cell containig "ALBT,
5820T, BTAL"

And yes, I've already e-mailed everyone with the standard routine on how to
enter data into the system..

 

[Traima]

Hi Don,

Thanks for your offer, I'll have another try with a the formula..


Traima

 

[Max]

One way is via this UDF by Dana Delouis

Function GetValue(s As String) As Double
Dim n As Long
For n = 1 To Len(s)
GetValue = WorksheetFunction.Max(GetValue, Val(Mid$(s, n)))
Next
End Function

To install the UDF above, hit Alt+F11 to get to VBE, click Insert>Module,
then copy n paste the above directly into the code window (whitespace on the
right). Hit Alt+Q to get back to Excel.

In Excel, apply it like this, in B1: =getvalue(A1)
where A1 contains the textstring, eg: "ALBT, 5820T, BTAL"

Any good? hit the YES below
--
Max
Singapore
http://savefile.com/projects/236895
Downloads:27,000 Files:200 Subscribers:70
xdemechanik

 

[Pete_UK]

This will extract a 4 digit string from C1:

=MID(C1,MIN(FIND({0,1,2,3,4,5,6,7,8,9},C1&"0123456789")),4)

So, if you put this in D1 and copy it down, it will give you the
following from your example:


Col C Col D
4001T, SEBA 4001
T4001 4001
5820S 5820
ALBT, 5820, BTAL 5820

Hope this helps.

Pete

 

[Traima]

Fantastic! It works

Thanks a lot for your help, Pete!


Traima

 

[confused!!]

I entered this VBA it returned a value of 0.

My next question is how is this function reading and pulling worksheet 2 data?

 

[Pete_UK]

You're welcome, Traima - thanks for feeding back.

Pete