+ Reply to Thread
Results 1 to 9 of 9

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

  1. #1
    Forum Contributor
    Join Date
    12-05-2015
    Location
    Albania
    MS-Off Ver
    2013
    Posts
    211

    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
    Last edited by XLalbania; 08-23-2016 at 06:20 PM.

  2. #2
    Forum Guru AlKey's Avatar
    Join Date
    07-20-2009
    Location
    Lakeland, FL USA
    MS-Off Ver
    Microsoft Office 2010/ Office 365
    Posts
    8,904

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

    Try this one
    Enter in B2 and copy down
    Formula: copy to clipboard
    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
    If you like my answer please click on * Add Reputation
    Don't forget to mark threads as "Solved" if your problem has been resolved

    "Nothing is so firmly believed as what we least know."
    --Michel de Montaigne

  3. #3
    Forum Guru Pete_UK's Avatar
    Join Date
    12-31-2011
    Location
    Warrington, England
    MS-Off Ver
    Office 2019 (still learning)
    Posts
    20,981

    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. #4
    Forum Guru Tony Valko's Avatar
    Join Date
    12-31-2011
    Location
    Pittsburgh
    MS-Off Ver
    2002, 2007:2013
    Posts
    18,890

    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.
    Biff
    Microsoft MVP Excel
    Keep It Simple Stupid

    Let's Go Pens. We Want The Cup.

  5. #5
    Forum Expert newdoverman's Avatar
    Join Date
    02-07-2013
    Location
    Port Dover, Ontario, Canada
    MS-Off Ver
    2010
    Posts
    10,330

    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: copy to clipboard
    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
    <---------If you like someone's answer, click the star to the left of one of their posts to give them a reputation point for that answer.
    Ron W

  6. #6
    Forum Guru Tony Valko's Avatar
    Join Date
    12-31-2011
    Location
    Pittsburgh
    MS-Off Ver
    2002, 2007:2013
    Posts
    18,890

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

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

  7. #7
    Forum Expert newdoverman's Avatar
    Join Date
    02-07-2013
    Location
    Port Dover, Ontario, Canada
    MS-Off Ver
    2010
    Posts
    10,330

    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. #8
    Forum Contributor
    Join Date
    12-05-2015
    Location
    Albania
    MS-Off Ver
    2013
    Posts
    211

    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. #9
    Forum Guru Tony Valko's Avatar
    Join Date
    12-31-2011
    Location
    Pittsburgh
    MS-Off Ver
    2002, 2007:2013
    Posts
    18,890

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

    You're welcome. We appreciate the feedback!

+ Reply to Thread

Thread Information

Users Browsing this Thread

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

Similar Threads

  1. [SOLVED] VLOOKUP, gives #N/A error (if Range lookup=0) or wrong data (if Range lookup=empty)
    By Ebalinska in forum Excel Formulas & Functions
    Replies: 19
    Last Post: 01-14-2016, 05:55 AM
  2. Need help deleting empty rows from a range from the bottom of the list up
    By pasqualebaldi in forum Excel Programming / VBA / Macros
    Replies: 2
    Last Post: 05-16-2014, 07:48 PM
  3. Data sort, empty formula cells to bottom merged cells!
    By ikkuh in forum Excel Programming / VBA / Macros
    Replies: 16
    Last Post: 04-09-2013, 03:10 AM
  4. Replies: 1
    Last Post: 11-08-2012, 06:09 AM
  5. Sorting non-empty cells to bottom
    By coopman64 in forum Excel General
    Replies: 5
    Last Post: 10-11-2012, 10:33 AM
  6. [SOLVED] Macro to bring empty cells of SearchResults to the bottom
    By kayoke in forum Excel Programming / VBA / Macros
    Replies: 1
    Last Post: 06-14-2012, 05:25 AM
  7. [SOLVED] Help with a macro to delete empty (but not blank) cells OR sort blanks to the bottom
    By Cyali in forum Excel Programming / VBA / Macros
    Replies: 2
    Last Post: 06-07-2012, 09:04 PM

Bookmarks

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