Excel Magic Trick 778: INDEX & MATCH Lookup Functions Beginning To Advanced (18 Examples) - YouTube example 6. I have a table similar to this one without the descending numbers as "pipe size". My "pipe sizes" would have to be 0-2, 2.1-4, 4.1-6, 6.1-8, and >8.1. My rainfall would also have to be ranges 0-1000, 1000-2000, 2000-
3000, 3000-4000, and 4000-5000. The corresponding table values are......
[TABLE="width: 330"]
<colgroup><col width="73" style="width: 55pt; mso-width-source: userset; mso-width-alt: 2669;" span="6"> <tbody>[TR]
[TD="width: 73"]
[/TD]
[TD="width: 73, bgcolor: #D9D9D9"]1000[/TD]
[TD="width: 73, bgcolor: #D9D9D9"]2000[/TD]
[TD="width: 73, bgcolor: #D9D9D9"]3000[/TD]
[TD="width: 73, bgcolor: #D9D9D9"]4000
[/TD]
[TD="width: 73"]5000[/TD]
[/TR]
[TR]
[TD="align: right"]2[/TD]
[TD="bgcolor: transparent"]10[/TD]
[TD="bgcolor: transparent"]7[/TD]
[TD="bgcolor: transparent"]5[/TD]
[TD="bgcolor: transparent"]4[/TD]
[TD="bgcolor: transparent"]1[/TD]
[/TR]
[TR]
[TD="align: right"]4[/TD]
[TD]7[/TD]
[TD]5
[/TD]
[TD]4[/TD]
[TD]3[/TD]
[TD]1
[/TD]
[/TR]
[TR]
[TD="align: right"]6[/TD]
[TD="bgcolor: transparent"]5[/TD]
[TD="bgcolor: transparent"]4[/TD]
[TD="bgcolor: transparent"]3
[/TD]
[TD="bgcolor: transparent"]2[/TD]
[TD="bgcolor: transparent"]1[/TD]
[/TR]
[TR]
[TD="align: right"]8[/TD]
[TD]4
[/TD]
[TD]3[/TD]
[TD]2[/TD]
[TD]2[/TD]
[TD]1[/TD]
[/TR]
[TR]
[TD="align: right"]100
[/TD]
[TD="bgcolor: transparent"]1[/TD]
[TD="bgcolor: transparent"]1[/TD]
[TD="bgcolor: transparent"]1[/TD]
[TD="bgcolor: transparent"]1[/TD]
[TD="bgcolor: transparent"]1[/TD]
[/TR]
</tbody>[/TABLE]
What value do I need to put in the 100 spot to show anything greater than 8?
if c51=5 and c52=2650 I would need c53=3 and I'm currently getting 5 with...
=INDEX(Sheet2!$G$5:$K$9,MATCH(Schaurer!C51,Sheet2!$F$5:$F$9,1),MATCH(Schaurer!C52,Sheet2!$G$4:$K$4,1))
3000, 3000-4000, and 4000-5000. The corresponding table values are......
[TABLE="width: 330"]
<colgroup><col width="73" style="width: 55pt; mso-width-source: userset; mso-width-alt: 2669;" span="6"> <tbody>[TR]
[TD="width: 73"]
[/TD]
[TD="width: 73, bgcolor: #D9D9D9"]1000[/TD]
[TD="width: 73, bgcolor: #D9D9D9"]2000[/TD]
[TD="width: 73, bgcolor: #D9D9D9"]3000[/TD]
[TD="width: 73, bgcolor: #D9D9D9"]4000
[/TD]
[TD="width: 73"]5000[/TD]
[/TR]
[TR]
[TD="align: right"]2[/TD]
[TD="bgcolor: transparent"]10[/TD]
[TD="bgcolor: transparent"]7[/TD]
[TD="bgcolor: transparent"]5[/TD]
[TD="bgcolor: transparent"]4[/TD]
[TD="bgcolor: transparent"]1[/TD]
[/TR]
[TR]
[TD="align: right"]4[/TD]
[TD]7[/TD]
[TD]5
[/TD]
[TD]4[/TD]
[TD]3[/TD]
[TD]1
[/TD]
[/TR]
[TR]
[TD="align: right"]6[/TD]
[TD="bgcolor: transparent"]5[/TD]
[TD="bgcolor: transparent"]4[/TD]
[TD="bgcolor: transparent"]3
[/TD]
[TD="bgcolor: transparent"]2[/TD]
[TD="bgcolor: transparent"]1[/TD]
[/TR]
[TR]
[TD="align: right"]8[/TD]
[TD]4
[/TD]
[TD]3[/TD]
[TD]2[/TD]
[TD]2[/TD]
[TD]1[/TD]
[/TR]
[TR]
[TD="align: right"]100
[/TD]
[TD="bgcolor: transparent"]1[/TD]
[TD="bgcolor: transparent"]1[/TD]
[TD="bgcolor: transparent"]1[/TD]
[TD="bgcolor: transparent"]1[/TD]
[TD="bgcolor: transparent"]1[/TD]
[/TR]
</tbody>[/TABLE]
What value do I need to put in the 100 spot to show anything greater than 8?
if c51=5 and c52=2650 I would need c53=3 and I'm currently getting 5 with...
=INDEX(Sheet2!$G$5:$K$9,MATCH(Schaurer!C51,Sheet2!$F$5:$F$9,1),MATCH(Schaurer!C52,Sheet2!$G$4:$K$4,1))