Search range of text strings in array

[RaulSerg]

Hi, pls see attached example.

Column E contains a range of search criteria.
For every instance where any of those strings is found in column B, the corresponding value in column F shall be output.

The list in column E may get quite long, so I need a variable/spilling approach.

Any help much appreciated.

[AliGW]

I don't see how it can be done when 123aifm needs to return something different to other instances of aifm or AIFM. Please provide more detailed sample data and explain the logic of each result.

[RaulSerg]

Apologies, I think I mixed up two output results.
Please find revised attached.

[AliGW]

Try this SPILL formula:

=BYROW(B2:B6,LAMBDA(r,XLOOKUP(1,--ISNUMBER(SEARCH(E2:E3,r)),F2:F3)))

[AliGW]

Seen, but no reply ...

[AliGW]

Could you please respond to the help you have received in this thread? Some acknowledgement of the assistance I have given you freely would be appreciated. Also, the thread will need marking as solved.

Thank you.

[RaulSerg]

Yes, seen but had not yet had a chance to test a bit further and take a few mins to digest, so didn't respond yet.
Only managed to go through now.

Works for my purposes, thanks.
Will mark solved.

Only thing one/I need to keep in mind: if a source input for some reason happens to contain both search criteria, only/always the first search criterion gets matched, so the order can make a difference in some very rare cases.

[AliGW]

Your sample data needs to be realistic. If it isn’t, you won’t get a solution that works exactly as you want. We use your sample data to test solutions.