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

[888ta8pa]

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

<tbody>
</tbody>

Sent table :

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

<tbody>
</tbody>

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

<tbody>
</tbody>

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!

 

[markmzz]

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

<tbody>
</tbody>

Hi!

Maybe the formula below can helps.

Array Formula - use Ctrl+Shift+Enter to enter the formula

In C2 and copy down

=IFERROR(IF(1/VLOOKUP(A2,InBox!A$2:B$9,2,0),
1/(1/LARGE(INDEX((InBox!A$2:A$9=A2)*InBox!B$2:B$9,),COUNTIF(A$2:A2,A2)))),"Error or 0")


[TABLE="class: grid, width: 490"]
<tbody>[TR]
[TD][/TD]
[TD="align: center"]A[/TD]
[TD="align: center"]B[/TD]
[TD="align: center"]C[/TD]
[TD="align: center"]D[/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"]A[/TD]
[TD="align: center"]B[/TD]
[TD="align: center"]C[/TD]
[/TR]
[TR]
[TD="align: center"]1[/TD]
[TD="align: center"]Name[/TD]
[TD="align: center"]Score[/TD]
[TD="align: center"]Result[/TD]
[TD="align: center"]Sent[/TD]
[TD="align: center"][/TD]
[TD="align: center"]1[/TD]
[TD="align: center"]Name[/TD]
[TD="align: center"]Score[/TD]
[TD="align: center"]InBox[/TD]
[/TR]
[TR]
[TD="align: center"]2[/TD]
[TD="align: center"]joe[/TD]
[TD="align: center"]95[/TD]
[TD="align: center"]Error or 0[/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"]2[/TD]
[TD="align: center"]beh[/TD]
[TD="align: center"]89[/TD]
[TD="align: center"][/TD]
[/TR]
[TR]
[TD="align: center"]3[/TD]
[TD="align: center"]beh[/TD]
[TD="align: center"]85[/TD]
[TD="align: center"]89[/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"]3[/TD]
[TD="align: center"]beh[/TD]
[TD="align: center"]80[/TD]
[TD="align: center"][/TD]
[/TR]
[TR]
[TD="align: center"]4[/TD]
[TD="align: center"]ray[/TD]
[TD="align: center"]80[/TD]
[TD="align: center"]Error or 0[/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"]4[/TD]
[TD="align: center"]jack[/TD]
[TD="align: center"]75[/TD]
[TD="align: center"][/TD]
[/TR]
[TR]
[TD="align: center"]5[/TD]
[TD="align: center"]beh[/TD]
[TD="align: center"]75[/TD]
[TD="align: center"]80[/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"]5[/TD]
[TD="align: center"]mat[/TD]
[TD="align: center"]74[/TD]
[TD="align: center"][/TD]
[/TR]
[TR]
[TD="align: center"]6[/TD]
[TD="align: center"]dave[/TD]
[TD="align: center"]60[/TD]
[TD="align: center"]55[/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"]6[/TD]
[TD="align: center"]dave[/TD]
[TD="align: center"]55[/TD]
[TD="align: center"][/TD]
[/TR]
[TR]
[TD="align: center"]7[/TD]
[TD="align: center"]james[/TD]
[TD="align: center"]35[/TD]
[TD="align: center"]38[/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"]7[/TD]
[TD="align: center"]moe[/TD]
[TD="align: center"]43[/TD]
[TD="align: center"][/TD]
[/TR]
[TR]
[TD="align: center"]8[/TD]
[TD="align: center"]fred[/TD]
[TD="align: center"]30[/TD]
[TD="align: center"]Error or 0[/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"]8[/TD]
[TD="align: center"]james[/TD]
[TD="align: center"]38[/TD]
[TD="align: center"][/TD]
[/TR]
[TR]
[TD="align: center"]9[/TD]
[TD="align: center"]beh[/TD]
[TD="align: center"]30[/TD]
[TD="align: center"]36[/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"]9[/TD]
[TD="align: center"]beh[/TD]
[TD="align: center"]36[/TD]
[TD="align: center"][/TD]
[/TR]
[TR]
[TD="align: center"]10[/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"]10[/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[/TR]
[TR]
[TD="align: center"]***[/TD]
[TD="align: center"]********[/TD]
[TD="align: center"]********[/TD]
[TD="align: center"]********[/TD]
[TD="align: center"]******[/TD]
[TD="align: center"]**[/TD]
[TD="align: center"]***[/TD]
[TD="align: center"]********[/TD]
[TD="align: center"]********[/TD]
[TD="align: center"]******[/TD]
[/TR]
</tbody>[/TABLE]


Markmzz

 

[Aladin Akyurek]

@ 888ta8pa

Your goal specification implies a formula in the Sent sheet. If so,

In C2 of Sent, control+shift+enter, not just enter, and copy down:

=IF(COUNTIFS(Inbox!$A$2:$A$9,$A2,Inbox!$B$2:$B$9,">"&$B2),MIN(IF(Inbox!$A$2:$A$9=$A2,IF(Inbox!$B$2:$B$9>$B2,Inbox!$B$2:$B$9))),"not available")

 

[markmzz]

Hi!

A small modification (use only Enter to enter the formula). In C2 and copy down

=IFERROR(IF(1/VLOOKUP(A2,InBox!A$2:B$9,2,0),
INDEX(1/(1/LARGE((InBox!A$2:A$9=A2)*InBox!B$2:B$9,COUNTIF(A$2:A2,A2))),)),"Error or 0")

Markmzz

 

[markmzz]

Hi!

Another small modification (Array Formula - use Ctrl+Shift+Enter to enter the formula). In C2 and copy down.

=IFERROR(IF(1/VLOOKUP(A2,InBox!A$2:B$9,2,0),
1/(1/LARGE((InBox!A$2:A$9=A2)*InBox!B$2:B$9,COUNTIF(A$2:A2,A2)))),"Error or 0")

Markmzz

 

[markmzz]

Hi!

One more (Array Formula - use Ctrl+Shift+Enter to enter the formula). In C2 and copy down.

=IFERROR(IF(1/VLOOKUP(A2,InBox!A$2:B$9,2,0),
1/(1/LARGE((InBox!A$2:A$9=A2)*(InBox!B$2:B$9>=B2)*InBox!B$2:B$9,COUNTIF(A$2:A2,A2)))),"Error or 0")

Markmzz

 

[markmzz]

Hi 888ta8pa,

I have one question for you: What you want for dave and for last record of beh? And why?

Markmzz​