excel magic trick 778, example 6

[tpuck2687]

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......

	1000	2000	3000	4000	5000
2	10	7	5	4	1
4	7	5	4	3	1
6	5	4	3	2	1
8	4	3	2	2	1
100	1	1	1	1	1

<colgroup><col width="73" style="width: 55pt; mso-width-source: userset; mso-width-alt: 2669;" span="6"> <tbody> </tbody>

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))

 

[Drrellik]

in post number 6 of this thread a comma got replaced with a / (Schaurer!C52/1000) did you cut and paste this and is this problem??

 

[tpuck2687]

I did just copy and paste to start nd changed it up a little but when I tried to replace / with , it said there are to many arguments. this is what I have now =INDEX(Sheet2!$G$5:$K$9,MATCH($C$51,LEFT(Sheet2!$F$5:$F$9,1)*1,1),MATCH($C$52/1000,LEFT(Sheet2!$G$4:$K$4,1)*1,1))

 

[Aladin Akyurek]

That's exactly what I needed! Some people just don't see the ranges unless I mark them accordingly, so I had to put them in. Thanks for the help!!

More convenient...

		0	1000	2000	3000	4000
		1000	2000	3000	4000	5000			1
0	2	10	7	5	4	1			1000
2	4	7	5	4	3	1			10
4	6	5	4	3	2	1
6	8	4	3	2	2	1
8	1.00E+308	1	1	1	1	1

<COLGROUP><COL style="WIDTH: 48pt" width=64><COL style="WIDTH: 81pt; mso-width-source: userset; mso-width-alt: 3840" width=108><COL style="WIDTH: 48pt" span=8 width=64><TBODY>
</TBODY>

J4:

=INDEX($C$3:$G$7,MATCH($J2,$A$3:$A$7,1),MATCH(MAX(J$3,1000),$C$2:$G$2,1))

 

[tpuck2687]

when you put in an 8 and 1169 you still get a 1 for the answer and it should be a 3 so I'm not sure that's working either.

 

[Aladin Akyurek]

when you put in an 8 and 1169 you still get a 1 for the answer and it should be a 3 so I'm not sure that's working either.

All of the values which are associated with 8 are 1.

 

[tpuck2687]

All of the values which are associated with 8 are 1.

It should be any values over 8 are 1. The ranges should 0-2 2.1-4 4.1-6 etc

 

[Aladin Akyurek]

It should be any values over 8 are 1. The ranges should 0-2 2.1-4 4.1-6 etc

Would you take a look at the exhibit in #13 and say what should be retrieved for the combination of 8 and 1169?

Note. 1.00E+308 is the display foem of a big number that is: 9.99999999999999E+307. It is used here to express the interval 8 and >8.

 

[tpuck2687]

I would want 8(≤8) to be on the second to last line and anything over 8(>8) to be the last line. That would be the same for all numbers. 2 should be on the first line not the second

 

[Aladin Akyurek]

I would want 8(≤8) to be on the second to last line and anything over 8(>8) to be the last line. That would be the same for all numbers. 2 should be on the first line not the second

Would you check

=INDEX(C$3:$G$7,
  MATCH(J2,$A$3:$A$7,1)-(LOOKUP(J$2,$A$3:$A$7)=J$2),
  MATCH(J$3,$C$1:$G$1,1))

whether it behaves as intended regarding column headers?

 

[tpuck2687]

that seems to be working for the first column numbers 2,4,6 and 8 now I would need the first row to do the same with 1000,2000,3000 and 4000