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

**ghosttomost** · 11-18-2009, 09:38 PM

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

**Paul** · 11-19-2009, 12:18 AM

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.

**ghosttomost** · 11-19-2009, 03:03 PM

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.

**Paul** · 11-19-2009, 03:20 PM

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?

**martindwilson** · 11-19-2009, 04:55 PM

error 508 is an OpenOffice thing

508
Pair missing
Missing bracket, for example, closing brackets, but no opening brackets.

**ghosttomost** · 11-19-2009, 05:49 PM

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

**ghosttomost** · 11-19-2009, 05:59 PM

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

**martindwilson** · 11-19-2009, 06:33 PM

post your excel workbook

**Paul** · 11-19-2009, 06:42 PM

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.

**ghosttomost** · 11-19-2009, 07:22 PM

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.

**ghosttomost** · 12-17-2009, 02:00 AM

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!)

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

LinkBack

Thread Tools

Rate This Thread

Display

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

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

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

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

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

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

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

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

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

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

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

Thread Information

Users Browsing this Thread

Bookmarks

Bookmarks

Posting Permissions