return value based on wildcard formula

[snowman1976]

hello
I am hoping someone can help with a formula I need to return a value based on a wildcard. I made a small example below. In one worksheet I have a list of wildcard values with correlating data in column B (AB430, AS430, etc). On a separate worksheet I have the values listed , but this time full part number, and I want a formula to return the corresponding data from column B. is there a formula that can do this? I already have a different check in place to make sure no two wildcards are alike in the first sheet, so there should only be on 1 possible match


A B
68058679* AB430
60630-TR3 -A500* AS430
8A8Z-7410608* AD440

68058679AZ-A result here

 

[Aladin Akyurek]

It's unlear what is the input and what constitutes output...

 

[snowman1976]

Sorry, I know its confusing and I couldn't lay it out the way I wanted.
Let me try this again.
I have a data list in column A that has wildcards attached (i.e. 68058679*). In this list I have data in column B that I want to retrieve using a formula.
The problem is this - in the second sheet the data I need to match does not have the wildcard, instead it has the suffix (68058679AZ-A). If I try a regular index/match formula it doesn't work. Is there any way to retrieve the data I want ?

 

[Aladin Akyurek]

Sorry, I know its confusing and I couldn't lay it out the way I wanted.
Let me try this again.
I have a data list in column A that has wildcards attached (i.e. 68058679*). In this list I have data in column B that I want to retrieve using a formula.
The problem is this - in the second sheet the data I need to match does not have the wildcard, instead it has the suffix (68058679AZ-A). If I try a regular index/match formula it doesn't work. Is there any way to retrieve the data I want ?

Post A1:A3 first then beneath that post B1:B3. When done, post the look up value separately...

 

[snowman1976]

68058679*
60630-tr3 -a500*
8a8z-7410608*

ab430
as430
ad440


68058679az-a
60630-tr3-a500
8a81-7410608-aa

 

[kvsrinivasamurthy]

IF A2:B4 is the data range and Lookup value is G2 then Array Formula in H2 is

=IFERROR(INDEX($B$2:$B$4,SMALL((IF(ISNUMBER(FIND(LEFT($A$2:$A$4,LEN($A$2:$A$4)-1),G2)),ROW($A$2:$A$4),"")),1)-ROW($A$2)+1),"")

ARRAY formula is used


To enter ARRAY formula
Paste the formula
Press F2
Press Ctrl+Shift+Enter keys together.

 

[snowman1976]

this is fantastic, works perfectly....thanks for the help !

 

[Aladin Akyurek]

68058679*
60630-tr3 -a500*
8a8z-7410608*

ab430
as430
ad440


68058679az-a
60630-tr3-a500
8a81-7410608-aa

Must the last three match A1:A3?