| Home |
| Search |
| Today's Posts |
|
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
'RIGHT', 'LEFT' in a VLOOKUP
I have a table of five digit SKU numbers that I need to cross-reference with
a list of UPC numbers and sales data. The last five digits of the UPC number are the SKU number. So my VLOOKUP is basically VLOOKUP (SKU, 'Sheet with UPC information'! UPC column:Price column, 2, false) To make that work, I need to actually add a column BEFORE the UPC column that's really RIGHT (UPC Column, 5). But I have lots of files, and don't want to save changes to them (they're not mine). Is there a way to do the VLOOKUP in a way that's essentially, VLOOKUP (SKU, 'Sheet with UPC information'! RIGHT (UPC Column,5):Price column, 2, false)? |
|
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
'RIGHT', 'LEFT' in a VLOOKUP
If your SKUs are unique (i.e. the last 5 digits of the UPC are also
unique), then you might be able to do it using wildcard characters like this: VLOOKUP ("*"&SKU, 'Sheet with UPC information'! UPC Column:Price column, 2, false) It depends on whether your UPCs are proper numbers or text values. Hope this helps. Pete On Mar 13, 5:00*pm, RJB wrote: I have a table of five digit SKU numbers that I need to cross-reference with a list of UPC numbers and sales data. The last five digits of the UPC number are the SKU number. So my VLOOKUP is basically VLOOKUP (SKU, 'Sheet with UPC information'! UPC column:Price column, 2, false) To make that work, I need to actually add a column BEFORE the UPC column that's really RIGHT (UPC Column, 5). But I have lots of files, and don't want to save changes to them (they're not mine). Is there a way to do the VLOOKUP in a way that's essentially, VLOOKUP (SKU, 'Sheet with UPC information'! RIGHT (UPC Column,5):Price column, 2, false)? |
|
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
'RIGHT', 'LEFT' in a VLOOKUP
It's possible if you use the functions MATCH and INDEX togheter in an array
formulas (CRTL+SHIF+ENTER) as follow, =INDEX(Return_Column,CORRESP(SKU,RIGHT(UPC_Column, 5),0)) -- Adilson Soledade "RJB" wrote: I have a table of five digit SKU numbers that I need to cross-reference with a list of UPC numbers and sales data. The last five digits of the UPC number are the SKU number. So my VLOOKUP is basically VLOOKUP (SKU, 'Sheet with UPC information'! UPC column:Price column, 2, false) To make that work, I need to actually add a column BEFORE the UPC column that's really RIGHT (UPC Column, 5). But I have lots of files, and don't want to save changes to them (they're not mine). Is there a way to do the VLOOKUP in a way that's essentially, VLOOKUP (SKU, 'Sheet with UPC information'! RIGHT (UPC Column,5):Price column, 2, false)? |
|
#4
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
'RIGHT', 'LEFT' in a VLOOKUP
Do I sub "MATCH" for "CORRESP"?
"Adilson Soledade" wrote: It's possible if you use the functions MATCH and INDEX togheter in an array formulas (CRTL+SHIF+ENTER) as follow, =INDEX(Return_Column,CORRESP(SKU,RIGHT(UPC_Column, 5),0)) -- Adilson Soledade "RJB" wrote: I have a table of five digit SKU numbers that I need to cross-reference with a list of UPC numbers and sales data. The last five digits of the UPC number are the SKU number. So my VLOOKUP is basically VLOOKUP (SKU, 'Sheet with UPC information'! UPC column:Price column, 2, false) To make that work, I need to actually add a column BEFORE the UPC column that's really RIGHT (UPC Column, 5). But I have lots of files, and don't want to save changes to them (they're not mine). Is there a way to do the VLOOKUP in a way that's essentially, VLOOKUP (SKU, 'Sheet with UPC information'! RIGHT (UPC Column,5):Price column, 2, false)? |
|
#5
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
'RIGHT', 'LEFT' in a VLOOKUP
Yes, CORRESP is the Portuguese equivalent to MATCH.
Pete On Mar 13, 5:42*pm, RJB wrote: Do I sub "MATCH" for "CORRESP"? "Adilson Soledade" wrote: It's possible if you use the functions MATCH and INDEX togheter in an array formulas (CRTL+SHIF+ENTER) as follow, =INDEX(Return_Column,CORRESP(SKU,RIGHT(UPC_Column, 5),0)) -- Adilson Soledade "RJB" wrote: I have a table of five digit SKU numbers that I need to cross-reference with a list of UPC numbers and sales data. The last five digits of the UPC number are the SKU number. So my VLOOKUP is basically VLOOKUP (SKU, 'Sheet with UPC information'! UPC column:Price column, 2, false) To make that work, I need to actually add a column BEFORE the UPC column that's really RIGHT (UPC Column, 5). But I have lots of files, and don't want to save changes to them (they're not mine). Is there a way to do the VLOOKUP in a way that's essentially, VLOOKUP (SKU, 'Sheet with UPC information'! RIGHT (UPC Column,5):Price column, 2, false)?- Hide quoted text - - Show quoted text - |
|
#6
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
'RIGHT', 'LEFT' in a VLOOKUP
Sorry for the mistake.
MATCH is the correcto form. Different languages , different functions. -- Adilson Soledade "Pete_UK" wrote: Yes, CORRESP is the Portuguese equivalent to MATCH. Pete On Mar 13, 5:42 pm, RJB wrote: Do I sub "MATCH" for "CORRESP"? "Adilson Soledade" wrote: It's possible if you use the functions MATCH and INDEX togheter in an array formulas (CRTL+SHIF+ENTER) as follow, =INDEX(Return_Column,CORRESP(SKU,RIGHT(UPC_Column, 5),0)) -- Adilson Soledade "RJB" wrote: I have a table of five digit SKU numbers that I need to cross-reference with a list of UPC numbers and sales data. The last five digits of the UPC number are the SKU number. So my VLOOKUP is basically VLOOKUP (SKU, 'Sheet with UPC information'! UPC column:Price column, 2, false) To make that work, I need to actually add a column BEFORE the UPC column that's really RIGHT (UPC Column, 5). But I have lots of files, and don't want to save changes to them (they're not mine). Is there a way to do the VLOOKUP in a way that's essentially, VLOOKUP (SKU, 'Sheet with UPC information'! RIGHT (UPC Column,5):Price column, 2, false)?- Hide quoted text - - Show quoted text - |
|
#7
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
'RIGHT', 'LEFT' in a VLOOKUP
One more quesntion.
If you'll have problems, probably you'll need to change the key search from SKU to TEXT(SKU,"00000"). Sometimes the VLOOKUP doesn't recognize numbers as search key. -- Adilson Soledade "Pete_UK" wrote: If your SKUs are unique (i.e. the last 5 digits of the UPC are also unique), then you might be able to do it using wildcard characters like this: VLOOKUP ("*"&SKU, 'Sheet with UPC information'! UPC Column:Price column, 2, false) It depends on whether your UPCs are proper numbers or text values. Hope this helps. Pete On Mar 13, 5:00 pm, RJB wrote: I have a table of five digit SKU numbers that I need to cross-reference with a list of UPC numbers and sales data. The last five digits of the UPC number are the SKU number. So my VLOOKUP is basically VLOOKUP (SKU, 'Sheet with UPC information'! UPC column:Price column, 2, false) To make that work, I need to actually add a column BEFORE the UPC column that's really RIGHT (UPC Column, 5). But I have lots of files, and don't want to save changes to them (they're not mine). Is there a way to do the VLOOKUP in a way that's essentially, VLOOKUP (SKU, 'Sheet with UPC information'! RIGHT (UPC Column,5):Price column, 2, false)? |
| Reply |
| Thread Tools | Search this Thread |
| Display Modes | |
|
|
Similar Threads
|
||||
| Thread | Forum | |||
| vlookup left | Excel Worksheet Functions | |||
| VLOOKUP LEFT VS RIGHT | Excel Worksheet Functions | |||
| Vlookup from left to right. | Excel Worksheet Functions | |||
| Vlookup to the left | Excel Worksheet Functions | |||
| VLOOKUP *Left* Value | Excel Discussion (Misc queries) | |||
Linear Mode
