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

1. ## 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

2. ## Re: Lookup the last 3 numbers in the bottom of range without empty cells

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

3. ## Re: Lookup the last 3 numbers in the bottom of range without empty cells

You can put this array* formula in C2:

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

then copy down.

*NOTE: an array formula needs to be committed using the key combination of Ctrl-Shift-Enter (CSE), rather than the usual < Enter >.

Hope this helps.

Pete

4. ## Re: Lookup the last 3 numbers in the bottom of range without empty cells

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.

5. ## Re: Lookup the last 3 numbers in the bottom of range without empty cells

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

6. ## Re: Lookup the last 3 numbers in the bottom of range without empty cells

Originally Posted by newdoverman
(ROWS(C\$2:C2)-1)*-1+3
Man, that looks painful!

7. ## Re: Lookup the last 3 numbers in the bottom of range without empty cells

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.

8. ## Re: Lookup the last 3 numbers in the bottom of range without empty cells

Thank you every body
All formula working fine, Now I will try to adpt to my sheet
Thank you

9. ## Re: Lookup the last 3 numbers in the bottom of range without empty cells

You're welcome. We appreciate the feedback!

There are currently 1 users browsing this thread. (0 members and 1 guests)

#### Posting Permissions

• You may not post new threads
• You may not post replies
• You may not post attachments
• You may not edit your posts

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1