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

_cn_10x24

_cn_13x30

_cn_16x20

_cn_17x17

_cn_24x24

_cn_24x30

_cn_2pc_16x20

_cn_2pc_17x17

_cn_30x30

_cn_30x40

_cn_3pc_16x20

_cn_3pc_16x24

_cn_5pc_10x21

_cn_9pc_12x12

_fr_11x14

_fr_12x12

_fr_16x20

_wd_10x15

_wd_10x15

_wd_12x12

_wd_13x19...etc

<colgroup><col></colgroup><tbody>
</tbody>

 

[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.