Tags: cell, display, entry, excel, formula, instance, microsoft, msdn, number, occurred, represents, row, software, value

Finding the first instance of a cell value

On Microsoft » Microsoft Excel

1,795 words with 1 Comments; publish: Wed, 04 Jun 2008 12:11:00 GMT; (3061,812.50, « »)

Hi,

I am trying to come up with a formula to display the number of days since a

cell value occurred.

Each row represents a daily data entry from machine output readings.

My data looks like:

green red orange purple black

yellow red blue brown orange

blue green brown pink yellow

etc.

What I need to get is the number of days since a color has occurred. The

most recent days are at the top, so if my formula searched for black, it

would return the value 1 because black is in the first row. If I searched

for brown it would return the value 2 because the first instance of brown is

in the second row.

Any help is appreciated.

Thanks

Chris

All Comments

Leave a comment...

  • 1 Comments
    • Hi,

      To Find for "Orange" in Rng, the following Array formula (Ctrl-Shift-Enter):

      =MATCH(1,MMULT(--(Rng="Orange"),TRANSPOSE(COLUMN(Rng))*0+1),0)

      Regards,

      Daniel M.

      "c" <c.excel.questionfor.info.me.org> wrote in message

      news:uT6Uc.79018$6t1.16131.excel.questionfor.info.twister.rdc-kc.rr.com...

      > Hi,

      > I am trying to come up with a formula to display the number of days since a

      > cell value occurred.

      > Each row represents a daily data entry from machine output readings.

      > My data looks like:

      > green red orange purple black

      > yellow red blue brown orange

      > blue green brown pink yellow

      > etc.

      > What I need to get is the number of days since a color has occurred. The

      > most recent days are at the top, so if my formula searched for black, it

      > would return the value 1 because black is in the first row. If I searched

      > for brown it would return the value 2 because the first instance of brown is

      > in the second row.

      > Any help is appreciated.

      > Thanks

      > Chris

      >

      #1; Wed, 04 Jun 2008 12:13:00 GMT