Index Match using nearest values

I have two lists of data that i need to compare with each other, the first has an ID and a time e.g

ID Time
A 09:37
B 10:26
A 11:15
C 12:20
etc

the second set has 3 values

ID Time Value
A 09:30 10
B 10:15 11
C 11:18 12
A 11:05 13
etc

What I need to do is match the ID and time from the first set of data to the ID and the nearest time from the second set and return the value from the second set.

Is this possible?

If your first table, including headers, is in A1:B5 and your second table, including headers, is in A8:C12 try this formula in C2 copied down

=INDEX(C$9:C$12,MATCH(1,(A2=A$9:A$12)* (ABS(B$9:B$12-B2)=MIN(IF(A2=A$9:A$12,ABS(B$9:B$12-B2)))),0))

confirmed with CTRL+SHIFT+ENTER

Originally Posted by NBVC

What exactly do you mean by "nearest"? Does it have to be greater than or equal to, less than or equal to?

It has to be less than or equal to

Originally Posted by Nebuchanezer

It has to be less than or equal to

That's not the same as "nearest". Can you give expected results for the example you gave?

Originally Posted by daddylonglegs

That's not the same as "nearest". Can you give expected results for the example you gave?

OK, using the same set of data

ID Time
A 09:37
B 10:26
A 11:15
C 12:20
etc


ID Time Value
A 09:30 10
B 10:15 11
C 11:18 12
A 11:05 13

The answer i'd like to get is

ID Time Value
A 09:37 10
B 10:26 11
A 11:15 13
C 12:20 12

Hope this make it clearer, thanks for your help.

I've never been one to limit myself to a single cell for a given formula if it got too complicated.
The biggest complication I see in your scenario is that you have one lookup table for multiple ID's. If it were me, the first thing I would do would be to separate out each ID, so you have one ID per lookup table. Easiest way off the top of my head would to autofilter on ID, then copy and paste. Once each lookup table is created, then the problem becomes a simple VLOOKUP().

You just need a way to select the lookup table based on ID. If it were me, I'd put each lookup table in it's own tab, with the ID in the name ("Alook","Blook" or similar). Then you can use the INDIRECT function to choose the lookup table [INDIRECT(A1&"look!A1:c100")

Hope that helps. If you really want a single cell solution, be patient and I expect someone will figure something out.

Adjusting my original suggestion......you could use this formula

=INDEX(C$9:C$12,MATCH(1,(A2=A$9:A$12)*(B2-B$9:B$12=MIN(IF(A2=A$9:A$12,IF(B2>B$9:B$12,B2-B$9:B$12)))),0))

confirmed with CTRL+SHIFT+ENTER

That seems to work great

Thanks a lot for your help