Forum Discussion
behrooz amiri
Apr 21, 2018Copper Contributor
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 :
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!
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
- Haytham AmairahSilver Contributor
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
- behrooz amiriCopper Contributor
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.