Get column header of last non-empty cell in unknown row

Question

I have a table in a sheet called "DATA" with the following headers:

Country, Code, Series, 2000, 2001, 2002, 2003, 2004, 2005, 2006.

In each row I have data for all columns always, except for years. Some rows have data for some years only, others all years.

In sheet "DATA AVAILABILITY" I want to build a formula which returns the most recent year for which there is available information in sheet "DATA", given a certain country and code. The relevant country and codes are in cells E2 and A3 of "DATA AVAILABILITY". Let's say, for argument's sake, that these are Country: Angola; Code: 3.

I have first built an array MATCH formula with two criteria:

={MATCH(1,('DATA AVAILABILITY'!E$2=Data!$B$1:$B$104701)*('DATA AVAILABILITY'!$A3=Data!$D$1:$D$104701),0)}

This has successfully given me the row in "DATA" in which there is information for Angola and code 3, which is row 1776.

Now I would like to get the header for the last non-empty cell of row 1776 in sheet "DATA". For this, I started by building a formula that would give me the column number of that cell:

=LOOKUP(2,1/(Data!1776:1776<>""),COLUMN(Data!1776:1776))

It successfully returned the number 53 which, after verifying on sheet "Data" is the correct number. I then added to the formula so that it would return the header, i.e., the year, instead of the column number:

=INDEX(Data!$A$1:$BE$104701,1,LOOKUP(2,1/(Data!1776:1776<>""),COLUMN(Data!1776:1776)))

Finally, I would like to combine both formulas (the MATCH and the INDEX formulas) so that the final result would be returned with one formula only. However, when I try to do it, something goes wrong and an error comes up - I am not even able to enter the formula. When I click ENTER, Excel returns an error that says there is a problem with the formula. what I have tried to do is to replace, in the LOOKUP within the INDEX, "Data!1776:1776" for the array MATCH formula that returns the row in which the information is - in my example, row 1776. The final formula which is not working is as follows:

=INDEX(Data!$A$1:$BE$104701,1,LOOKUP(2,1/(MATCH(1,('DATA AVAILABILITY'!E$2=Data!$B$1:$B$104701)*('DATA AVAILABILITY'!$A3=Data!$D$1:$D$104701)<>""),COLUMN(MATCH(1,('DATA AVAILABILITY'!E$2=Data!$B$1:$B$104701)*('DATA AVAILABILITY'!$A3=Data!$D$1:$D$104701))))

What may I be doing wrong? Thank you

Can you provide a snapshot of your sheet? We can use the data from there to run your formula — Zac
@Zac How do you copy the information from a "snapshot" into the worksheet? — Ron Rosenfeld
Hi, thanks for your reply! @Zac the sheet has a lot of confidential information and, as such, I would not be able to upload it here. I could do a mock-up sheet if you'd like. Thanks a lot! — franciscofcosta
Mock-up is exactly what I meant. It means we don't have to guess. @RonRosenfeld: by snapshot I meant a cut of the worksheet and not an actual screen shot :). Although, I believe there are tools/techniques you can use to capture data from screen shots — Zac
@Zac Sorry. I interpreted "snapshot" to mean "screenshot". And my OCR program sometimes works, but more often than not, doesn't — Ron Rosenfeld

Ron Rosenfeld Ron Rosenfeld · Accepted Answer · 2018-02-08T17:41:33

Hard to tell what is going on without at least some sample data (as a table or linked workbook -- NOT as a screenshot), and I would do it a bit differently.

You can simplify your formula to get the Header of the column that contains the last data in row 1776:

=LOOKUP(2,1/(Data!1776:1776<>""),Data!$1:$1)

To return the column number:

=LOOKUP(2,1/(Data!1776:1776<>""),COLUMN(Data!$1:$1))

To return the Appropriate Row Number (enter with CSE):

=MAX(($E$2=Data!$B$1:$B$104701)*(A3=Data!$D$1:$D$104701)*ROW($A$1:$A$104701))

To return the last filled in value, in the row that matches Country and Code, we make use of the fact that using 0 for the column number in the INDEX function returns all the columns in the designated row:

=LOOKUP(2,1/(INDEX(Data!$B$1:$BE$104701,MAX(($E$2=Data!$B$1:$B$104701)*(A3=Data!$D$1:$D$104701)*ROW($A$1:$A$104701)),0)<>""),INDEX(Data!$B$1:$BE$104701,MAX(($E$2=Data!$B$1:$BE$104701)*(A3=Data!$D$1:$D$104701)*ROW($A$1:$A$104701)),0))

entered with CSE.

Get column header of last non-empty cell in unknown row

1 Answers