+ Reply to Thread
Results 1 to 5 of 5

vlookup returning the closest match down

  1. #1
    Registered User
    Join Date
    09-19-2008
    Location
    Gloucester, MA
    MS-Off Ver
    Excel 2016
    Posts
    62

    vlookup returning the closest match down

    I'm using vlookup to get a price for a unit of measure and it passes the value of the lesser not the greater. Is there anyway to get vlookup to pick the closest match as the greater value not the lesser one?

    For example:
    1000 units costs $8,000
    4000 units cost $23,000
    12000 units cost $68,000

    If someone wants to buy 2500 units, I'd like the vlookup to pass the price based on 4000 units, but it passes 8,000 the value on 1000 units.

    Can I use vlookup to give me the result of the greater unit not the lesser?

    Thanks.
    Last edited by kadams99; 12-08-2009 at 07:59 PM.

  2. #2
    Forum Expert teylyn's Avatar
    Join Date
    10-28-2008
    Location
    New Zealand
    MS-Off Ver
    Excel 365 Insider Fast
    Posts
    11,372

    Re: vlookup returning the closest match down

    Hi,

    you need to rearrange your lookup table to suit the way Vlookup works

    Please Login or Register  to view this content.

  3. #3
    Registered User
    Join Date
    09-19-2008
    Location
    Gloucester, MA
    MS-Off Ver
    Excel 2016
    Posts
    62

    Re: vlookup returning the closest match down

    I tried that, but when I put in 4000 units it doesn't give me the correct price of $23,000. It should work like this:

    1 - 1000 units is $8,000
    1001 - 4000 units is $23,000
    4001 - 12,000 units is $68,000
    etc.

    Thanks.

  4. #4
    Forum Expert teylyn's Avatar
    Join Date
    10-28-2008
    Location
    New Zealand
    MS-Off Ver
    Excel 365 Insider Fast
    Posts
    11,372

    Re: vlookup returning the closest match down

    Then this as your table

    Please Login or Register  to view this content.
    hth

  5. #5
    Registered User
    Join Date
    09-19-2008
    Location
    Gloucester, MA
    MS-Off Ver
    Excel 2016
    Posts
    62

    Re: vlookup returning the closest match down

    I love this site! That worked! You're so smart.

+ 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