Value of cell in row based on last non-empty cell in another column

[trigger467]

I am trying to figure out how to show the value of a cell in a row when another column in the same row has the last non-empty cell.

Example.

I have the days of the month listed in cells A2:A31 and sales figures in cells B2:B20.

On a summary page, I am trying to do a "Data accurate through ______" where the missing date is that from cell A20, since cell B20 is the last row with non-empty data in the column.

Any help is appreciated.

trigger467

 

[johnmeyer]

I have the days of the month listed in cells A2:A31 and sales figures in cells B2:B20.

On a summary page, I am trying to do a "Data accurate through ______" where the missing date is that from cell A20, since cell B20 is the last row with non-empty data in the column.

Use an "array formula." Copy and paste this formula into the editing box in Excel:

=INDEX(A2:A32,MATCH(TRUE,B2:B32="",0)-1,0)

Excel Workbook
	A	B	C	D	E
1	Month	Sales
2	1	400		Data Accurate Through:	7
3	2	500
4	3	600
5	4	700
6	5	800
7	6	900
8	7	1000
9	8
10	9
11	10
12	11
13	12
BUT

#VALUE!

Excel 2002

#VALUE!

Entered with Ctrl+Shift+Enter. If entered correctly, Excel will surround with curly braces {}. Note: Do not try and enter these manually yourself

 

[trigger467]

Perfect!

Thanks for your help.

trigger467