Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Using wildcard * in MATCH
According to Microsoft Help, if the match type is 0 and the lookup value is
text, the lookup value can contain the wildcard (*) or (?). I have a product line plan that has a tab for the product styles and a tab for the SKU's associated with the styles. There will be one record per style on the style tab, but multiple records on the SKU tab. I added a "key" field to the style tab for use in various formulas and data validation. I am creating a product spec sheet by style which will become a controlled document once the line plan is adopted and all SKU's are finalized that uses the key field for lookups on the two tabs. The 1st thing it does is lookup the style based on user input of the key. The second is to find the 1st SKU associated with that style on the SKU tab using the result of the lookup. Due to the set up of the SKU tab, I use INDEX and MATCH because the style name is to the right of the SKU. This works for most of our style names. Where it breaks down is if there is a size associated with a style. Again, the style tab will only have one listing regardless of size, but the SKU tab will have different style names based on the size. (e.g., style tab shows Prod1, Prod2, but SKU tab shows Prod1 Small, Prod1 Large, Prod2 Small, Prod2 Large) There's no way around this. My problem: when using the INDEX and MATCH on the spec sheet, the SKU for those with size designation is not found because of the added text for the size in the SKU tab. Using text functions (LEFT, etc.) doesn't work when looking in a range of cells. There is a "T( )" function that converts a cell's contents to text. I tried adding that into the MATCH formula and adding a wildcard, but even that doesn't work. Is there a way to have the MATCH look only for "Prod1" and not "Prod1 Small" in the style column on the SKU sheet? Any suggestions greatly appreciated. Bernie |
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Using wildcard * in MATCH
Is there a way to have the MATCH look only for "Prod1"
and not "Prod1 Small" in the style column on the SKU sheet? Something like this should achieve the "fuzzy" matching In A2 down are your lookup values, eg: Prod1, etc while in Sheet1's col A, you have: Prod1 Small, etc Assume you want to return what's in Sheet1's col C Put in B2, normal ENTER to confirm: =INDEX(Sheet1!$C$2:$C$100,MATCH(TRUE,INDEX(ISNUMBE R(SEARCH(A2,Sheet1!$A$2:$A$100)),),0)) Copy down. Success? hit the YES below -- Max Singapore http://savefile.com/projects/236895 Downloads:27,000 Files:200 Subscribers:70 xdemechanik --- |
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Using wildcard * in MATCH
Max, my apologies. I see the error of how I worded this question. A2 on
Sheet1 (style tab) will have "Prod1". A2 on Sheet2 (SKU tab) will have "SKU1" and B2 will have "Prod1 Small". A3 on Sheet2 will have "SKU2" and B3 will have "Prod1 Large". And so on. On my product spec sheet, the user will input a "product key" (e.g., PK1 or PK2). This will populate a cell named "LookupV". I need INDEX to look in columns A & B on Sheet2, MATCH to find the cell in column B that says "Prod1 Small", and INDEX to return "SKU1" from column A when "LookupV" = "Prod1". (For this formula, I only need the 1st SKU associated with "Prod1".) I hope that's a little clearer. I'm so close to it I'm not sure I can explain it better without divulging proprietary information! "Max" wrote: Something like this should achieve the "fuzzy" matching In A2 down are your lookup values, eg: Prod1, etc while in Sheet1's col A, you have: Prod1 Small, etc Assume you want to return what's in Sheet1's col C Put in B2, normal ENTER to confirm: =INDEX(Sheet1!$C$2:$C$100,MATCH(TRUE,INDEX(ISNUMBE R(SEARCH(A2,Sheet1!$A$2:$A$100)),),0)) Copy down. Success? hit the YES below -- Max Singapore http://savefile.com/projects/236895 Downloads:27,000 Files:200 Subscribers:70 xdemechanik --- |
#4
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Using wildcard * in MATCH
(I'm not sure I understand, but....)
So it doesn't matter if Prod1 matches "prod1 small" or "prod1 medium" or "prod1 Large". You can bring back the data associated with the first match? If yes, then you could use: =index(sku!a:a,match("prod1"&"*",sku!b:b,0)) or =index(sku!a:a,match(a1&"*",sku!b:b,0)) or (depending on the layout) =vlookup("prod1"&"*",sku!a:b,2,false) =vlookup(a1&"*",sku!a:b,2,false) I'd add a check to make sure that the cell isn't empty: =if(a1="","",vlookup(a1&"*",...) Appending the wildcard to an empty cell could cause a match you don't want. berniean wrote: According to Microsoft Help, if the match type is 0 and the lookup value is text, the lookup value can contain the wildcard (*) or (?). I have a product line plan that has a tab for the product styles and a tab for the SKU's associated with the styles. There will be one record per style on the style tab, but multiple records on the SKU tab. I added a "key" field to the style tab for use in various formulas and data validation. I am creating a product spec sheet by style which will become a controlled document once the line plan is adopted and all SKU's are finalized that uses the key field for lookups on the two tabs. The 1st thing it does is lookup the style based on user input of the key. The second is to find the 1st SKU associated with that style on the SKU tab using the result of the lookup. Due to the set up of the SKU tab, I use INDEX and MATCH because the style name is to the right of the SKU. This works for most of our style names. Where it breaks down is if there is a size associated with a style. Again, the style tab will only have one listing regardless of size, but the SKU tab will have different style names based on the size. (e.g., style tab shows Prod1, Prod2, but SKU tab shows Prod1 Small, Prod1 Large, Prod2 Small, Prod2 Large) There's no way around this. My problem: when using the INDEX and MATCH on the spec sheet, the SKU for those with size designation is not found because of the added text for the size in the SKU tab. Using text functions (LEFT, etc.) doesn't work when looking in a range of cells. There is a "T( )" function that converts a cell's contents to text. I tried adding that into the MATCH formula and adding a wildcard, but even that doesn't work. Is there a way to have the MATCH look only for "Prod1" and not "Prod1 Small" in the style column on the SKU sheet? Any suggestions greatly appreciated. Bernie -- Dave Peterson |
#5
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Using wildcard * in MATCH
Actually, A2 on Sheet1 will have the product key, "PK1", and B2 will have
"Prod1". "berniean" wrote: Max, my apologies. I see the error of how I worded this question. A2 on Sheet1 (style tab) will have "Prod1". A2 on Sheet2 (SKU tab) will have "SKU1" and B2 will have "Prod1 Small". A3 on Sheet2 will have "SKU2" and B3 will have "Prod1 Large". And so on. On my product spec sheet, the user will input a "product key" (e.g., PK1 or PK2). This will populate a cell named "LookupV". I need INDEX to look in columns A & B on Sheet2, MATCH to find the cell in column B that says "Prod1 Small", and INDEX to return "SKU1" from column A when "LookupV" = "Prod1". (For this formula, I only need the 1st SKU associated with "Prod1".) I hope that's a little clearer. I'm so close to it I'm not sure I can explain it better without divulging proprietary information! "Max" wrote: Something like this should achieve the "fuzzy" matching In A2 down are your lookup values, eg: Prod1, etc while in Sheet1's col A, you have: Prod1 Small, etc Assume you want to return what's in Sheet1's col C Put in B2, normal ENTER to confirm: =INDEX(Sheet1!$C$2:$C$100,MATCH(TRUE,INDEX(ISNUMBE R(SEARCH(A2,Sheet1!$A$2:$A$100)),),0)) Copy down. Success? hit the YES below -- Max Singapore http://savefile.com/projects/236895 Downloads:27,000 Files:200 Subscribers:70 xdemechanik --- |
#6
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Using wildcard * in MATCH
I appreciate both your efforts, Dave and Max. Product management has now
altered the line plan and this exercise has become academic. I no longer have this problem. I'm sorry to have wasted your time. "Dave Peterson" wrote: (I'm not sure I understand, but....) So it doesn't matter if Prod1 matches "prod1 small" or "prod1 medium" or "prod1 Large". You can bring back the data associated with the first match? If yes, then you could use: =index(sku!a:a,match("prod1"&"*",sku!b:b,0)) or =index(sku!a:a,match(a1&"*",sku!b:b,0)) or (depending on the layout) =vlookup("prod1"&"*",sku!a:b,2,false) =vlookup(a1&"*",sku!a:b,2,false) I'd add a check to make sure that the cell isn't empty: =if(a1="","",vlookup(a1&"*",...) Appending the wildcard to an empty cell could cause a match you don't want. berniean wrote: According to Microsoft Help, if the match type is 0 and the lookup value is text, the lookup value can contain the wildcard (*) or (?). I have a product line plan that has a tab for the product styles and a tab for the SKU's associated with the styles. There will be one record per style on the style tab, but multiple records on the SKU tab. I added a "key" field to the style tab for use in various formulas and data validation. I am creating a product spec sheet by style which will become a controlled document once the line plan is adopted and all SKU's are finalized that uses the key field for lookups on the two tabs. The 1st thing it does is lookup the style based on user input of the key. The second is to find the 1st SKU associated with that style on the SKU tab using the result of the lookup. Due to the set up of the SKU tab, I use INDEX and MATCH because the style name is to the right of the SKU. This works for most of our style names. Where it breaks down is if there is a size associated with a style. Again, the style tab will only have one listing regardless of size, but the SKU tab will have different style names based on the size. (e.g., style tab shows Prod1, Prod2, but SKU tab shows Prod1 Small, Prod1 Large, Prod2 Small, Prod2 Large) There's no way around this. My problem: when using the INDEX and MATCH on the spec sheet, the SKU for those with size designation is not found because of the added text for the size in the SKU tab. Using text functions (LEFT, etc.) doesn't work when looking in a range of cells. There is a "T( )" function that converts a cell's contents to text. I tried adding that into the MATCH formula and adding a wildcard, but even that doesn't work. Is there a way to have the MATCH look only for "Prod1" and not "Prod1 Small" in the style column on the SKU sheet? Any suggestions greatly appreciated. Bernie -- Dave Peterson . |
#7
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Using wildcard * in MATCH
No prob. No response is ever wasted.
It'll come in handy someday, for someone somewhere. -- Max Singapore http://savefile.com/projects/236895 Downloads:27,000 Files:200 Subscribers:70 xdemechanik --- "berniean" wrote in message ... I appreciate both your efforts, Dave and Max. Product management has now altered the line plan and this exercise has become academic. I no longer have this problem. I'm sorry to have wasted your time. |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Using the wildcard with IF | New Users to Excel | |||
If and wildcard | Excel Discussion (Misc queries) | |||
sum if wildcard | New Users to Excel | |||
How to use AdvancedFilter wildcard to match end of string? | Excel Discussion (Misc queries) | |||
Wildcard MATCH() breaks on long (?) strings | Excel Worksheet Functions |