Vlookup Question..if text lookup_value do not match with table_arr

L

ldiaz

I have thousand of rows as follow:
Sheet1
Product STD-Code?
C-G-COL-YK
C-G-COL-YG
C-G-CRV-BICEP-YK
C-G-CRV-BICEP-PK
C-G-CRV-BICEP-YG
C-G-CRV-THROAT-YK
C-G-CRV-THROAT-YE
C-G-CRV-THROAT-EB
C-G-PGRN-WK
C-G-PGRN-WY
C-G-PGRN-WA
C-G-THIGH-LG-PK

and I have another list where it only shows one of each of family and I
replaced the digits that may change to ("xx"), where ("xx") means that the
value may change , example could be YK, YG, YB, PK, EB, LG, etc
Many Rows: C-G-COL-YG
Concentrated: C-G-COL-xx



Sheet2
Product STD-Code
C-G-COL-xx |Accy-90
C-G-CRV-BICEP-xx |Accy-90
C-G-CRV-THROAT-xx |Accy-40
C-G-PGRN-xx |Accy-40
C-G-THIGH-xx-xx |Accy-40

what I want ..is to put a vlookup that take the lookup_value for
example:C-G-COL-YK (located on sheet1) and take as table_array column Product
and STD-Code (Located in Sheet2) and the results that I want is "Accy-90" and
so on.


Could you please help me on this....
 
P

Pete_UK

Try something like this in B2 of Sheet1:

=VLOOKUP(LEFT(A2,LEN(A2)-2)&"xx",Sheet2!A:B,2,0)

and copy down as required.

Hope this helps.

Pete
 
L

ldiaz

this code works fine if the lookup_value has no more digits after xx

but on lookup_values as these..

C-G-COL-YG-5349
C-G-COL-YK-5349

I need it to find this value on table array.
C-G-COL-xx-5349


please help on this...
Thanks in advanced.
--
Lorenzo Díaz
Cad Technician


Pete_UK said:
Try something like this in B2 of Sheet1:

=VLOOKUP(LEFT(A2,LEN(A2)-2)&"xx",Sheet2!A:B,2,0)

and copy down as required.

Hope this helps.

Pete
Click to expand...
 
P

Pete_UK

I had assumed, after a quick look at your table 2, that you would
always have xx at the end of your codes - I now see that your fifth
entry in that table has xx-xx at then end, so the formula would not
have worked completely anyway. You can use a wildcard with VLOOKUP,
but your latest post implies that two xx characters can appear in the
middle of the code (rather than at the end), so is there any way of
determining where these characters might occur?

Pete
 
L

ldiaz

most common is like follow:

last 4 digits mean a project number, and the text that may change is 2
digits before that.
P-G-SHLD-LIM-LG-YK-5330
P-G-YOKE-YK-5390
P-TACTBAG-WG-5157
S-G-THROAT-WG-5200

example :
P-G-SHLD-LIM-LG-xx-5330
P-G-YOKE-xx-5390
P-TACTBAG-xx-5157
S-G-THROAT-xx-5200

remember that I need to mantain the condition to look up valus as this.
P-TACTBAG-YK

on a table array to find this:
P-TACTBAG-xx

Thanks
LD
 
P

Pete_UK

So, will it always be xx (and the xx-xx was just a typo), or might
there be more than just 2 x's?

Pete
 
L

ldiaz

wa a typo, always it will be.

P-G-SHLD-LIM-LG-xx-5330
or
P-G-SHLD-LIM-LG-xx


Thanks
LD
--
Lorenzo Díaz
Cad Technician


Pete_UK said:
So, will it always be xx (and the xx-xx was just a typo), or might
there be more than just 2 x's?

Pete
Click to expand...
 
P

Pete_UK

Okay. I'm a bit short of time at the moment (small matter of a
football match starting in a few minutes !!), but I'll get back to
you. The basic approach will be:

=IF(ISNA(vlookup1),IF(ISNA(vlookup2),"not found",vlookup2),vlookup1)

where vlookup1 will be similar to what I suggested before, and
vlookup2 will assume 4 digits at the end of the lookup value.

Pete
 
L

ldiaz

I didn't understand,

could be possible when you have time to write an example please?

Thanks and win the game.
LD
 
P

Pete_UK

Yes, I said I'd get back to you. Spain beat Germany 1-0, so I wasn't
really rooting for either side.

Try something like this in B2:

=IF(ISNA(VLOOKUP(LEFT(A2,LEN(A2)-2)&"xx",Sheet2!A:B,2,0)),
IF(ISNA(VLOOKUP(LEFT(A2,LEN(A2)-7)&"xx-"&RIGHT(A2,4),Sheet2!A:B,
2,0)),"not present",
VLOOKUP(LEFT(A2,LEN(A2)-7)&"xx-"&RIGHT(A2,4),Sheet2!A:B,2,0)),
VLOOKUP(LEFT(A2,LEN(A2)-2)&"xx",Sheet2!A:B,2,0))

This is all one formula - be wary of spurious line-breaks when it
appears in your newsgroup Reader.

Hope this helps.

Pete
 
L

ldiaz

Hi Pete.
I got your formula, and it works great!.
Thank you so much for your help on this issue.

Have a nice day.
 
Top