Index and Match on keywords within text.

[ChocksterNo1]

Hi

I have keywords in a named Range called Table. I'm trying to see if a an excel report has any of these keywords in Column O and if so, return that keyword.

The formula I've tried is

=IFERROR(INDEX(Table,MATCH(TRUE,ISNUMBER(SEARCH(Table,"*"&O2&"*")),0)),"Not Found")

All it returns is Not Found for every row.

Could someone have a look at my formula to see what I've done wrong?

Thanks

Chock

 

[steve the fish]

That works if array entered.

 

[ChocksterNo1]

I've tried ****ft + Ctrl + Enter and it doesn;t seem to work. Is there anything I'm missing?

thanks

Jamie

 

[steve the fish]

Are you seeing the curly brackets {} around either end of the formula?

 

[ChocksterNo1]

no, can I manually insert them?

 

[steve the fish]

No. When you have the formula entered into the cell instead of pressing enter press like this:

Control key, then keeping control pressed press the shift key. Keeping them both pressed press enter. The curly brackets will appear and excel will attempt array processing of your formula.

 

[ChocksterNo1]

got it working now, thanks for your help