+ Reply to Thread
Results 1 to 3 of 3

Find closer higher value

  1. #1
    Registered User
    Join Date
    12-08-2010
    Location
    Caracas, Venezuela
    MS-Off Ver
    Excel 2003
    Posts
    1

    Find closer higher value

    Ok, this is driving me crazy

    I have a workbook with two sheets

    Sheet 1, Column A has a list of arbitrary and repetitive values: 15, 18, 19,5, 23, etc...

    Sheet 2 has the following:
    Column A has a unique set of numeric values: 10, 20, 30 and so on
    Column B has a text string associated to each unique value

    What I want to do is the following:

    Compare each cell on column A, sheet 1 to column A, sheet 2 (the complete list of unique values). In case there is a match, Sheet1, ColumnB should paste the text string associated to the value found on Sheet2. However, since column A has mostly arbitrary values, the probability of having an exact match are low, in which case what needs to be done is find the closest higher value in Sheet2, Column A; take the text string associated to it on Column 2 and paste it on Sheet1, Column2.

    Any ideas???

    Thanks!

  2. #2
    Forum Expert Cutter's Avatar
    Join Date
    05-24-2004
    Location
    Ontario,Canada
    MS-Off Ver
    Excel 2010
    Posts
    6,451

    Re: Find closer higher value

    Are the values on Sheet 2 actually 10,20,30 etc?

    If so, with your values starting in A1 on each sheet. Put this formula in B1 of Sheet 1 and drag down (adjust the Sheet2 range in the formula):

    =VLOOKUP(A1+9,Sheet2!$A$1:$B$3,2,1)

    BTW - welcome to the forum!
    Last edited by Cutter; 12-08-2010 at 08:49 PM.

  3. #3
    Forum Expert daddylonglegs's Avatar
    Join Date
    01-14-2006
    Location
    England
    MS-Off Ver
    2016
    Posts
    14,675

    Re: Find closer higher value

    Here's one option......

    Try this array formula in sheet1 B2

    =INDEX(sheet2!B2:B100,MATCH(MIN(IF(sheet2!A2:A100>=A2,sheet2!A2:A100)),sheet2!A2:A100,0))

    confirmed with CTRL+SHIFT+ENTER
    Audere est facere

+ 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