CODEWITHSUNDEEP
excelexcel-formulaexcel-indirect
user3109467
user3109467

Reputation: 15

Formula to get last value in columns then value of another cell in the same row

I have a table with rows = depth and columns = value of the surveys done in each month and year. Not all years/months have surveys so some cells are blank. What I want to do is to determine to which depth each survey has been done, because it is not the same for all years/months. The depth values are stored in Column E and the survey values are from Column F to DM.

I was able to call the last value in each column using this formula from Pearson: 

=INDIRECT(ADDRESS(MAX((F1:F111<>"")*(ROW(F1:F111))),COLUMN(F:F)))

but this gives me value (which I don't need), not depth.

I want to know which depth (row value) this last value corresponds to.

Upvotes: 1

Views: 814

Answers (1)

pnuts
pnuts

Reputation: 59442

Please try: 

=INDEX($E:$E,MAX((F1:F111<>"")*(ROW(F1:F111))))

entered as an array formula in a row outside the range 1:111 and copied across to suit (so probably from F:DM).

Upvotes: 1

Related Questions