+ Reply to Thread
Results 1 to 11 of 11

Two Part VLOOKUP Question: finding to the left, and finding next largest number

  1. #1
    Registered User
    Join Date
    11-18-2009
    Location
    VanIsle
    MS-Off Ver
    Excel 2003
    Posts
    6

    Two Part VLOOKUP Question: finding to the left, and finding next largest number

    Excel noob here, hopefully this isn't too confusing.

    In column A I have dates and in column B I have numbers (represented as 6.5 or 9.0).

    An example of the problem I'm having with VLOOKUP:

    In cell B3 I have the number 8.0 and I am trying to use VLOOKUP to look down column B for next number greater than or equal to B3 and return the date from the column to the left.

    All functions I've tried inserting to find "next greater than or equal to" haven't worked and VLOOKUP only seems to return the number to the right or below any given cell in the range so I've given up after a couple days and narrowed this problem down to two specific ones:

    1) How do search a column for the next number greater than or equal to?
    2) How do you return data from the left?

    Thank you for your time
    Cheers

  2. #2
    Forum Moderator Paul's Avatar
    Join Date
    02-05-2007
    Location
    Wisconsin
    MS-Off Ver
    2016/365
    Posts
    6,881

    Re: Two Part VLOOKUP Question: finding to the left, and finding next largest number

    Hi Ghost, and welcome to the forum.

    As you've seen, VLOOKUP can only look to the right of the lookup column, but an INDEX/MATCH function can look up, down, left or right based on how you use it.

    For your problem, you might try the array formula:

    =INDEX(A1:A100,MATCH(MIN(IF(B1:B100>B3,B1:B100)),B1:B100,0))

    After typing or editing this formula, you must press CTRL+SHIFT+ENTER, not just ENTER. When done properly, Excel will automatically insert braces around your formula. It might not make sense at first, but if you read through some of the threads about INDEX/MATCH, you'll see how handy it can actually be!

    Note: If your range of dates/numbers goes past row 100, just edit that part of the formula accordingly. Using full columns in this formula (if used a lot in the workbook) will make calculations slow down a bit.

  3. #3
    Registered User
    Join Date
    11-18-2009
    Location
    VanIsle
    MS-Off Ver
    Excel 2003
    Posts
    6

    Re: Two Part VLOOKUP Question: finding to the left, and finding next largest number

    Hi Paul, thank you for your time.
    I tried the function you suggested but it gives me Error#508, and it doesn't seem like I'm missing any brackets.

    I tried playing the the INDEX/MATCH formula myself but it doesn't make much sense to me yet.

  4. #4
    Forum Moderator Paul's Avatar
    Join Date
    02-05-2007
    Location
    Wisconsin
    MS-Off Ver
    2016/365
    Posts
    6,881

    Re: Two Part VLOOKUP Question: finding to the left, and finding next largest number

    Error 508? Are you trying to use this in VBA or on the spreadsheet? I don't recall the last time I saw an error number when entering a spreadsheet formula.

    Are you using Excel, OpenOffice, Office for Mac?

  5. #5
    Forum Guru martindwilson's Avatar
    Join Date
    06-23-2007
    Location
    London,England
    MS-Off Ver
    office 97 ,2007
    Posts
    19,320

    Re: Two Part VLOOKUP Question: finding to the left, and finding next largest number

    error 508 is an OpenOffice thing

    508
    Pair missing
    Missing bracket, for example, closing brackets, but no opening brackets.
    "Unless otherwise stated all my comments are directed at OP"

    Mojito connoisseur and now happily retired
    where does code go ?
    look here
    how to insert code

    how to enter array formula

    why use -- in sumproduct
    recommended reading
    wiki Mojito

    how to say no convincingly

    most important thing you need
    Martin Wilson: SPV
    and RSMBC

  6. #6
    Registered User
    Join Date
    11-18-2009
    Location
    VanIsle
    MS-Off Ver
    Excel 2003
    Posts
    6

    Re: Two Part VLOOKUP Question: finding to the left, and finding next largest number

    Hmm, yeah I put that in when I joined the forum but for some reason it says the wrong program above my post.

  7. #7
    Registered User
    Join Date
    11-18-2009
    Location
    VanIsle
    MS-Off Ver
    Excel 2003
    Posts
    6

    Re: Two Part VLOOKUP Question: finding to the left, and finding next largest number

    Well, I'm trying it in Excel in 2007 right now too, and although I feel like I'm making progress (I get anything from multiple wrong dates to numbers or errors) I still can't return the right date from column A.

    EDIT: It seems to returning different dates than the one I want in Excel 07. It was looking for 8.0 only, and then 8.1 only, but now I think I got it to look for greater than or equal to 8.0 but I don't know how to get it to identify the first TRUE it sees when going down the list (which corresponds to the closest possible date in column A).
    Last edited by ghosttomost; 11-19-2009 at 06:28 PM.

  8. #8
    Forum Guru martindwilson's Avatar
    Join Date
    06-23-2007
    Location
    London,England
    MS-Off Ver
    office 97 ,2007
    Posts
    19,320

    Re: Two Part VLOOKUP Question: finding to the left, and finding next largest number

    post your excel workbook

  9. #9
    Forum Moderator Paul's Avatar
    Join Date
    02-05-2007
    Location
    Wisconsin
    MS-Off Ver
    2016/365
    Posts
    6,881

    Re: Two Part VLOOKUP Question: finding to the left, and finding next largest number

    The formula I gave you doesn't even reference 8.0 or 8.1. It references the value in B3. If that number happens to be 8 or 8.1 so be it. It also is specifically coded to find the date corresponding to the next largest value. Take a look at the example sheet I've attached.

    If it still doesn't make sense, then do as martin suggested and upload a sample workbook.
    Attached Files Attached Files

  10. #10
    Registered User
    Join Date
    11-18-2009
    Location
    VanIsle
    MS-Off Ver
    Excel 2003
    Posts
    6

    Re: Two Part VLOOKUP Question: finding to the left, and finding next largest number

    Ah, thanks again Paul, I undestand what the formula is meant to do now.

    I see it's my fault, I guess I didn't specify what I meant by 'next largest'. The formula you've given searches for the next largest number when what I meant was that I wanted to look for the next nearest number greater than or equal to B3 when going down from the top. So, to use the example you've attached, the date I would want returned would be 1/7/09 since it's number is 9.65 which is larger than 1/5/09's 7.71.

    I hope this helps...
    To clarify though I have my dates in reverse order (closest to present is at the top) so the date I'm looking for is the lateset date that has a number greater than or equal to B3.

  11. #11
    Registered User
    Join Date
    11-18-2009
    Location
    VanIsle
    MS-Off Ver
    Excel 2003
    Posts
    6

    Re: Two Part VLOOKUP Question: finding to the left, and finding next largest number

    Hmm, I guess this was all too confusing for everyone.

    I'll ask for help one last time before I promise to leave everyone alone!

    I've attached my workfile (it's a list of earthquakes). What I'm trying to do is have in column G the # of days since the last earthquake of equal or larger size. To do this, I imagined using some sort of function to look down column B for the next earthquake of greater or equal magnitude and return the date of the earthquake (from column A). In the attached sheet I haven't included any of the formulas suggested in this thread or any of the variations I've tried. Instead I've included a manual input in G3 of the desired output.

    Hope this helps, thanks again everyone
    (don't judge my colour coded list!)
    Attached Files Attached Files

+ 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