+ Reply to Thread
Results 1 to 18 of 18

Return Row no. for Lookup from bottom of range, ignoring bottom n no. of rows

  1. #1
    Registered User
    Join Date
    04-06-2012
    Location
    India
    MS-Off Ver
    Pro Plus 2019
    Posts
    70

    Return Row no. for Lookup from bottom of range, ignoring bottom n no. of rows

    Hi!

    This is a sort of follow-up question to one of my earlier posts on this forum (sorry unable to post the link since posting of html tags is probably prohibited), wherein I wanted to lookup a value from the bottom of a column range, ignoring the last 5 rows.

    The formula I am using is
    Please Login or Register  to view this content.
    since it addresses my requirement of lookup for non-numeric values.

    I am however using the lookup function for almost 50 columns with 3000 rows and that is slowing down my calculations considerably (data being updated in real time). Since the same formula is being repeated for same row no. across all 50 columns with only the lookup column changing, I guess it would be faster if I could get the Row no. (similar to Match function) and use Indirect function to get data in the same row no. from all 50 columns.

    So the request is, can I modify the above formula to get the row no. instead of the the actual value (similar to Match Function from bottom of range ignoring last 5 rows).

    Sample File attached
    Attached Files Attached Files

  2. #2
    Forum Guru
    Join Date
    12-14-2012
    Location
    London England
    MS-Off Ver
    MS 365 Office Suite.
    Posts
    7,849

    Re: Return Row no. for Lookup from bottom of range, ignoring bottom n no. of rows

    =count(f1:f15)-5
    Please Avoid Joining My List Of Blocked Users by:

    Saying Please and Thank you.

    Making requests not demands.

    Checking back on your post. I will not edit any post after 4 days.

    Marking threads as closed once your issue is resolved. How? The tools at the top

    Any reputation (*) points appreciated. None of us gets paid here.

    If you found someone's input useful, please take a second to click the * at the bottom left to let them know

  3. #3
    Registered User
    Join Date
    04-06-2012
    Location
    India
    MS-Off Ver
    Pro Plus 2019
    Posts
    70

    Re: Return Row no. for Lookup from bottom of range, ignoring bottom n no. of rows

    Quote Originally Posted by mehmetcik View Post
    =count(f1:f15)-5
    hi mehmtcik,

    This is a simple count function. I want to find the row no. where the particular value occurs (99 in my sample excel file), starting from the bottom of the range (ignoring the last 5 rows of latest data).

  4. #4
    Forum Guru
    Join Date
    03-02-2006
    Location
    Los Angeles, Ca
    MS-Off Ver
    WinXP/MSO2007;Win10/MSO2016
    Posts
    11,117

    Re: Return Row no. for Lookup from bottom of range, ignoring bottom n no. of rows

    named range:
    AllBut5
    Please Login or Register  to view this content.
    Please Login or Register  to view this content.
    **array entered....

    for just the row # then, (CSE)
    Please Login or Register  to view this content.
    Last edited by protonLeah; 12-26-2018 at 03:53 PM.
    Ben Van Johnson

  5. #5
    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: Return Row no. for Lookup from bottom of range, ignoring bottom n no. of rows

    Deleted by AlKey
    Last edited by AlKey; 12-26-2018 at 03:58 PM.
    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

  6. #6
    Registered User
    Join Date
    04-06-2012
    Location
    India
    MS-Off Ver
    Pro Plus 2019
    Posts
    70

    Re: Return Row no. for Lookup from bottom of range, ignoring bottom n no. of rows

    Quote Originally Posted by protonLeah View Post
    named range:
    Please Login or Register  to view this content.
    **array entered....
    This formula (with array entered) gives a #REF! error

    Please Login or Register  to view this content.
    This gives me row no. 7 as the result (with array entered), but the expected result is 4 since i need to ignore the last 5 rows. Could you modify this formula to ignore the last 5 rows while calculating the row no.?

    Thanks for the options..

  7. #7
    Forum Guru
    Join Date
    03-02-2006
    Location
    Los Angeles, Ca
    MS-Off Ver
    WinXP/MSO2007;Win10/MSO2016
    Posts
    11,117

    Re: Return Row no. for Lookup from bottom of range, ignoring bottom n no. of rows

    It works with the sample provided
    Attached Files Attached Files

  8. #8
    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: Return Row no. for Lookup from bottom of range, ignoring bottom n no. of rows

    Hi naira,

    Could you please provide an example of the desired outcome?
    Thanks,

  9. #9
    Registered User
    Join Date
    04-06-2012
    Location
    India
    MS-Off Ver
    Pro Plus 2019
    Posts
    70

    Re: Return Row no. for Lookup from bottom of range, ignoring bottom n no. of rows

    Quote Originally Posted by protonLeah View Post
    It works with the sample provided
    Hi protonLeah,

    It probably works since the named range AllBut5 refers to
    Please Login or Register  to view this content.
    Consequently, the formula is dependent on having some data in column G. In case I delete data in row 4 of col. G, I get answer as row 3.

    I just want the row no. (ignoring the last 5 rows) where 99 occurs in column F (without referring to column G).

    Sorry, I guess my original post did not make it clear (probably since I had posted that I wanted to modify my original formula).

    I intend to get data from Col. G using the Indirect function.

  10. #10
    Registered User
    Join Date
    04-06-2012
    Location
    India
    MS-Off Ver
    Pro Plus 2019
    Posts
    70

    Re: Return Row no. for Lookup from bottom of range, ignoring bottom n no. of rows

    Quote Originally Posted by AlKey View Post
    Hi naira,

    Could you please provide an example of the desired outcome?
    Thanks,
    Hi Alkey,

    Am looking for answer as 4 (which is row 4) of my sample file attached herewith.
    Attached Files Attached Files

  11. #11
    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: Return Row no. for Lookup from bottom of range, ignoring bottom n no. of rows

    Here is a simpler approach:
    Enter formula in A3 and drag formula across to B3
    Formula: copy to clipboard
    Please Login or Register  to view this content.

    v A B C D E F G
    1 98 a
    2 Value for Lookup Original Formula 99 s
    3 99 f 99 d
    4 99 f
    5 96 g
    6 95 h
    7 99 j
    8 98 k
    9 97 l
    10
    11 Desired row no. from Match Function 4

  12. #12
    Registered User
    Join Date
    04-06-2012
    Location
    India
    MS-Off Ver
    Pro Plus 2019
    Posts
    70

    Re: Return Row no. for Lookup from bottom of range, ignoring bottom n no. of rows

    Quote Originally Posted by AlKey View Post
    Here is a simpler approach:
    Enter formula in A3 and drag formula across to B3
    hi Alkey,

    It seems Col. G is creating a lot of confusion (as protonLeah also misunderstood my request).

    Please see image with simplified data of requirement (and Col. G removed).

    Capture.JPG
    Last edited by naira; 12-26-2018 at 04:57 PM.

  13. #13
    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: Return Row no. for Lookup from bottom of range, ignoring bottom n no. of rows

    Enter in C2
    Formula: copy to clipboard
    Please Login or Register  to view this content.

  14. #14
    Registered User
    Join Date
    04-06-2012
    Location
    India
    MS-Off Ver
    Pro Plus 2019
    Posts
    70

    Re: Return Row no. for Lookup from bottom of range, ignoring bottom n no. of rows

    Quote Originally Posted by AlKey View Post
    Enter in C2
    Formula: copy to clipboard
    Please Login or Register  to view this content.
    Sorry Alkey,

    It seems you have got it the other way round.

    I want the result of the Formula as 4 (not 99)

  15. #15
    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: Return Row no. for Lookup from bottom of range, ignoring bottom n no. of rows

    Quote Originally Posted by naira View Post
    Sorry Alkey,

    It seems you have got it the other way round.

    I want the result of the Formula as 4 (not 99)
    You never mentioned where the 4 should come from...

  16. #16
    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: Return Row no. for Lookup from bottom of range, ignoring bottom n no. of rows

    Maybe this
    Enter in D11
    This will give you 4
    Formula: copy to clipboard
    Please Login or Register  to view this content.

  17. #17
    Forum Guru Bo_Ry's Avatar
    Join Date
    09-10-2018
    Location
    Thailand
    MS-Off Ver
    MS 365
    Posts
    4,489

    Re: Return Row no. for Lookup from bottom of range, ignoring bottom n no. of rows

    Please try at D11

    =MATCH(2,INDEX(1/(F1:INDEX(F:F,COUNT(F:F)-5)=A3),))

  18. #18
    Registered User
    Join Date
    04-06-2012
    Location
    India
    MS-Off Ver
    Pro Plus 2019
    Posts
    70

    Re: Return Row no. for Lookup from bottom of range, ignoring bottom n no. of rows

    Thanks Bo_Ry,

    Come to my rescue once again

+ 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. Lookup from bottom of range, ignoring bottom n no. of rows
    By naira in forum Excel Formulas & Functions
    Replies: 8
    Last Post: 12-05-2018, 04:26 AM
  2. OFFSETTING a reference range to exclude 3 bottom rows
    By drgkt in forum Excel Formulas & Functions
    Replies: 3
    Last Post: 09-30-2017, 06:10 PM
  3. [SOLVED] Help with formula to return bottom five rows that are not zero
    By rv02 in forum Excel Formulas & Functions
    Replies: 6
    Last Post: 09-18-2017, 07:24 AM
  4. [SOLVED] Lookup the last 3 numbers in the bottom of range without empty cells
    By XLalbania in forum Excel General
    Replies: 8
    Last Post: 08-24-2016, 07:49 AM
  5. [SOLVED] Rows copying from bottom to top. need to copy top to bottom.
    By jafvortex93 in forum Excel Programming / VBA / Macros
    Replies: 2
    Last Post: 12-01-2015, 02:07 PM
  6. Draw bottom border on every rows from top to bottom
    By tantcu in forum Excel Programming / VBA / Macros
    Replies: 3
    Last Post: 10-08-2013, 09:52 AM
  7. Replies: 1
    Last Post: 04-09-2006, 04:30 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