Excel get last data in column and return value from different column

1
votes

I have a table that I've created in excel that dynamically inputs data from a stock so my column A is growing every day.

I'm looking for a formula that will search column A for the last data available then return back that row's column H. So based on the photo, it would return the value -0.14.

I've figured out how to get to the last cell with data in column A but can't figure out how to pull information from that row's column H (I can't apply that same method to column H because that column has 0s throughout)

= INDEX(A:A,COUNT(A:A))

enter image description here

1
excelexcel-formula
Ok this would seem to be straightforward to me if you Vlookup from that last found cell. – David Wooley - AST

1 Answers

1
votes

You are on the right track with your formula. Just replace column A:A with H:H. The final formula will be:

=INDEX(H:H,COUNT(A:A))

Be aware that this formula won't work if you have blanks in your data (since it won't count them). In this case you can use this formula:

=INDEX(H:H,MAX((A:A<>"")*(ROW(A:A))))

The term MAX((A:A<>"")*(ROW(A:A))) will find the highest row number that is not a blank and return it to the INDEX function, which picks the corresponding value from column H