Index for partial match in lookup value

[A Rossi]

Hi everyone

I am trying to get an Index Match from a table based on alphanumeric values contained in the lookup value.

Sheet2 Table1 ColumnA Header[Item] contains the lookup array
Sheet2 Table1 ColumnB header[Shape] contains the return value array

Sheet1 ColumnA contains the lookup value
Sheet1 ColuimnB is where the return value will populate

Ex. find cn_16x20 within BRP-222_cn_16x20

I keep getting NA. Can't figure it out.

Thanks Rick

below is the formula

=INDEX(Table1[Shape],MATCH(TRUE,ISNUMBER(SEARCH(Table1[Item],A2)),0))

 

[mole999]

cn and x won't be ISNUMBER = TRUE, also I'm going to suggest the the whole will be text

 

[A Rossi]

Hi

But i have about 30 different values to match. I have a few different types of formulas with no luck

[TABLE="width: 139"]
<colgroup><col></colgroup><tbody>[TR]
[TD]_cn_10x24[/TD]
[/TR]
[TR]
[TD]_cn_13x30[/TD]
[/TR]
[TR]
[TD]_cn_16x20[/TD]
[/TR]
[TR]
[TD]_cn_17x17[/TD]
[/TR]
[TR]
[TD]_cn_24x24[/TD]
[/TR]
[TR]
[TD]_cn_24x30[/TD]
[/TR]
[TR]
[TD]_cn_2pc_16x20[/TD]
[/TR]
[TR]
[TD]_cn_2pc_17x17[/TD]
[/TR]
[TR]
[TD]_cn_30x30[/TD]
[/TR]
[TR]
[TD]_cn_30x40[/TD]
[/TR]
[TR]
[TD]_cn_3pc_16x20[/TD]
[/TR]
[TR]
[TD]_cn_3pc_16x24[/TD]
[/TR]
[TR]
[TD]_cn_5pc_10x21[/TD]
[/TR]
[TR]
[TD]_cn_9pc_12x12[/TD]
[/TR]
[TR]
[TD]_fr_11x14[/TD]
[/TR]
[TR]
[TD]_fr_12x12[/TD]
[/TR]
[TR]
[TD]_fr_16x20[/TD]
[/TR]
[TR]
[TD]_wd_10x15[/TD]
[/TR]
[TR]
[TD]_wd_10x15[/TD]
[/TR]
[TR]
[TD]_wd_12x12[/TD]
[/TR]
[TR]
[TD]_wd_13x19...etc[/TD]
[/TR]
</tbody>[/TABLE]

 

[Aladin Akyurek]

Try to post a small part of Table1, the value in A2, and the result that must obtain.

 

[AhoyNC]

You have an array formula are you entering your formula with CTRL-SHIFT-ENTER? If not you will get an #NA .

 

[A Rossi]

Try to post a small part of Table1, the value in A2, and the result that must obtain.

Hi Aladin

 

[Aladin Akyurek]

Does this work for you?

=LOOKUP(9.99999999999999E+307,SEARCH(Table1[Item],A2),Table1[Shape])

 

[A Rossi]

Does this work for you?

=LOOKUP(9.99999999999999E+307,SEARCH(Table1[Item],A2),Table1[Shape])

Works GREAT!

I have never seen that one before. Will be adding it to my snippets library.

Thanks

P.S. I have another code i am working on. Can i message you when ready?

 

[Aladin Akyurek]

@ A Rossi

You are welcome.