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

1. ## 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?

Cheers

2. ## 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. ## 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. ## 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. ## 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.

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. ## 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).

9. ## 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.

10. ## 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. ## 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!)

There are currently 1 users browsing this thread. (0 members and 1 guests)

#### 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