Lookup lowest value in a list for an item for a specific date

**JungleJme** · 07-26-2012, 04:39 AM

Hi,

I've been trying to do the following:

I have a list of machines and i have a long list of services for those machine. Each machine has a contract start date. I want to find the first service (i.e. service entry with the lowest date value) that comes AFTER the contract start date

I tried a couple of ways to no avail, any help is greatly appreciated! See the example attached and thanks for any help!

(also posted on ozgrid http://www.ozgrid.com/forum/showthre...348#post620348)

Ta

J

**zbor** · 07-26-2012, 04:55 AM

Can you also upload example workbook?

**JungleJme** · 07-26-2012, 05:00 AM

I had uploaded an xlsx and then thought i would add an .xls instead. In tyring to do so IE crashed 5 times in a row for no apparent reason.

The original xlsx should appear now. I'll try uploading an xls again in a minute once the real possibility of me putting my fist through my monitor has passed......

**benishiryo** · 07-26-2012, 05:08 AM

hi JungleJme, this seems to work:
=MIN(IF(($A$16:$A$21=A3)*($B$16:$B$21>B3),$B$16:$B$21))

copy this formula & paste into the formula bar. then press CTRL + SHIFT + ENTER

**zbor** · 07-26-2012, 05:12 AM

Try this (comfirmed with ctrl+shift+enter):

Formula:

=MIN(IF(($A$16:$A$21=$A3)*($C$16:$C$21="Yes")*($B$16:$B$21>B3),$B$16:$B$21))

**JungleJme** · 07-26-2012, 05:28 AM

Thanks guys, seems to work on the sample - have put it into my main table and got a couple of erroneous looking results could be the data though, will investigate and report back

---------- Post added at 10:28 AM ---------- Previous post was at 10:21 AM ----------

Yeh, that works great - errors were down to other factors! Thanks gents.