Help with Multiple Text matching one wildcard text, and index the value behind

[JOINGO]

Hello All,

Thank you in advance for your help.

What I try to do is alike fuzzy match:

A1: H00002745-P01-10
A2: H00003817-P01-10
A3: H00004155-Q01-10

C1: H*-P*-10
D1: $215

I use formula in B1:=INDEX(D:D, MATCH(A1,C:C,0))
Trying to bring $215 to B1 and B2. Because I think the H00002745-P01-10 and H00003817-P01-10 both match the wildcard text: H*-P*-10

But B1 returned #N/A

Where should I modified in the formula of B1?


[TABLE="width: 125"]
<colgroup><col></colgroup><tbody>[TR]
[TD][TABLE="width: 125"]
<colgroup><col></colgroup><tbody>[TR]
[TD]KY[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[/TR]
</tbody>[/TABLE]

 

[Aladin Akyurek]

Hello All,

Thank you in advance for your help.

What I try to do is alike fuzzy match:

A1: H00002745-P01-10
A2: H00003817-P01-10
A3: H00004155-Q01-10

C1: H*-P*-10
D1: $215

I use formula in B1:=INDEX(D:D, MATCH(A1,C:C,0))
Trying to bring $215 to B1 and B2. Because I think the H00002745-P01-10 and H00003817-P01-10 both match the wildcard text: H*-P*-10

But B1 returned #N/A

Where should I modified in the formula of B1?


[TABLE="width: 125"]
<TBODY>[TR]
[TD][TABLE="width: 125"]
<TBODY>[TR]
[TD]KY
[/TD]
[/TR]
</TBODY>[/TABLE]

[/TD]
[/TR]
</TBODY>[/TABLE]

Your layout is unclear....

[TABLE="width: 425"]
<COLGROUP><COL style="WIDTH: 142pt; mso-width-source: userset; mso-width-alt: 6712" width=189><COL style="WIDTH: 48pt" span=3 width=64><COL style="WIDTH: 91pt; mso-width-source: userset; mso-width-alt: 4295" width=121><COL style="WIDTH: 48pt" width=64><TBODY>[TR]
[TD="class: xl65, width: 189, bgcolor: transparent"]H00002745-P01-10[/TD]
[TD="class: xl65, width: 64, bgcolor: transparent, align: right"]25[/TD]
[TD="class: xl65, width: 64, bgcolor: transparent"] [/TD]
[TD="class: xl65, width: 64, bgcolor: transparent"] [/TD]
[TD="class: xl65, width: 121, bgcolor: transparent"]H*-P*-10[/TD]
[TD="class: xl65, width: 64, bgcolor: transparent, align: right"]125[/TD]
[/TR]
[TR]
[TD="class: xl65, bgcolor: transparent"]H00003817-P01-10[/TD]
[TD="class: xl65, bgcolor: transparent, align: right"]100[/TD]
[TD="class: xl65, bgcolor: transparent"] [/TD]
[TD="class: xl65, bgcolor: transparent"] [/TD]
[TD="class: xl65, bgcolor: transparent"] [/TD]
[TD="class: xl65, bgcolor: transparent"] [/TD]
[/TR]
[TR]
[TD="class: xl65, bgcolor: transparent"]H00004155-Q01-10[/TD]
[TD="class: xl65, bgcolor: transparent, align: right"]200[/TD]
[TD="class: xl65, bgcolor: transparent"] [/TD]
[TD="class: xl65, bgcolor: transparent"] [/TD]
[TD="class: xl65, bgcolor: transparent"] [/TD]
[TD="class: xl65, bgcolor: transparent"] [/TD]
[/TR]
</TBODY>[/TABLE]

F1:

=SUMIF($A$1:$A$3,$E1,$B$1:$B$3)

 

[JOINGO]

Your layout is unclear....

[TABLE="width: 425"]
<tbody>[TR]
[TD="class: xl65, width: 189, bgcolor: transparent"]H00002745-P01-10[/TD]
[TD="class: xl65, width: 64, bgcolor: transparent, align: right"]25[/TD]
[TD="class: xl65, width: 64, bgcolor: transparent"][/TD]
[TD="class: xl65, width: 64, bgcolor: transparent"][/TD]
[TD="class: xl65, width: 121, bgcolor: transparent"]H*-P*-10[/TD]
[TD="class: xl65, width: 64, bgcolor: transparent, align: right"]125[/TD]
[/TR]
[TR]
[TD="class: xl65, bgcolor: transparent"]H00003817-P01-10[/TD]
[TD="class: xl65, bgcolor: transparent, align: right"]100[/TD]
[TD="class: xl65, bgcolor: transparent"][/TD]
[TD="class: xl65, bgcolor: transparent"][/TD]
[TD="class: xl65, bgcolor: transparent"][/TD]
[TD="class: xl65, bgcolor: transparent"][/TD]
[/TR]
[TR]
[TD="class: xl65, bgcolor: transparent"]H00004155-Q01-10[/TD]
[TD="class: xl65, bgcolor: transparent, align: right"]200[/TD]
[TD="class: xl65, bgcolor: transparent"][/TD]
[TD="class: xl65, bgcolor: transparent"][/TD]
[TD="class: xl65, bgcolor: transparent"][/TD]
[TD="class: xl65, bgcolor: transparent"][/TD]
[/TR]
</tbody>[/TABLE]

F1:

=SUMIF($A$1:$A$3,$E1,$B$1:$B$3)

Thank you for your kind help Aladin.

My layout is like this:

The A column are list of product numbers, they can be assigned to patterns like C1 (wildcard form).
I try to map the price from the D column to the different product number according to pattern of product number.

So I use Formula=INDEX(D:D, MATCH(A1, C:C, 0)) in B1, but it does not work.

I have googled this in many pages, I am wondering if there's a better formula for this text-wildcard match.

[TABLE="class: grid, width: 500, align: left"]
<tbody>[TR]
[TD]A [/TD]
[TD]B[/TD]
[TD]C[/TD]
[TD]D[/TD]
[/TR]
[TR]
[TD]H00002745-P01-10[/TD]
[TD][/TD]
[TD]H*-P*-10[/TD]
[TD]$125[/TD]
[/TR]
[TR]
[TD]H00003817-P01-10[/TD]
[TD][/TD]
[TD]H*-Q*-10[/TD]
[TD]$100[/TD]
[/TR]
[TR]
[TD]H00004155-Q01-10[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]

 

[Aladin Akyurek]

Thank you for your kind help Aladin.

My layout is like this:

The A column are list of product numbers, they can be assigned to patterns like C1 (wildcard form).
I try to map the price from the D column to the different product number according to pattern of product number.

So I use Formula=INDEX(D:D, MATCH(A1, C:C, 0)) in B1, but it does not work.

I have googled this in many pages, I am wondering if there's a better formula for this text-wildcard match.

[TABLE="class: grid, width: 500, align: left"]
<TBODY>[TR]
[TD]A
[/TD]
[TD]B
[/TD]
[TD]C
[/TD]
[TD]D
[/TD]
[/TR]
[TR]
[TD]H00002745-P01-10
[/TD]
[TD][/TD]
[TD]H*-P*-10
[/TD]
[TD]$125
[/TD]
[/TR]
[TR]
[TD]H00003817-P01-10
[/TD]
[TD][/TD]
[TD]H*-Q*-10
[/TD]
[TD]$100
[/TD]
[/TR]
[TR]
[TD]H00004155-Q01-10
[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
</TBODY>[/TABLE]

What is the result you want to see and for what?

Never mind... Try:

B1, copied down:

=LOOKUP(9.99999999999999E+307,SEARCH($C$1:$C$2,$A1),$D$1:$D$2)

 

[JOINGO]

Thank you! Your help means a lot to me!

Your formula that did brings price(D volumn) to its matched Product number according to the number pattern. Thank you very much for your kind help.

KY

 

[Aladin Akyurek]

Thank you! Your help means a lot to me!

Your formula that did brings price(D volumn) to its matched Product number according to the number pattern. Thank you very much for your kind help.

KY

You are welcome. Thanks for providing feedback.