Selecting first value

  • Thread starter Thread starter Kou
  • Start date Start date
K

Kou

I am trying to select the first value that comes up
in a row from left to right. The value isn't neccessarily
the highest value or the lowest, thus the MAX function is
out of question. I don't want to write a macro, so I was
wondering if anyone had an idea of how to do this with a
simple forumula?
 
Kou,

Here is one way
=INDIRECT(CHAR(MIN(IF(NOT(ISBLANK(B2:Y2)),COLUMN(B2:Y2)))+64)&ROW($B$2))

Its an array formula, so enetr with Ctrl-Sift-Enter.

B2:Y2 are the cells being tested, just change to suit.

--

HTH

Bob Phillips
... looking out across Poole Harbour to the Purbecks
(remove nothere from the email address if mailing direct)
 
Hi Bob,

This is not "my" thread, but since I have been banging my head over this problem
over an hour I must reply.

Five (out of five) stars to you!

However, I have made a slight modification to your formula to make it possible
to fill it down, which is what I was trying to achieve.

In A2 array enter
=INDIRECT(CHAR(MIN(IF(NOT(ISBLANK(B2:Y2)),COLUMN(B2:Y2)))+64)&ROW(A2))
then just fill down.

Best regards,
Anders Silvén
 
I am trying to select the first value that comes up
in a row from left to right. The value isn't neccessarily
the highest value or the lowest, thus the MAX function is
out of question. I don't want to write a macro, so I was
wondering if anyone had an idea of how to do this with a
simple forumula?
Click to expand...

I believe the array-entered formula:

=INDEX(5:5,1,MATCH(TRUE,ISNUMBER(5:5),0))

will find the first numeric value in row 5. Change the row designation for
different rows.

To array-enter, hold down <ctrl><shift> while hitting <enter>. XL will place
braces {...} around the formula.

If by "value" you mean any entry, text or number or error or Boolean, then post
back.


--ron
 
I'm not sure if I fully understand this, but it works!!!

Thanks to you all for asking and answering!
 
I'm not sure if I fully understand this, but it works!!!

Thanks to you all for asking and answering!
Click to expand...

Well, let's pull it apart then:

=INDEX(5:5,1,MATCH(TRUE,ISNUMBER(5:5),0))


ISNUMBER(5:5) will return an array of {FALSE,FALSE,FALSE,TRUE,...}
with the TRUE appearing at the location of the first numeric entry.

The MATCH function then returns the location of that entry -- 4 in the
above example. We use 0 as an optional argument since the array is not sorted.

This number will be the column number, which we use in the INDEX function.


--ron
 
Back
Top