+ Reply to Thread
Results 1 to 2 of 2

find greatest value less than criteria (lookup/match?), use relative cell values

  1. #1
    Registered User
    Join Date
    07-27-2012
    Location
    Arizona
    MS-Off Ver
    Excel 2003
    Posts
    1

    find greatest value less than criteria (lookup/match?), use relative cell values

    I am working on a spreadsheet in which I need to first find a value in a running sum that is the greatest value less than a criterion value. for this I have successfully used both lookup and match functions each alone. however, I then need to use not only the cell value that is found, but also the value of two cells relative to that but going both directions, one in the same column, one in a different column and different row.

    so, to clarify, if I have the following data:

    A1: 2012 B1: 2013 C1: 2014 D1: 2015
    A2: 3.4 B2: 4.1 C2: 4.8 D2: 5.2

    A3: 5 (criteria goal)

    my intention is to ultimately have a function that uses cells C2, C1 and D1

    the formula below returns the value of C2 correctly

    =LOOKUP(A3,A2:D2)

    but I cannot figure out how to write a function that then pulls C1 and D1 values as well, using those along with C2

  2. #2
    Forum Contributor CheshireCat's Avatar
    Join Date
    10-11-2011
    Location
    Victoria, Canada
    MS-Off Ver
    Excel 2003 & 2010
    Posts
    258

    Re: find greatest value less than criteria (lookup/match?), use relative cell values

    Using INDEX and MATCH, try:

    for the value (equivalent to C2)

    Please Login or Register  to view this content.
    for the value (equivalent to C1)

    Please Login or Register  to view this content.
    for the value (equivalent to D1)

    Please Login or Register  to view this content.
    Docendo discimus.

+ 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