Look in a row for a word

Murph - Sep 24, 2009 at 07:49 AM
 Trowa - Sep 28, 2009 at 09:11 AM

I am looking for a formula that will look in a row G8:IR8 for the any cell that contains "Wrong Stream" (which is there because of an if statement - if(G5<>G10, "Wrong Stream", "") etc for each column).

I need it to look in this row for "Wrong Stream" and if there is a "Wrong Stream" I need it to say "Wrong Stream" in cell F10 and if there are no occurrences of "Wrong Stream" then it is to return nothing.

I have tried using an if statement: =IF(G8:IR8="Wrong Stream", "Wrong Stream", "") but it doesn't work.

I am trying to stay away from macros to make it more user friendly.

Also, if you know of a formula that will make the =MAX() formula keep the formatt of the cell that it picks, that would be greatly appreciated as well.

Thank you in advance for your time and help.


1 reply

I'm using a dutch version of excel and don't know the exact translation of the functions name.

With the function Compare (?) you can determine the columnnumber of the word you are searching for. If the word is not found you get an error message. Use this function in conjunction with the IF function and you should be able to create a function doing exactly what you want.

If you need more help let me know.

Best regards,