Need a worksheet function that will search through a row of common entries and return the one that i

  • Thread starter Thread starter Steve
  • Start date Start date
S

Steve

If I were to try and enter a column functions for the cells under
column "N", where the formula in cell "N1" would look at cells "A1 ->
M1" and give me the only result that is not "F", (SEE BELOW), what
would be the best way to handle this?

CELLS A1 THROUGH F1 = "F"

CELL G1 = "Bracket" (this cell's designation and entry could vary from
row to row, i.e. in row 2 the odd cell may be C2 and might say
"Housing")

CELLS H1 THROUGH M1 = "F"


I've tried the following,
....IF(A1="F",IF(B1="F",IF(C1="F",IF(D1="F",E1,"F"),"F"),"F"),"F")...,
however the formula sometimes gets to long and I have to use several
columns to achieve what I'm after:


Please Help,

Steve
 
Ctrl/Shift/Enter:
=INDEX(A1:M1,MATCH(TRUE,A1:M1<>"F",0))
If A1:M1 are ALL "F", the above will return an error. If you need to show
nothing in that case, then ctrl/shift/enter:
=IF(ISNA(MATCH(TRUE,A1:M1<>"F",0)),"",INDEX(A1:M1,MATCH(TRUE,A1:M1<>"F",0)))

A live, online Excel Master Class is starting in September (which I'm
teaching).
For details, please follow this link:

http://www.iil.com/str_link_all_results.asp?select_cartid=395


Bob Umlas
Excel MVP
 
Ctrl/Shift/Enter:
=INDEX(A1:M1,MATCH(TRUE,A1:M1<>"F",0))
If A1:M1 are ALL "F", the above will return an error. If you need to show
nothing in that case, then ctrl/shift/enter:
=IF(ISNA(MATCH(TRUE,A1:M1<>"F",0)),"",INDEX(A1:M1,MATCH(TRUE,A1:M1<>"F",0)))
Click to expand...

This doesn't catch situations in which there were more than one non-F in A1:M1.
To do that as well as catching nothing but Fs, try

=CHOOSE(1+MIN(COUTIF(A1:M1,"<>F"),2),"all Fs",
INDEX(A1:M1,MATCH(TRUE,A1:M1<>"F",0),"multiple non-Fs")

More functionality with no additional function calls.
A live, online Excel Master Class is starting in September (which I'm
teaching).
For details, please follow this link:
Click to expand...

Still SPAMMING the newsgroups, eh?

Is this course really going to be worth US $2,200 ?
 
Thanks a lot, you just made my life a lot easier and my spreadsheet a
lot smaller....steve
 
I tried using this formula you provided and only get the '#NAME?'
error message. Any suggestions? ...
Click to expand...
...

Oops, typo. Try

=CHOOSE(1+MIN(COUNTIF(A1:M1,"<>F"),2),"all Fs",
INDEX(A1:M1,MATCH(TRUE,A1:M1<>"F",0),"multiple non-Fs")
 
Harlan,

I tried the formula you've provided me with and I can manage to get
the "all Fs" and the "multiple non-Fs" to work, however for the last
part to show the single item in the row that is different I'm getting
the "#VALUE!" error message. Please help...

I've also noticed at other times a formula will be doing what it is
supposed to be doing, however once I place my cursor up in the formula
bar for that formula for whatever reason I end up getting this
"#VALUE!" error message. Who knows?


Regards,

Steve
 
Bob,

I was able to make my problem work with your formula, however for some
reason my spreadsheet and formulas were very sensitive. I had noticed
that after using your formula in my spreadsheet that at times it would
do what it is
supposed to be doing, however once I place my cursor up in the formula
bar for that formula for whatever reason I end up getting this
"#VALUE!" error message, even though I wouldn't change a thing... Who
knows?


Please help,

Steve
 
Back
Top