Find number in a list and return number after it.

dwrowe001

New Member
Hi everyone,
I have a worksheet where I have a formula looking for target numbers (in B column) from the Master sheet, and then returning the number right above it or just before it. It does this for all the instances of that target number found in the B column of Master list.

Here is screenshot of Sheet1

B column only contains the target number, 1 to 10, no formulas
C column contains formula: COUNTIF(B1Table,B3), copied down C3 to C12. B1table is the B column of Master sheet.
D3 thru XX Columns contain the formula, copied across and down:
IF(\$C3>=COLUMNS(\$F3:F3),SUMPRODUCT(--(LARGE((Master!\$B\$2:Master!\$B\$1999=\$B3)*ROW(Master!\$B\$2:Master!\$B\$1999),\$C3-COLUMNS(\$F3:F3)+1)>ROW(Master!\$B\$2:Master!\$B\$1999)),--(ROW(Master!\$B\$2:Master!\$B\$1999)>=LARGE((Master!\$B\$2:Master!\$B\$1999=\$B3)*ROW(Master!\$B\$2:Master!\$B\$1999),\$C3-COLUMNS(\$F3:F3)+1)-\$B1),Master!\$B\$2:Master!\$B\$1999),"0")

for reference here is what formula in V12 looks like:
=IF(\$C12>=COLUMNS(\$D12:V12),SUMPRODUCT(--(LARGE((Master!\$B\$3:Master!\$B\$2000=\$B12)*ROW(Master!\$B\$3:Master!\$B\$2000),\$C12-COLUMNS(\$D12:V12)+1)>ROW(Master!\$B\$3:Master!\$B\$2000)),--(ROW(Master!\$B\$3:Master!\$B\$2000)>=LARGE((Master!\$B\$3:Master!\$B\$2000=\$B12)*ROW(Master!\$B\$3:Master!\$B\$2000),\$C12-COLUMNS(\$D12:V12)+1)-\$B\$1),Master!\$B\$3:Master!\$B\$2000),"0")

Here a screenshot of Master sheet:

I'm trying to come up with a similar formula, that does the same thing, but instead of returning the number directly above the target number it returns the number directly below it. Make it Sheet2.
I've tried and tried to modify the above formula to get it to do that, but I just mess things up. I can't do it. Can someone help me figure this out.

It would be awesome if you could also explain how the above formula works.. I can't understand how it all works together to accomplish what it does.

Dave

Excel Facts

Test for Multiple Conditions in IF?
Use AND(test, test, test, test) or OR(test, test, test, ...) as the logical_test argument of IF.

Fluff

MrExcel MVP, Moderator
What version of Excel are you using?
Please update you account details to show this, as it affects which functions you can use.

dwrowe001

New Member
What version of Excel are you using?
Please update you account details to show this, as it affects which functions you can use.

Ok, I updated my profile.. think I got everything. I'm using MSOFFICE 2016, Excel 2016. 64bit.

Fluff

MrExcel MVP, Moderator
Ok, thanks for that, how about
Cell Formulas
RangeFormula
D3:T12D3=IFERROR(INDEX(Master!\$B\$1:\$B\$2000,AGGREGATE(15,6,(ROW(Master!\$B\$2:\$B\$2000)-ROW(Master!\$B\$2)+1)/(Master!\$B\$2:\$B\$2000=\$B3),COLUMNS(\$D3:D3))),"")
D15:T24D15=IFERROR(INDEX(Master!\$B\$3:\$B\$2001,AGGREGATE(15,6,(ROW(Master!\$B\$2:\$B\$2000)-ROW(Master!\$B\$2)+1)/(Master!\$B\$2:\$B\$2000=\$B15),COLUMNS(\$D15:D15))),"")

+Fluff New.xlsm
B
1
29
39
410
55
68
710
89
97
103
114
1210
133
143
159
1610
175
186
199
207
213
221
234
249
259
267
274
282
298
307
Master

dwrowe001

New Member
Ok, thanks for that, how about
Cell Formulas
RangeFormula
D3:T12D3=IFERROR(INDEX(Master!\$B\$1:\$B\$2000,AGGREGATE(15,6,(ROW(Master!\$B\$2:\$B\$2000)-ROW(Master!\$B\$2)+1)/(Master!\$B\$2:\$B\$2000=\$B3),COLUMNS(\$D3:D3))),"")
D15:T24D15=IFERROR(INDEX(Master!\$B\$3:\$B\$2001,AGGREGATE(15,6,(ROW(Master!\$B\$2:\$B\$2000)-ROW(Master!\$B\$2)+1)/(Master!\$B\$2:\$B\$2000=\$B15),COLUMNS(\$D15:D15))),"")

+Fluff New.xlsm
B
1
29
39
410
55
68
710
89
97
103
114
1210
133
143
159
1610
175
186
199
207
213
221
234
249
259
267
274
282
298
307
Master

Fluff,
Yes, that works quite nicely... thank you for your help.

Dave

Fluff

MrExcel MVP, Moderator
You're welcome & thanks for the feedback.

Replies
4
Views
110
Replies
1
Views
135
Replies
3
Views
129
Replies
0
Views
96
Replies
7
Views
147