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:

[TABLE="width: 500, align: center"]
<tbody>[TR]
[TD="align: center"][/TD]
[TD="align: center"]<1%[/TD]
[TD="align: center"]1%[/TD]
[TD="align: center"]3%[/TD]
[TD="align: center"]5%[/TD]
[TD="align: center"]10%[/TD]
[TD="align: center"]25%[/TD]
[TD="align: center"]30%[/TD]
[TD="align: center"]50%[/TD]
[TD="align: center"]75%[/TD]
[/TR]
[TR]
[TD="align: center"]0[/TD]
[TD="align: center"]30.5[/TD]
[TD="align: center"]31.5[/TD]
[TD="align: center"]32.5[/TD]
[TD="align: center"]33.5[/TD]
[TD="align: center"]34.5[/TD]
[TD="align: center"]35.5[/TD]
[TD="align: center"]36[/TD]
[TD="align: center"]36.5[/TD]
[TD="align: center"]37[/TD]
[/TR]
[TR]
[TD="align: center"]1[/TD]
[TD="align: center"]31.5[/TD]
[TD="align: center"]32.5[/TD]
[TD="align: center"]33.5[/TD]
[TD="align: center"]34.5[/TD]
[TD="align: center"]35.5[/TD]
[TD="align: center"]36.5[/TD]
[TD="align: center"]36.75[/TD]
[TD="align: center"]37.25[/TD]
[TD="align: center"]38[/TD]
[/TR]
[TR]
[TD="align: center"]2[/TD]
[TD="align: center"]31.75[/TD]
[TD="align: center"]32[/TD]
[TD="align: center"]32.25[/TD]
[TD="align: center"]33[/TD]
[TD="align: center"]33.5[/TD]
[TD="align: center"]34[/TD]
[TD="align: center"]36[/TD]
[TD="align: center"]36.2[/TD]
[TD="align: center"]39[/TD]
[/TR]
[TR]
[TD="align: center"]3[/TD]
[TD="align: center"]32[/TD]
[TD="align: center"]32.25[/TD]
[TD="align: center"]34[/TD]
[TD="align: center"]35[/TD]
[TD="align: center"]37[/TD]
[TD="align: center"]38[/TD]
[TD="align: center"]39.2[/TD]
[TD="align: center"]40[/TD]
[TD="align: center"]41[/TD]
[/TR]
[TR]
[TD="align: center"]4[/TD]
[TD="align: center"]33[/TD]
[TD="align: center"]33.5[/TD]
[TD="align: center"]36[/TD]
[TD="align: center"]36.5[/TD]
[TD="align: center"]37[/TD]
[TD="align: center"]38[/TD]
[TD="align: center"]39[/TD]
[TD="align: center"]42[/TD]
[TD="align: center"]43[/TD]
[/TR]
</tbody>[/TABLE]

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%

[TABLE="width: 500"]
<tbody>[TR]
[TD="align: center"]Age[/TD]
[TD="align: center"]Height[/TD]
[TD="align: center"]What I want returned (answer)[/TD]
[/TR]
[TR]
[TD="align: center"]1[/TD]
[TD="align: center"]32.5[/TD]
[TD="align: center"]1%[/TD]
[/TR]
[TR]
[TD="align: center"]3[/TD]
[TD="align: center"]38[/TD]
[TD="align: center"]25%[/TD]
[/TR]
</tbody>[/TABLE]

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

[TABLE="width: 500"]
<tbody>[TR]
[TD="align: center"]Age[/TD]
[TD="align: center"]Height[/TD]
[TD="align: center"]What I want returned (answer)[/TD]
[/TR]
[TR]
[TD="align: center"]1[/TD]
[TD="align: center"]34[/TD]
[TD="align: center"]3%[/TD]
[/TR]
[TR]
[TD="align: center"]4[/TD]
[TD="align: center"]39.5[/TD]
[TD="align: center"]30%[/TD]
[/TR]
</tbody>[/TABLE]

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)