Find value within range and get it as result

Hi all

I have the folowing layout

J1 = "Nr. 8" K1 = "Bl. 44" L1 = "Sc. b" M1 = "Ap. 33"

The above is not always in this order. I need a formula that will search the range J:M for a case insensitive string and return the found value as result.

I tried the folosing but it doesn;t suit my needs

Please Login or Register  to view this content.

Thank you

Perhaps:

Please Login or Register  to view this content.

Maybe:

=IF(ISNUMBER(MATCH("*Nr*", $J1:$M1, 0)), INDEX($J1:$M1, MATCH("*Nr*", $J1:$M1, 0)), "")


EDIT: Ah, xl07, definitely use IFERROR() since it's available to you.