How To Reference Last Row With Data In A Column?

[Dhira]

How do I reference the most recent row number that had data?
Example:
Column C references data in column B
Column B's last data was on row 5
But Column C is now row 17 wants to use the data in Column B row 5 to calculate a formula.

How to reference that row number (i.e. 5; which will change sporadically every time data is entered in Column B)?

 

[Burrgogi]

Easily do-able with a VBA solution. Not sure how to do so with a formula.

 

[Cubist]

Depending on what calculation, there might be a solution. Can you provide an example?

 

[AhoyNC]

Assuming you have values in column B. This will return the last value in the column. As Cubist states above it would help to see an example .
The lookup value in the MATCH function needs to be greater than the largest value in column B.

Book1
	A	B	C	D
1		Data		6
2		2
3		4
4		5
5		6
6
Sheet1

Cell Formulas
Range	Formula
D1	D1	=INDEX(B:B,MATCH(999999999999,B:B))

 

[Dhira]

=INDEX(B:B,MATCH(999999999999,B:B))

Thank you, this is almost it.

What I now need is to somehow be able to show/reference the ROW of the last value (In your example, ROW 5).
How to extract the row is the question.

I'll see if I can put up an example.

 

[MARK858]

Just wrap it in a Row function

Book1
	A	B	C	D
1		Data		5
2		2
3		4
4		5
5		6
Sheet2

Cell Formulas
Range	Formula
D1	D1	=ROW(INDEX(B:B,MATCH(999999999999,B:B)))

 

[Dhira]

Just wrap it in a Row function

Book1
	A	B	C	D
1		Data		5
2		2
3		4
4		5
5		6
Sheet2

Cell Formulas
Range	Formula
D1	D1	=ROW(INDEX(B:B,MATCH(999999999999,B:B)))

Awesome, sir.

I now have almost what I need.
I used your formula to show the row, but ultimately, the reason i wanted the row of 'last data' was so i could use it in a formula to calculate (multiplying it with a percentage).
However, I can't seem to concoct a formula to show that row (of last data) and multiply it by the column.

I managed to give an example below:

Data.xlsx
	A	B	C	D	E
1		(Last) Data	"%"	Last Data Row	Last Data x "%" Column
2		2	100%	2	2
3		4	75%	3	3
4		5	55%	4	2.75
5		6	71%	5	4.26
6			66%	5	3.96
7		6	87%	7	5.22
8			95%	7	5.7
9			35%	7	2.1
10		4	55%	10	2.2
11			71%	10	2.84
12			66%	10	2.64
13			87%	10	3.48
Sheet3

Cell Formulas
Range	Formula
D2:D13	D2	=ROW(INDEX(B$1:B2,MATCH(9999999,B$1:B2)))
E10,E7,E2:E5	E2	=B2*C2
E6,E11,E8	E6	=B5*C6
E9,E12	E9	=B7*C9
E13	E13	=B10*C13

The question is then this:
Now that I have the row with the last data, how do I combine it with a column in order to reference it yet again in 'Column E above (which I changed manually to show the desired result, but I need a formula that calculates it)?

 

[Cubist]

Book1
	A	B	C	D	E
1		(Last) Data	"%"		Last Data x "%"
2		2	100%		2
3		4	75%		3
4		5	55%		2.75
5		6	71%		4.26
6			66%		3.96
7		6	87%		5.22
8			95%		5.7
9			35%		2.1
10		4	55%		2.2
11			71%		2.84
12			66%		2.64
13			87%		3.48
Sheet10

Cell Formulas
Range	Formula
E2:E13	E2	=LOOKUP(2,1/($B$2:B2<>""),$B$2:B2)*C2

 

[Dhira]

Book1
	A	B	C	D	E
1		(Last) Data	"%"		Last Data x "%"
2		2	100%		2
3		4	75%		3
4		5	55%		2.75
5		6	71%		4.26
6			66%		3.96
7		6	87%		5.22
8			95%		5.7
9			35%		2.1
10		4	55%		2.2
11			71%		2.84
12			66%		2.64
13			87%		3.48
Sheet10

Cell Formulas
Range	Formula
E2:E13	E2	=LOOKUP(2,1/($B$2:B2<>""),$B$2:B2)*C2

Thanks so much @Cubist. This is the exact answer I asked for.

I actually needed this for a larger formula I'm workign on, but I wanna give you credit for this, so I'm gonna mark it as complete.

If you get a chance, could you look at my question in this thread? It builds on this one and is the final solution I need.

Thanks again.