return value based on wildcard formula

snowman1976

Board Regular
Joined
Nov 4, 2008
Messages
191
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
 

Some videos you may like

Excel Facts

Create a chart in one keystroke
Select the data and press Alt+F1 to insert a default chart. You can change the default chart to any chart type

snowman1976

Board Regular
Joined
Nov 4, 2008
Messages
191
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

MrExcel MVP
Joined
Feb 14, 2002
Messages
85,201
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

Board Regular
Joined
Nov 4, 2008
Messages
191

ADVERTISEMENT

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

ab430
as430
ad440


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

kvsrinivasamurthy

Well-known Member
Joined
Nov 6, 2013
Messages
657
IF A2:B4 is the data range and Lookup value is G2 then Array Formula in H2 is
Code:
=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.
 

Watch MrExcel Video

Forum statistics

Threads
1,122,509
Messages
5,596,568
Members
414,079
Latest member
Frills

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top