Lookup the last 3 numbers in the bottom of range without empty cells

Hello Excel Gurus
As in the title I need a formula in cell C2 and copy down Lookup the last 3 numbers in the bottom of range without empty cells
Thnk you so much for your help

	A	B	C
1	Range		last 3
2	33		66
3			9
4	22		26
5
6	66
7	9
8
9
10	26
11
12

Try this one
Enter in B2 and copy down

Formula:

Please Login or Register  to view this content.

this part is what controls number of rows. There are 5 values in 9 rows (COUNT(A:A)-2) ( 5-2 leaves 3 rows with data)

v	A	B
1
2	33	66
3		9
4	22	26
5
6	66
7	9
8
9
10	26

Try this...

Data Range

	A	B
1	Range	Last 3
2	33	66
3		9
4	22	26
5
6	66
7	9
8
9
10	26

This array formula** entered in B2 and copied down to B4:

=IFERROR(INDEX(A:A,LARGE(IF(A$2:A$10<>"",ROW(A$2:A$10)),4-ROWS(B$2:B2))),"")

** array formulas need to be entered using the key
combination of CTRL,SHIFT,ENTER (not just ENTER).
Hold down both the CTRL key and the SHIFT key
then hit ENTER.

This works for me as it is tolerant of moving the original data if necessary during editing. Enter in C2 with Ctrl + Shift + Enter (array formula)

Formula:

Please Login or Register  to view this content.

	A	B	C
2	33		55
3			56
4	22		44
5
6	66
7	9
8
9
10	26
11
12	77
13
14	55
15
16	56
17	44
18
19
20

or

	C	D
2	55
3	56
4	44
5		33
6
7		22
8
9		66
10		9
11
12
13		26
14
15		77
16
17		55
18
19		56
20		44
21
22
23

Originally Posted by newdoverman

(ROWS(C$2:C2)-1)*-1+3

Man, that looks painful!

Hi Tony, it is a bit painful It could payoff if doing a lot of editing. I've had the miserable experience of people adding side-bars, titles etc until I was ready to shoot them (boss and his boss who couldn't make up their minds). It was a case of self defence so I had to make it as "bullet proof" as I could

If the requirement changes to need a different number of values, just change the 3 at the end to whatever is required and fill down.

For the OP: The (ROWS(C$2:C2)-1)*-1+3 is just a count down that starts at 3. If the statement is changed to (ROWS(C$2:C2)-1)*1+3 the counter counts up starting at 3.

You're welcome. We appreciate the feedback!