How can I apply the ISNUMBER function row-by-row until a value is found?

[Daniel_Longo]

I can use the ISNUMBER formula explicitly to get the result I want. However, I need to do a dynamic row-by-row search until a value is returned and then stop. For example, I want to determine if the value in column A is found in ANY of the rows in column R. If it is, then I want to return the value in that row's column S and place it in column G. In my example I am explicitly searching R5 for the value in A3 and returning S5 to G3. Now, how would I get it to look in R1, R2, R3... until if found the match? The trick for me is having to use the ISNUMBER function to search a string of values in a cell. Otherwise, with my basic understanding I could use a MATCH or INDEX formula I think.

Thanks!

[#2 · Accepted solution]

@Daniel_Longo 

=IFERROR(INDEX($S$1:$S$100, MATCH(TRUE, ISNUMBER(SEARCH(", "&A1&", ", ", "&$R$1:$R$100&", ")), 0)), "")

[#3]

I understand maybe about half of that, but it WORKS!! That means it is magic. Thank you! That is a HUGE help.