Find last numerical value in row range and return the column header

Hi All,

I'm analysing a large sheet of data and need some assistance:

What I need is to have a formula to find the last numerical value in a row and then return the column header. Basically, this is to do with projecting destruction dates for files in storage over the next 10 years and I want an extra column to display text along the lines of 'Box destruction due in x year' for that row.

Note: Each row is a subtotal which calculates the number of files within a box. To add to the confusion, there are two types of box; 'Mixed Files' which contain files that cannot be destroyed along with files that can and 'Complete Box' which only contains files that can be destroyed. I will modify any formula to include this argument.

I have something crude which kind of works but is basically a long collection of nested IF statements, one for each year, and I want something a bit more streamlined. Call me old skool but I believe any code can be condensed and work the same, if not better.....

I have played about with the 'INDEX' and 'MATCH' functions but not quite got it to work for me.

Thanks for any help!
Tony

Originally Posted by StephenR

=INDEX(A1:E1,1,MATCH(LOOKUP(9.99999999999999E+307,A2:E2),A2:E2,0))

I don't think this will always work, for example if there are duplicates. If the last value is in E2 and is 10.....but 10 also appears in A2 then the formula returns the value from A1

Try this version

=LOOKUP(9.9E+307,A2:E2,A$1:E$1)

Yep, sorry Stephen, I have looked at formula closer and found where it refers to header row (row 1) in BOLD:

=INDEX(R15:AC15,1,MATCH(LOOKUP(9.99999999999999E+307,R15:AC15),R15:AC15,0))

I have it changed now so returns header. Thanks for pointing out Arthur. Any pointers on my second post regarding using this with 'IF' statements?

Yes, Daddylonglegs, you are correct. Some of the numbers do reiterate along the row until it hits the 'Year of Destruction' and then the 'IF' statements return 'N/A'. This woudln't really be an issue though as the number should always increase until it gets to the stage where the number equals the total number of files in that box (present in another column), therefore the last number will always be larger and hence different from the ones before it.
Still, this will also come in handy one day I'm sure

Originally Posted by Fidd$

Any pointers on my second post regarding using this with 'IF' statements?

Do you mean that you might not want the last number because that might be associated with "Mixed files". How can you tell, is there a row that says "Mixed" or "Complete"?

OK, so do you want to simplify the formulas in S6:AC6 or are they only a means to get the results in AD6 and AE6, you could probably get rid of the formulas in S6:AC6 altogether and just calculate those 2 directly.....