(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