| 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 |
| 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 | |||
Hybrid Mode
