Index, Match Help

dramey1

New Member
Joined
Jun 21, 2017
Messages
22
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.
123456789101112
228801440960720575480410360320290260240
388004400293022001760147012601100980880800730
41840092006130460036803070263023002045184016751530
5346001730011530865069205765494543253845346031452880
654000270001799513500108009000771567506000540049104500
8116000580003866029000232001931516570145001289011600105459600

<colgroup><col span="3"><col><col span="2"><col><col><col><col span="4"></colgroup><tbody>
</tbody>
 
Last edited:

Excel Facts

Lock one reference in a formula
Need 1 part of a formula to always point to the same range? use $ signs: $V$2:$Z$99 will always point to V2:Z99, even after copying
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
ABCDEFGHIJKLM
1123456789101112
2228801440960720575480410360320290260240
3388004400293022001760147012601100980880800730
441840092006130460036803070263023002045184016751530
55346001730011530865069205765494543253845346031452880
6654000270001799513500108009000771567506000540049104500
78116000580003866029000232001931516570145001289011600105459600
8
9
10
11Rainfall Rate3.5Formula
12Square Feet4.36130
Sheet3
Cell Formulas
RangeFormula
D12=INDEX(B2:M7,MATCH(B12,A2:A7),MATCH(B11,B1:M1))
 
Upvote 0
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
 
Upvote 0
ok, in that case try

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

Same layout as previous post.
 
Upvote 0
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.
 
Upvote 0
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
 
Upvote 0
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.
 
Upvote 0
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
ABCDEFGHIJKLM
1123456789101112
28116000580003866029000232001931516570145001289011600105459600
3654000270001799513500108009000771567506000540049104500
45346001730011530865069205765494543253845346031452880
541840092006130460036803070263023002045184016751530
6388004400293022001760147012601100980880800730
7228801440960720575480410360320290260240
8
9
10
113.5
1210003
Sheet1
Cell Formulas
RangeFormula
D12=INDEX(A2:A7,MATCH(B12,INDEX(B2:M7,0,MATCH(CEILING(B11,1),B1:M1,0)),-1))
 
Upvote 0
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))
 
Upvote 0

Forum statistics

Threads
1,216,723
Messages
6,132,320
Members
449,718
Latest member
Marie42719

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top