Lookup or calculate nearest bigger value of table items where items are not unique.

[behrooz amiri]

Dear All Excel MASTERS, HLEP!

Let's say I have an excel file which have 2 sheets : "Inbox" and "Sent"
in those sheets, there are 2 columns : "Name" and "SCORE"
what I am trying to do, is to always get the nearest bigger "Score" (minimum value which is bigger) for any "Name" in the "Inbox" table, from the "Sent" Table. Please think of sheets bellow :

Inbox Table :

beh	89
beh	80
jack	75
mat	74
dave	55
moe	43
james	38
beh	36

Sent table :

joe	95
beh	85
ray	80
beh	75
dave	60
james	35
fred	30
beh	30

the goal is to get result like the bellow :

joe	95	Error or "0"
beh	85	89
ray	80	Error or "0"
beh	75	80
dave	60	55
james	35	38
fred	30	Error or "0"
beh	30	36

 
I tried many formulas for this, as you can see in bellow screenshot, after hours! I managed to find out I NEED HELP! then if you Excel masters help me solve this, I really would appreciate it.

the closest formula I have used is as bellow :

{=IF(Inbox!A2=Sent!$A$1:$A$8,MIN(IF(Sent!$B$1:$B$8>Inbox!B2,Sent!$B$1:$B$8)))}

screenshot, lot's of try! no luck. only the first two columns are data! others are my failures!

[#2 · Accepted solution]

Hi Behrooz,

Please try this formula:

=IF(ISNUMBER(MATCH(A2,Sent!$A$2:$A$9,0)),
  SUM(IF(MIN(ABS(B2-IF(A2=Sent!$A$2:$A$9,Sent!$B$2:$B$9)))=ABS(B2-IF(A2=Sent!$A$2:$A$9,Sent!$B$2:$B$9)),Sent!$B$2:$B$9)),0)

And find it in the attached file.

I hope this helps you

Haytham

[#3]

Thank you for your help..
Actually Your Post is the correct answer for the question, so I mark your post as the "Best Answer".
but, to make it easier for you  guys to help me, I simplified the question, and now this caused an issue.
so I'm going to add a new thread for the real issue, and I think you can help..
then kindly PLEASE take a look at my new post with the link bellow:

Advanced: Formula to calculate elapsed time from 2 given time, based on a string which is not unique

Regards.