IF Formulas to Find Text String in Specified Range

  • Thread starter Thread starter Kerrick Sawyers
  • Start date Start date
K

Kerrick Sawyers

Hello everyone!

I am hoping that someone can help me out here. I am
looking for an IF formula that will allow me to search a
specified range for data that does not = "--" and then
return that value in a specified cell. Does anyone know
of a way to do this. I have tried numerous conditional
formulas that will only return a value for two columns.
The range I am working with is actually 8 columns wide.
Thanks in advance for any help that you might be able to
give me. Take care.

Kindest regards,

Kerrick Sawyers
GE Healthcare
 
Kerric

This may seem convoluted but if you have the data in the range A1:H8, then array enter (shift, ctrl, enter) the following formula into the required output cell. Worked with the data I tested. Modify the range as required

Ton
=OFFSET(A1,MAX((A1:H8<>"""--""")*(ROW(A1:H8)))-1,MAX((INDIRECT("a"&MAX((A1:H8<>"""--""")*(ROW(A1:H8)))&":h"&MAX((A1:H8<>"""--""")*(ROW(A1:H8))))<>"""--""")*COLUMN(A:H))-1


----- Kerrick Sawyers wrote: ----

Hello everyone

I am hoping that someone can help me out here. I am
looking for an IF formula that will allow me to search a
specified range for data that does not = "--" and then
return that value in a specified cell. Does anyone know
of a way to do this. I have tried numerous conditional
formulas that will only return a value for two columns.
The range I am working with is actually 8 columns wide.
Thanks in advance for any help that you might be able to
give me. Take care

Kindest regards

Kerrick Sawyer
GE Healthcar
 
Thanks! However, I get an error message - Probably my own
fault, I forgot that each cell actually begins with the
number 0 even though it is formatted as text. Any ideas
on how to fix this? Thanks! :)

Regards,

Kerrick

-----Original Message-----
Kerrick

This may seem convoluted but if you have the data in the
Click to expand...
range A1:H8, then array enter (shift, ctrl, enter) the
following formula into the required output cell. Worked
with the data I tested. Modify the range as required.
Tony
=OFFSET(A1,MAX((A1:H8<>"""--""")*(ROW(A1:H8)))-1,MAX
Click to expand...
((INDIRECT("a"&MAX((A1:H8<>"""--""")*(ROW(A1:H8)))&":h"&MAX
 
Kerrick

This may seem convoluted but if you have the data in the range A1:H8, then array enter (shift, ctrl, enter) the following formula into the required output cell. Worked with the data I tested. Modify the range as required.


Tony
=OFFSET(A1,MAX((A1:H8<>"""--""")*(ROW(A1:H8)))-1,MAX((INDIRECT("a"&MAX((A1:H8<>"""--""")*(ROW(A1:H8)))&":h"&MAX((A1:H8<>"""--""")*(ROW(A1:H8))))<>"""--""")*COLUMN(A:H))-1)


----- Kerrick Sawyers wrote: -----

Hello everyone!

I am hoping that someone can help me out here. I am
looking for an IF formula that will allow me to search a
specified range for data that does not = "--" and then
return that value in a specified cell. Does anyone know
of a way to do this. I have tried numerous conditional
formulas that will only return a value for two columns.
The range I am working with is actually 8 columns wide.
Thanks in advance for any help that you might be able to
give me. Take care.

Kindest regards,

Kerrick Sawyers
GE Healthcare
Click to expand...
 
Thanks! However, I get an error message - Probably my own
fault, I forgot that each cell actually begins with the
number 0 even though it is formatted as text. Any ideas
on how to fix this? Thanks! :) ...
Click to expand...

The original specs were ambiguous as to what was sought. Only specs given for
what to discard. BTW, does the text "--" include the double quotes, or do the
double hyphens appear in cells without double quotes? Tony assumed the double
quotes were in the cells and that the double hyphens within the double quotes
were the entire contents of these cells.

Tony's formula could generate #REF! errors if there were double hyphens in every
cell. What error message do you get? Details are essential.

If the double hyphens are not enclosed in double quotes but could be substrings
in cells in which they occur, then try the following to find the bottommost,
rightmost value not containing double hyphens.

=INDEX(A1:H8,MAX(ISERROR(FIND("--",A1:H8))*ROW(A1:H8)),
MAX(ISERROR(FIND("--",INDEX(A1:H8,MAX(ISERROR(FIND("--",A1:H8))
*ROW(A1:H8)),0)))*COLUMN(A1:H8)))

This searches down then right. To search right then down, use

=INDEX(A1:H8,MAX(ISERROR(FIND("--",INDEX(A1:H8,0,
MAX(ISERROR(FIND("--",A1:H8))*COLUMN(A1:H8)))))*ROW(A1:H8)),
MAX(ISERROR(FIND("--",A1:H8))*COLUMN(A1:H8)))
 
Back
Top