| Home |
| Search |
| Today's Posts |
|
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
Vlookup Question..if text lookup_value do not match with table_arr
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.... -- Lorenzo DÃ*az Cad Technician |
|
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
Vlookup Question..if text lookup_value do not match withtable_arr
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 On Jun 27, 7:53*pm, ldiaz wrote: 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.... -- Lorenzo Díaz Cad Technician |
|
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
Vlookup Question..if text lookup_value do not match with table
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" wrote: 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 On Jun 27, 7:53 pm, ldiaz wrote: 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.... -- Lorenzo DÃ*az Cad Technician |
|
#4
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
Vlookup Question..if text lookup_value do not match with table
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 On Jun 29, 5:14*pm, ldiaz wrote: 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" wrote: 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 On Jun 27, 7:53 pm, ldiaz wrote: 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.... -- Lorenzo Díaz Cad Technician- Hide quoted text - - Show quoted text - |
|
#5
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
Vlookup Question..if text lookup_value do not match with table
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 -- Lorenzo DÃ*az Cad Technician "Pete_UK" wrote: 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 On Jun 29, 5:14 pm, ldiaz wrote: 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" wrote: 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 On Jun 27, 7:53 pm, ldiaz wrote: 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.... -- Lorenzo DÃ*az Cad Technician- Hide quoted text - - Show quoted text - |
|
#6
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
Vlookup Question..if text lookup_value do not match with table
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 On Jun 29, 6:19*pm, ldiaz wrote: 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 -- Lorenzo Díaz Cad Technician |
|
#7
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
Vlookup Question..if text lookup_value do not match with table
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" wrote: 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 On Jun 29, 6:19 pm, ldiaz wrote: 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 -- Lorenzo DÃ*az Cad Technician |
|
#8
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
Vlookup Question..if text lookup_value do not match with table
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 On Jun 29, 6:56*pm, ldiaz wrote: 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" wrote: 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 On Jun 29, 6:19 pm, ldiaz wrote: 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 -- Lorenzo Díaz Cad Technician- Hide quoted text - - Show quoted text - |
|
#9
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
Vlookup Question..if text lookup_value do not match with table
I didn't understand,
could be possible when you have time to write an example please? Thanks and win the game. LD -- Lorenzo DÃ*az Cad Technician "Pete_UK" wrote: Community Message Not Available |
|
#10
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
Vlookup Question..if text lookup_value do not match with table
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 On Jun 29, 7:54*pm, ldiaz wrote: I didn't understand, could be possible when you have time to write an example please? Thanks and win the game. LD -- Lorenzo Díaz Cad Technician "Pete_UK" wrote: Community Message Not Available- Hide quoted text - - Show quoted text - |
|
#11
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
Vlookup Question..if text lookup_value do not match with table
Hi Pete.
I got your formula, and it works great!. Thank you so much for your help on this issue. Have a nice day. -- Lorenzo DÃ*az Cad Technician "Pete_UK" wrote: 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 On Jun 29, 7:54 pm, ldiaz wrote: I didn't understand, could be possible when you have time to write an example please? Thanks and win the game. LD -- Lorenzo DÃ*az Cad Technician "Pete_UK" wrote: Community Message Not Available- Hide quoted text - - Show quoted text - |
|
#12
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
Vlookup Question..if text lookup_value do not match with table
I'm glad to hear that, Lorenzo - thanks for feeding back.
Pete On Jun 30, 5:15*pm, ldiaz wrote: Hi Pete. I got your formula, and it works great!. Thank you so much for your help on this issue. Have a nice day. -- Lorenzo Díaz Cad Technician "Pete_UK" wrote: 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. |
| Reply |
| Thread Tools | Search this Thread |
| Display Modes | |
|
|
Similar Threads
|
||||
| Thread | Forum | |||
| match largest value that is exactly equal to lookup_value | Excel Worksheet Functions | |||
| vlookup lookup_value | Excel Worksheet Functions | |||
| Vlookup(lookup_value) | Excel Worksheet Functions | |||
| Can Index/Match pull lookup_value from a combo box? | Excel Discussion (Misc queries) | |||
| vlookup:same lookup_value, different returns | Excel Worksheet Functions | |||
Linear Mode
