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

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

On Sep 9, 11:52*am, Traima wrote:
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

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

"Pete_UK" wrote:

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

On Sep 9, 11:52 am, Traima wrote:
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

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.

--
Don Guillett
Microsoft MVP Excel
SalesAid Software

"Traima" wrote in message
...
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

"Pete_UK" wrote:

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

On Sep 9, 11:52 am, Traima wrote:
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

Hi Don,

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


Traima

"Don Guillett" wrote:

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.

--
Don Guillett
Microsoft MVP Excel
SalesAid Software

"Traima" wrote in message
...
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

"Pete_UK" wrote:

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

On Sep 9, 11:52 am, Traima wrote:
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

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

On Sep 9, 1:27*pm, Traima wrote:
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



"Pete_UK" wrote:
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

On Sep 9, 11:52 am, Traima wrote:
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- Hide quoted text -

- Show quoted text -

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

"Pete_UK" wrote:

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

On Sep 9, 1:27 pm, Traima wrote:
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



"Pete_UK" wrote:
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

On Sep 9, 11:52 am, Traima wrote:
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- Hide quoted text -

- Show quoted text -

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 InsertModule,
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
---
"Traima" wrote:
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"

This will extract a 4 digit string from C1:

=MID(C1,MIN(FIND({0,1,2,3,4,5,6,7,8,9},C1&"0123456 789")),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


On Sep 9, 2:25*pm, Traima wrote:
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..



"Pete_UK" wrote:
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

On Sep 9, 1:27 pm, Traima wrote:
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

"Pete_UK" wrote:
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

On Sep 9, 11:52 am, Traima wrote:
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- Hide quoted text -

- Show quoted text -- Hide quoted text -

- Show quoted text -