Excel - using Index and Match - adjusting the Match function's array input

[JLoewen]

This is a complicated question to ask and I am not sure exactly how to ask it. I am attempting to make a pipe sizing tool to speed up one of my work processes. The procedure is pretty straight forward. Do some math to figure out GPM going to drain (done elsewhere) and then look up the pipe size required for the pipe slope specified. We use the table below (Plumbing Code).

I need to have the spreadsheet pick the appropriate column for pipe slope (typically 1/8" per foot), and then use the MATCH -1 function to pick the first row that is greater than the required GPM capacity calculated elsewhere in the spreadsheet. From there, it needs to index and pick out the pipe size.

I am not sure how to tell MATCH to pick a specific array to match to based on a dynamic input (pipe slope). Any suggestions?

[TABLE="width: 562"]
<colgroup><col span="3"><col><col span="3"></colgroup><tbody>[TR]
[TD][/TD]
[TD="colspan: 2"]Table 1106.2[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD="colspan: 3"]Storm Drain Pipe Sizing[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD]1[/TD]
[TD]2[/TD]
[TD]3[/TD]
[TD]4[/TD]
[TD]5[/TD]
[/TR]
[TR]
[TD][/TD]
[TD="colspan: 6, align: center"]Capacity (GPM)[/TD]
[/TR]
[TR]
[TD][/TD]
[TD]Pipe Size (inches)[/TD]
[TD][/TD]
[TD="colspan: 4"]Slope of Horizontal Drain[/TD]
[/TR]
[TR]
[TD][/TD]
[TD]Vertical[/TD]
[TD]1/16" per foot[/TD]
[TD]1/8" per foot[/TD]
[TD]1/4" per foot[/TD]
[TD]1/2" per foot[/TD]
[/TR]
[TR]
[TD="align: center"]9[/TD]
[TD="align: center"]15"[/TD]
[TD="align: center"]5543[/TD]
[TD="align: center"]2508[/TD]
[TD="align: center"]3546[/TD]
[TD="align: center"]5016[/TD]
[TD="align: center"]7093[/TD]
[/TR]
[TR]
[TD="align: center"]8[/TD]
[TD="align: center"]12"[/TD]
[TD="align: center"]3272[/TD]
[TD="align: center"]1480[/TD]
[TD="align: center"]2093[/TD]
[TD="align: center"]2960[/TD]
[TD="align: center"]4187[/TD]
[/TR]
[TR]
[TD="align: center"]7[/TD]
[TD="align: center"]10"[/TD]
[TD="align: center"]2050[/TD]
[TD="align: center"]927[/TD]
[TD="align: center"]1311[/TD]
[TD="align: center"]1855[/TD]
[TD="align: center"]2623[/TD]
[/TR]
[TR]
[TD="align: center"]6[/TD]
[TD="align: center"]8"[/TD]
[TD="align: center"]1117[/TD]
[TD="align: center"]505[/TD]
[TD="align: center"]714[/TD]
[TD="align: center"]1010[/TD]
[TD="align: center"]1429[/TD]
[/TR]
[TR]
[TD="align: center"]5[/TD]
[TD="align: center"]6"[/TD]
[TD="align: center"]538[/TD]
[TD="align: center"]243[/TD]
[TD="align: center"]344[/TD]
[TD="align: center"]487[/TD]
[TD="align: center"]689[/TD]
[/TR]
[TR]
[TD="align: center"]4[/TD]
[TD="align: center"]5"[/TD]
[TD="align: center"]311[/TD]
[TD="align: center"]117[/TD]
[TD="align: center"]165[/TD]
[TD="align: center"]234[/TD]
[TD="align: center"]331[/TD]
[/TR]
[TR]
[TD="align: center"]3[/TD]
[TD="align: center"]4"[/TD]
[TD="align: center"]180[/TD]
[TD="align: center"]81[/TD]
[TD="align: center"]115[/TD]
[TD="align: center"]163[/TD]
[TD="align: center"]231[/TD]
[/TR]
[TR]
[TD="align: center"]2[/TD]
[TD="align: center"]3"[/TD]
[TD="align: center"]87[/TD]
[TD="align: center"]39[/TD]
[TD="align: center"]55[/TD]
[TD="align: center"]79[/TD]
[TD="align: center"]111[/TD]
[/TR]
[TR]
[TD="align: center"]1[/TD]
[TD="align: center"]2"[/TD]
[TD="align: center"]34[/TD]
[TD="align: center"]15[/TD]
[TD="align: center"]22[/TD]
[TD="align: center"]31[/TD]
[TD="align: center"]44[/TD]
[/TR]
</tbody>[/TABLE]

 

[Fluff]

Something like

Excel 2013/2016
	A	B	C	D	E	F	G	H	I
1		Pipe Size (inches)	Slope of Horizontal Drain
2		Vertical	1/16" per foot	1/8" per foot	1/4" per foot	1/2" per foot		Slope	1/8" per foot
3	9	15"	5543	2508	3546	5016	7093	GPM	45
4	8	12"	3272	1480	2093	2960	4187	Pipe	4"
5	7	10"	2050	927	1311	1855	2623
6	6	8"	1117	505	714	1010	1429
7	5	6"	538	243	344	487	689
8	4	5"	311	117	165	234	331
9	3	4"	180	81	115	163	231
10	2	3"	87	39	55	79	111
11	1	2"	34	15	22	31	44
Data

Cell Formulas
Range	Formula
I4	=INDEX(B3:B11,MATCH(I3,INDEX(C3:G11,0,MATCH(I2,C2:F2,0)),-1))

 

[JLoewen]

That worked, though I am not sure why.

Thank you for the help.

 

[Fluff]

You're welcome & thanks for the feedback