Forum Discussion

behrooz amiri's avatar
behrooz amiri
Copper Contributor
Apr 21, 2018

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

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 :

beh89
beh80
jack75
mat74
dave55
moe43
james38
beh36

 

Sent table :

joe95
beh85
ray80
beh75
dave60
james35
fred30
beh30


the goal is to get result like the bellow :

joe95Error or "0"
beh8589
ray80Error or "0"
beh7580
dave6055
james3538
fred30Error or "0"
beh3036

 
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!

Resources