IF formula to calculate smallest difference between given number and multiple possibilities

[dancer5]

I've gotten a bit stuck trying to figure out how to calculate the smallest difference between a given value and multiple others.

I have a dataset with rates and need to find out what the smallest difference is between that rate and previously known rates for that event.
The number of previously known rates will vary, so I tried using filter to get the array of all previously known rates for a specific event, but I don't know how to combine that with an IF to get the smallest difference. Since I'm not looking for MIN or MAX values, but the closest larger value to my rate to then use to get a difference.

Right now I have this formula in G2:
=IF(C2<>1, MIN(IF(E2:F7>D2, E2:F7))-D2, "")
But this doesn't dynamically change the E:F array to only look up those with the corresponding site in column A. I need a way that will find the correct values in E:F when A:A=A2 for G2, A:A=A8 for G8 etc.

 

[6StringJazzer]

First you said

the smallest difference between a given value and multiple others.

But then

the closest larger value to my rate

Let's be really precise here--are you looking for the lowest previous rate that is higher than your rate (that is, closest rate without going under your rate), then return the difference?

Please put actual data in a post so I don't have to type all that in for testing. Thanks.

 

[dancer5]

Sorry, I'm actually looking for both. I've added a gen diff column for the smallest difference regardless if greater or less than. Here is the sample data.

site	event	avail	rate	prev known rate	first known rate	difference	gen diff
1	1	1	508	296	320
1	2	1	195	296
1	3	0	195	522
1	4	0	517	496
1				508
1				545
2	1	0	458	366	366
2	2	0	405	351
2	3	1	397	370
2	4	1	366	351
2	5	0	382	370
2				441
2