Last Value and Row in a Column

[jay333]

I need a formula that provides the value in Column 'A' at Row 'X' where 'X' is the last row for which there is data in Column 'B'. In the below example this would mean - for the last row in Col 'B' that has a value what is the value in Col 'A'. In this case the result would be 'Apr'. I have a formula which finds the last value in Col 'B' which I do need (ie. 75), however, I now need a formula that provides the corresponding month (ie. Apr). Any suggestions? Thank you in advance.
[TABLE="width: 123"]
<TBODY>[TR]
[TD]Col 'A'</SPAN>[/TD]
[TD]Col 'B'</SPAN>[/TD]
[/TR]
[TR]
[TD]Jan</SPAN>[/TD]
[TD]100</SPAN>[/TD]
[/TR]
[TR]
[TD]Feb</SPAN>[/TD]
[TD]95</SPAN>[/TD]
[/TR]
[TR]
[TD]Mar</SPAN>[/TD]
[TD]100</SPAN>[/TD]
[/TR]
[TR]
[TD]Apr</SPAN>[/TD]
[TD]75</SPAN>[/TD]
[/TR]
[TR]
[TD]May</SPAN>[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Jun</SPAN>[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Jul</SPAN>[/TD]
[TD][/TD]
[/TR]
</TBODY><COLGROUP><COL span=2></COLGROUP>[/TABLE]

 

[Jeff Meyers]

See if this works for you:

Sheet2[TABLE="class: html-maker-worksheet"]
<thead>[TR]
[TH][/TH]
[TH]A[/TH]
[TH]B[/TH]
[/TR]
</thead><tbody>[TR]
[TH]1[/TH]
[TD]Jan[/TD]
[TD="align: right"]100[/TD]
[/TR]
[TR]
[TH]2[/TH]
[TD]Feb[/TD]
[TD="align: right"]95[/TD]
[/TR]
[TR]
[TH]3[/TH]
[TD]Mar[/TD]
[TD="align: right"]100[/TD]
[/TR]
[TR]
[TH]4[/TH]
[TD]Apr[/TD]
[TD="align: right"]75[/TD]
[/TR]
[TR]
[TH]5[/TH]
[TD]May[/TD]
[TD][/TD]
[/TR]
[TR]
[TH]6[/TH]
[TD]Jun[/TD]
[TD][/TD]
[/TR]
[TR]
[TH]7[/TH]
[TD]Jul[/TD]
[TD][/TD]
[/TR]
[TR]
[TH]8[/TH]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TH]9[/TH]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TH]10[/TH]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TH]11[/TH]
[TD]Apr[/TD]
[TD][/TD]
[/TR]
[TR]
[TH]12[/TH]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TH]13[/TH]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TH]14[/TH]
[TD]------------[/TD]
[TD]------------[/TD]
[/TR]
[TR]
[TH]15[/TH]
[TD][/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]
Excel 2010

Cell	Formula
A11	=OFFSET(A1,COUNT(B1:B7)-1,0)

<tbody>
[TD="bgcolor: #FFFFFF"] Worksheet Formulas [TABLE="class: html-maker-worksheet"]
<thead>[TR]

</thead><tbody>
</tbody>

[/TD]
[/TR]
</tbody>[/TABLE]

 

[Rick Rothstein]

Given this formula a try...

=INDEX(A:A,SUMPRODUCT(MAX(ROW(B$1:B$65535)*(B$1:B$65535<>""))))

 

[jay333]

This works great. Thanks!

 

[Jeff Meyers]

Happy to help out. Thanks for the feedback.

 

[T. Valko]

I need a formula that provides the value in Column 'A' at Row 'X' where 'X' is the last row for which there is data in Column 'B'. In the below example this would mean - for the last row in Col 'B' that has a value what is the value in Col 'A'. In this case the result would be 'Apr'. I have a formula which finds the last value in Col 'B' which I do need (ie. 75), however, I now need a formula that provides the corresponding month (ie. Apr). Any suggestions? Thank you in advance.
[TABLE="width: 123"]
<TBODY>[TR]
[TD]Col 'A'</SPAN>
[/TD]
[TD]Col 'B'</SPAN>
[/TD]
[/TR]
[TR]
[TD]Jan</SPAN>
[/TD]
[TD]100</SPAN>
[/TD]
[/TR]
[TR]
[TD]Feb</SPAN>
[/TD]
[TD]95</SPAN>
[/TD]
[/TR]
[TR]
[TD]Mar</SPAN>
[/TD]
[TD]100</SPAN>
[/TD]
[/TR]
[TR]
[TD]Apr</SPAN>
[/TD]
[TD]75</SPAN>
[/TD]
[/TR]
[TR]
[TD]May</SPAN>
[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Jun</SPAN>
[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Jul</SPAN>
[/TD]
[TD][/TD]
[/TR]
</TBODY>[/TABLE]

Here's another one...

=LOOKUP(1E100,B:B,A:A)