Find number in a list and return number after it.

dwrowe001

New Member
Joined
Mar 12, 2017
Messages
39
Office Version
  1. 2016
Platform
  1. Windows
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

1598714117108.jpeg


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:
1598715236374.jpeg

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.

Thank you so much in advance for your help.

Dave
 

Some videos you may like

Excel Facts

How to total the visible cells?
From the first blank cell below a filtered data set, press Alt+=. Instead of SUM, you will get SUBTOTAL(9,)

Fluff

MrExcel MVP, Moderator
Joined
Jun 12, 2014
Messages
48,448
Office Version
  1. 365
Platform
  1. Windows
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
Joined
Mar 12, 2017
Messages
39
Office Version
  1. 2016
Platform
  1. Windows
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
Joined
Jun 12, 2014
Messages
48,448
Office Version
  1. 365
Platform
  1. Windows
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
Joined
Mar 12, 2017
Messages
39
Office Version
  1. 2016
Platform
  1. Windows
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
Joined
Jun 12, 2014
Messages
48,448
Office Version
  1. 365
Platform
  1. Windows
You're welcome & thanks for the feedback.
 

Watch MrExcel Video

Forum statistics

Threads
1,114,528
Messages
5,548,578
Members
410,853
Latest member
Tramp71
Top