Lookup/ Index/ Match question

[GinChiarello]

Hi -- I am trying to write a formula that will return to me a % based on a given age and height.

Starting in cell D4 I have this:

	<1%	1%	3%	5%	10%	25%	30%	50%	75%
0	30.5	31.5	32.5	33.5	34.5	35.5	36	36.5	37
1	31.5	32.5	33.5	34.5	35.5	36.5	36.75	37.25	38
2	31.75	32	32.25	33	33.5	34	36	36.2	39
3	32	32.25	34	35	37	38	39.2	40	41
4	33	33.5	36	36.5	37	38	39	42	43

<tbody>
</tbody>

First -- if I have a 1 year old that is 32.5 inches tall -- how do I write a formula to return the % ---> the answer is 1%

Age	Height	What I want returned (answer)
1	32.5	1%
3	38	25%

<tbody>
</tbody>

Second -- if is possible to write a formula where the height might not hit an exact height number listed, so instead it returns the lower % --> for a 1 year old that is 34.0 inches tall ---> the answer would be 3% (since 34.5% is 5% and 33.5% is 3%)

Age	Height	What I want returned (answer)
1	34	3%
4	39.5	30%

<tbody>
</tbody>

Thank you in advance,
Ginny

 

[Aladin Akyurek]

Question 1.

Let A1:J6 on Sheet1 house the data including column and row headers.

Sheet2

C2, just enter and copy down:

=LOOKUP($B2,INDEX(Sheet1!$B$2:$J$6,MATCH($A2,Sheet1!$A$2:$A$6,0),0),Sheet1!$B$1:$J$1)