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.

Col 'A'</SPAN>	Col 'B'</SPAN>
Jan</SPAN>	100</SPAN>
Feb</SPAN>	95</SPAN>
Mar</SPAN>	100</SPAN>
Apr</SPAN>	75</SPAN>
May</SPAN>
Jun</SPAN>
Jul</SPAN>

<TBODY>
</TBODY><COLGROUP><COL span=2></COLGROUP>

 

[Jeff Meyers]

See if this works for you:

Sheet2

	A	B
1	Jan	100
2	Feb	95
3	Mar	100
4	Apr	75
5	May
6	Jun
7	Jul
8
9
10
11	Apr
12
13
14	------------	------------
15

<thead>
</thead><tbody>
</tbody>

Excel 2010

Worksheet Formulas Cell Formula A11 =OFFSET(A1,COUNT(B1:B7)-1,0) <thead> </thead><tbody> </tbody>

<tbody>
</tbody>

 

[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.

Col 'A'</SPAN>	Col 'B'</SPAN>
Jan</SPAN>	100</SPAN>
Feb</SPAN>	95</SPAN>
Mar</SPAN>	100</SPAN>
Apr</SPAN>	75</SPAN>
May</SPAN>
Jun</SPAN>
Jul</SPAN>

<TBODY>
</TBODY>

Here's another one...

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