1

I'm working off this post: Formula to find the header index of the first non blank cell of a range in Excel?

Where the formula is: =IF(COUNTA(B2:Z2),INDEX(B$1:Z$1,MATCH(TRUE,INDEX(B2:Z2<>"",0),0)),"")

It searches the matrix row from right to left, to find the first value, and then pick the corresponding date from the column header.

How would I reverse the equation, so it searches from the left to right. Finding the last date rather than the first?

Thanks

* edit *

enter image description here

The formula is searching from left to right. For "Rob" it starts at B3 to F3, giving the answer "15-01-01" ... but the answer I want is "15-01-04" as that would search the row from right to left.

For Kyle the correct answer should be "15-01-02"

And for Adam, the correct answer should be "15-01-05"

Does that clear things up?

Community
  • 1
  • 1
Chef1075
  • 2,614
  • 9
  • 40
  • 57

2 Answers2

1

Something similar to this might be what you are looking for. You would need to adjust the LOOKUP to return a result from a different array than the lookup array though, something like this:

=LOOKUP(9.99E+307,B3:F3,$B$2:$F$2)

If you put that in H3 and copy it down it should return the results you're looking for.

Community
  • 1
  • 1
CactusCake
  • 986
  • 3
  • 12
  • 32
0

how about using LARGE (or AGGREGATE):

LARGE(IF(D1:H1<>"",1) * COLUMN(D1:H1) - COLUMN(D1) + 1,COLUMN(D1:H1) - COLUMN(D1) + 1)

This gives the largest column number, so you can wrap an INDEX around this formula to get the Dates.

(This needs to be entered with CSE)

The name's Bob. MS Bob.
  • 859
  • 9
  • 22
Canoeron
  • 26
  • 2