Using wildcard * in MATCH
berniean
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
Max
> 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(ISNUMBER(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
---
berniean
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!
Dave Peterson
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.
--
Dave Peterson
berniean
"Prod1".
berniean
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:
> .
>
Max
It'll come in handy someday, for someone somewhere.
--
Max
Singapore
http://savefile.com/projects/236895
Downloads:27,000 Files:200 Subscribers:70
xdemechanik
---
news:A9EC9171-0F2B-4346...@microsoft.com...
