Index, Match Help

[dramey1]

hello, I'm struggling coming up with a formula to give me a value with 2 given starting conditions. I will paste my table in here after I explain it. So, the top row of data is a given number but not exact, and the various rows of numbers below the top row is also given, but not exact. The top row is rainfall rates, and the bulk of numbers is square feet. I need an equation that is able to find the correct column based off of the rainfall rate, and the correct row based off of the square feet, and then return the diameter needed from the left most column. The square feet will almost never be exact, and I need the equation to find the value that is greater than or equal to the given square feet. An equation or vba code will work fine with me. Thanks in advance.
[TABLE="width: 880"]
<colgroup><col span="3"><col><col span="2"><col><col><col><col span="4"></colgroup><tbody>[TR]
[TD][/TD]
[TD]1[/TD]
[TD]2[/TD]
[TD]3[/TD]
[TD]4[/TD]
[TD]5[/TD]
[TD]6[/TD]
[TD]7[/TD]
[TD]8[/TD]
[TD]9[/TD]
[TD]10[/TD]
[TD]11[/TD]
[TD]12[/TD]
[/TR]
[TR]
[TD]2[/TD]
[TD]2880[/TD]
[TD]1440[/TD]
[TD]960[/TD]
[TD]720[/TD]
[TD]575[/TD]
[TD]480[/TD]
[TD]410[/TD]
[TD]360[/TD]
[TD]320[/TD]
[TD]290[/TD]
[TD]260[/TD]
[TD]240[/TD]
[/TR]
[TR]
[TD]3[/TD]
[TD]8800[/TD]
[TD]4400[/TD]
[TD]2930[/TD]
[TD]2200[/TD]
[TD]1760[/TD]
[TD]1470[/TD]
[TD]1260[/TD]
[TD]1100[/TD]
[TD]980[/TD]
[TD]880[/TD]
[TD]800[/TD]
[TD]730[/TD]
[/TR]
[TR]
[TD]4[/TD]
[TD]18400[/TD]
[TD]9200[/TD]
[TD]6130[/TD]
[TD]4600[/TD]
[TD]3680[/TD]
[TD]3070[/TD]
[TD]2630[/TD]
[TD]2300[/TD]
[TD]2045[/TD]
[TD]1840[/TD]
[TD]1675[/TD]
[TD]1530[/TD]
[/TR]
[TR]
[TD]5[/TD]
[TD]34600[/TD]
[TD]17300[/TD]
[TD]11530[/TD]
[TD]8650[/TD]
[TD]6920[/TD]
[TD]5765[/TD]
[TD]4945[/TD]
[TD]4325[/TD]
[TD]3845[/TD]
[TD]3460[/TD]
[TD]3145[/TD]
[TD]2880[/TD]
[/TR]
[TR]
[TD]6[/TD]
[TD]54000[/TD]
[TD]27000[/TD]
[TD]17995[/TD]
[TD]13500[/TD]
[TD]10800[/TD]
[TD]9000[/TD]
[TD]7715[/TD]
[TD]6750[/TD]
[TD]6000[/TD]
[TD]5400[/TD]
[TD]4910[/TD]
[TD]4500[/TD]
[/TR]
[TR]
[TD]8[/TD]
[TD]116000[/TD]
[TD]58000[/TD]
[TD]38660[/TD]
[TD]29000[/TD]
[TD]23200[/TD]
[TD]19315[/TD]
[TD]16570[/TD]
[TD]14500[/TD]
[TD]12890[/TD]
[TD]11600[/TD]
[TD]10545[/TD]
[TD]9600[/TD]
[/TR]
</tbody>[/TABLE]

 

[Jonmo1]

Something like this

=INDEX(B2:M7,MATCH(B12,A2:A7),MATCH(B11,B1:M1))

B11 is the given rainfall rate
B12 is the given square feet

Unknown
	A	B	C	D	E	F	G	H	I	J	K	L	M
1		1	2	3	4	5	6	7	8	9	10	11	12
2	2	2880	1440	960	720	575	480	410	360	320	290	260	240
3	3	8800	4400	2930	2200	1760	1470	1260	1100	980	880	800	730
4	4	18400	9200	6130	4600	3680	3070	2630	2300	2045	1840	1675	1530
5	5	34600	17300	11530	8650	6920	5765	4945	4325	3845	3460	3145	2880
6	6	54000	27000	17995	13500	10800	9000	7715	6750	6000	5400	4910	4500
7	8	116000	58000	38660	29000	23200	19315	16570	14500	12890	11600	10545	9600
8
9
10
11	Rainfall Rate	3.5		Formula
12	Square Feet	4.3		6130
Sheet3

Cell Formulas
Range	Formula
D12	=INDEX(B2:M7,MATCH(B12,A2:A7),MATCH(B11,B1:M1))

 

[dramey1]

Sorry, I wasn't very clear about what the square feet is. The square feet will be in the thousands. The square feet refers to the numbers under the rainfall rates. The numbers in the left column are what I need the equation to return (the diameter) which will be between 2 and 8.
rainfall rates- between 0-12
square feet- main data (in the thousands)
diameter- value that should be returned, 2- 8
Thanks

 

[Jonmo1]

ok, in that case try

=INDEX(A2:A7,MATCH(B12,INDEX(B2:M7,0,MATCH(B11,B1:M1))))

Same layout as previous post.

 

[dramey1]

No, that doesn't work. It returns a diameter, just not the correct one. I put in 1000 square feet with a rainfall rate of 3.5, and it returned a 2 inch diameter when it should be 6. I had a very similar formula to the one gave and had the same problem.

 

[Jonmo1]

Why should it be 6 ?

The 3.5 rainfall corresponds to column D of your table.

Column D has
960 2930 6130 11530 17995 38660

1000 in that table seems closest matched to 960
960 corresponds to 2 in column A

 

[dramey1]

The logic behind this is: At a rainfall rate of 3.5 (which should be rounded up to 4 in the equation) would put us in column E. 720 2200 4600 8650 13500 29000. That means that a diameter of 2 can handle a max of 720 sq. feet, diameter of 3 can handle a max of 2200 sq. feet, and so on.
I miss spoke in my previous post, the correct answer would be 3 because 1000 sq. ft is larger than 720 and that means that a 2 in diameter cannot handle that large of an area, so a 3 in diameter is needed.

 

[Jonmo1]

Ok, so the 3.5 should be rounded up...

And within the column you need the next higher value.
Can you reverse the sort of those columns, so column E goes
29000 13500 86500 4600 2200 720

Then you can do
=INDEX(A2:A7,MATCH(B12,INDEX(B2:M7,0,MATCH(CEILING(B11,1),B1:M1,0)),-1))

Unknown
	A	B	C	D	E	F	G	H	I	J	K	L	M
1		1	2	3	4	5	6	7	8	9	10	11	12
2	8	116000	58000	38660	29000	23200	19315	16570	14500	12890	11600	10545	9600
3	6	54000	27000	17995	13500	10800	9000	7715	6750	6000	5400	4910	4500
4	5	34600	17300	11530	8650	6920	5765	4945	4325	3845	3460	3145	2880
5	4	18400	9200	6130	4600	3680	3070	2630	2300	2045	1840	1675	1530
6	3	8800	4400	2930	2200	1760	1470	1260	1100	980	880	800	730
7	2	2880	1440	960	720	575	480	410	360	320	290	260	240
8
9
10
11		3.5
12		1000		3
Sheet1

Cell Formulas
Range	Formula
D12	=INDEX(A2:A7,MATCH(B12,INDEX(B2:M7,0,MATCH(CEILING(B11,1),B1:M1,0)),-1))

 

[dramey1]

Yes!! Thank you, that works perfectly.

 

[Jonmo1]

You're welcome.

Since you're open to sorting, if you reverse sort the rows as well, Row 1 is 12 11 10 9 8 etc.

Then you can do this slightly more efficiently, without the ceiling and exact match..

=INDEX(A2:A7,MATCH(B12,INDEX(B2:M7,0,MATCH(B11,B1:M1,-1)),-1))