lookup

  • Thread starter Thread starter Dave Bigelow
  • Start date Start date
D

Dave Bigelow

Hi:

I have a simple spreadsheet with locations down the right
hand side, a date series across the top. In the
intersection of the columns and rows I enter numbers to
specify an activity. On the right hand side I have a
column entitled "date last active". How can I automate
cells in this column to return the last date active
without building a nested function with 30 series in it?

Dave
 
Hi Dave,

Try this:

With the date series in C1:H1.

=SUMPRODUCT((C6:H6=LOOKUP(MAX(C6:H6)+1,C6:H6))*C$1:H$1)

Of course, adjust the ranges to suit and format the target
cells as date.

Biff
 
Dave Bigelow said:
Hi:

I have a simple spreadsheet with locations down the right
hand side, a date series across the top. In the
intersection of the columns and rows I enter numbers to
specify an activity. On the right hand side I have a
column entitled "date last active". How can I automate
cells in this column to return the last date active
without building a nested function with 30 series in it?

Dave
Click to expand...

Suppose, as a simple example, that your dates are in A1:J1. If I understand
correctly, you will put numbers in some cells of A2:J2 and you want a
formula in K2 that returns the date in row 1 corresponding to whichever of
these numbers is furthest to the right. (You will then copy this formula
down for rows 3, 4 etc.) Try this formula in K2:
=INDEX(A1:J1,MAX(NOT(ISBLANK(A2:J2))*COLUMN(A2:J2)))
This is an array formula, so needs to be entered using CTRL+SHIFT+ENTER
rather than just ENTER.
 
Back
Top