+ Reply to Thread
Results 1 to 4 of 4

VLookup the next greater value in a table ( again? )

  1. #1
    Forum Contributor
    Join Date
    07-16-2009
    Location
    DutchLand
    MS-Off Ver
    Excel MacOS 16
    Posts
    161

    VLookup the next greater value in a table ( again? )

    Did search the web and here of course, but found several VLOOKUP and MATCH e.t.c samples, but not the way I need.

    I have a table and the first Column got the question but is also the column to find the answer.

    In the added sample book there is a table with the desired result. The third Table.

    Any suggestions to get it done?
    The problem is also that I cant use a hidden column between the two data and result column. The two table column are in a greater whole and can't change that to avoid mess up the underlaying parts.

    As always, thank you
    Attached Files Attached Files
    Last edited by lord anubis; 04-05-2020 at 04:52 AM.

  2. #2
    Registered User
    Join Date
    01-24-2020
    Location
    Belgium
    MS-Off Ver
    2013
    Posts
    95

    Re: VLookup the next greater value in a table ( again? )

    Frankly speaking, your question is a bit tricky as you didn't make it clear that the lookup value is the numbers in the first column multiplied by 1.6.

    If I'm alright, what you need is the smallest number not less than 1.6 X [Automaat Amp.]

    Since the numbers are sorted in an ascending order, MATCH(lookup_value,lookup_array,match_type) function does the trick with match_type parameter defaulting to 1.
    Formula: copy to clipboard
    Please Login or Register  to view this content.


    I guess a 40 in the mock-up result column (cell in red in the attached file) is a slip of pen?
    Attached Files Attached Files

  3. #3
    Forum Expert
    Join Date
    05-01-2014
    Location
    California, US
    MS-Off Ver
    Excel 2010
    Posts
    1,795

    Re: VLookup the next greater value in a table ( again? )

    @anubis, does this do what you want?
    Please Login or Register  to view this content.
    Note: That returns 32, not 40, for J11 (20). I believe 32 is correct, if I understand your intent, because 20*1.6 = 32 (J13). Compare with J8 and J12.

    Caveat: You might want to write ROUND(J2*1.6,1) instead of J2*1.6, in order to avoid binary arithmetic anomalies. I round to 1 decimal place because all of your examples return decimal values with up to 1 decimal place. You might choose more decimal places if J2 might be non-integers.
    Last edited by joeu2004; 04-04-2020 at 07:27 PM.

  4. #4
    Forum Contributor
    Join Date
    07-16-2009
    Location
    DutchLand
    MS-Off Ver
    Excel MacOS 16
    Posts
    161

    Re: VLookup the next greater value in a table ( again? )

    Thank you both for your effort and solutions.
    Also for the tips, they where also valuable.

    And of course, it should be 32. - - I did do the Nursery School.


    Did learn about
    [Automaat Amp.])+1 in Tables
    ROUND(J2*1.6,1) instead of J2*1.6, in order to avoid binary arithmetic anomalies.
    20*1.6 = 32
    1+MATCH(........
    Last edited by lord anubis; 04-05-2020 at 04:57 AM.

+ 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] IF greater with a Vlookup, no #N/As
    By jomili in forum Excel Formulas & Functions
    Replies: 2
    Last Post: 09-20-2012, 08:13 AM
  2. How to get greater than value returned in lookup or vlookup?
    By RONCONWAY@COMCAST in forum Excel - New Users/Basics
    Replies: 1
    Last Post: 03-19-2012, 02:29 PM
  3. How do I get VLOOKUP to look for the next greater value
    By kingcole in forum Excel General
    Replies: 5
    Last Post: 06-13-2006, 01:10 PM
  4. [SOLVED] vlookup must return a value that is greater than
    By BG in forum Excel Formulas & Functions
    Replies: 3
    Last Post: 05-03-2006, 07:15 AM
  5. vlookup - finding the next value that is GREATER than the lookup value?
    By Harold Good in forum Excel Formulas & Functions
    Replies: 0
    Last Post: 09-06-2005, 06:05 PM
  6. [SOLVED] vlookup - finding the next value that is GREATER than the lookup value?
    By Harold Good in forum Excel Formulas & Functions
    Replies: 0
    Last Post: 09-06-2005, 03:05 PM
  7. vlookup - finding the next value that is GREATER than the lookup value?
    By Bernie Deitrick in forum Excel Formulas & Functions
    Replies: 18
    Last Post: 09-06-2005, 02:05 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