+ Reply to Thread
Results 1 to 3 of 3

lookup cells above or below a certain referenced number?

  1. #1
    Registered User
    Join Date
    04-15-2011
    Location
    gray
    MS-Off Ver
    excel 2007
    Posts
    52

    lookup cells above or below a certain referenced number?

    If I have a column of unique numbers and want to reference a cell that appears above or below a referenced number, is this possible?

    A
    132
    11
    100
    90
    75
    60
    190


    I want to know what number appears 3 cells below 90? Is there a formula for that? Please advise. Thank you.

  2. #2
    Forum Expert
    Join Date
    12-15-2009
    Location
    Chicago, IL
    MS-Off Ver
    Microsoft Office 365
    Posts
    3,173

    Re: lookup cells above or below a certain referenced number?

    Assuming your list is in Column A and your lookup value is in C1, in D1 enter =IFERROR(INDEX($A$1:$A$7,MATCH($C$1,$A$1:$A$7,0)+ROW()),"") and drag it down to D3
    Attached Files Attached Files

  3. #3
    Forum Expert icestationzbra's Avatar
    Join Date
    01-07-2004
    MS-Off Ver
    2007, 2010
    Posts
    1,417

    Re: lookup cells above or below a certain referenced number?

    if A2 through A8 have the numbers, B2 is the reference cell, then the value in the 3rd cell below the value referenced by B2 can be found using:

    C2: =IFERROR(INDEX(A2:A8,MATCH(B2,A2:A8,0)+3),"")

+ Reply to Thread

Thread Information

Users Browsing this Thread

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

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