Table lookup with criteria between range of numbers

[xbakx]

Hi everyone. I am making a fitness testing spreadsheet that puts you into different levels (1-5) depending on your performance. The tables below show Resting Heart Rate with separate tables for Male and Female. The gender, age, and Resting Heart Rate results are in another sheet (shown below).

I want to create a formula that outputs the level you are based on those criteria. I've been playing around with INDEX, MATCH, and VLOOKUP, but I'm not very advanced in Excel and don't know how to deal with the ranges for age and resting heart rate.




<style><!--table {mso-displayed-decimal-separator:"\."; mso-displayed-thousand-separator:"\,";}@page {margin:.75in .7in .75in .7in; mso-header-margin:.3in; mso-footer-margin:.3in;}td {padding-top:1px; padding-right:1px; padding-left:1px; mso-ignoreadding; color:black; font-size:12.0pt; font-weight:400; font-style:normal; text-decoration:none; font-family:Calibri, sans-serif; mso-font-charset:0; mso-number-format:General; text-align:general; vertical-align:bottom; border:none; mso-background-source:auto; mso-pattern:auto; mso-protection:locked visible; white-space:nowrap; mso-rotate:0;}.xl63 {font-size:10.0pt; font-family:Arial, sans-serif; mso-font-charset:0;}.xl64 {color:black; font-weight:700; font-family:Arial, sans-serif; mso-font-charset:0; text-align:center; border:.5pt solid windowtext;}.xl65 {font-size:10.0pt; font-family:Arial, sans-serif; mso-font-charset:0; border:.5pt solid windowtext;}.xl66 {color:black; font-size:10.0pt; font-weight:700; font-family:Arial, sans-serif; mso-font-charset:0; border:.5pt solid windowtext;}.xl67 {color:black; font-size:10.0pt; font-family:Arial, sans-serif; mso-font-charset:0; border:.5pt solid windowtext;}.xl68 {color:black; font-weight:700; font-family:Arial, sans-serif; mso-font-charset:0; text-align:center; border-top:.5pt solid windowtext; border-right:none; border-bottom:.5pt solid windowtext; border-left:.5pt solid windowtext;}.xl69 {color:black; font-weight:700; font-family:Arial, sans-serif; mso-font-charset:0; text-align:center; border-top:.5pt solid windowtext; border-right:none; border-bottom:.5pt solid windowtext; border-left:none;}.xl70 {color:black; font-weight:700; font-family:Arial, sans-serif; mso-font-charset:0; text-align:center; border-top:.5pt solid windowtext; border-right:.5pt solid windowtext; border-bottom:.5pt solid windowtext; border-left:none;}--></style>

Thank you much!

 

[crazydragon84]

a table of data and table of what you want your output to look like might help...

 

[xbakx]

Sorry, you're right.

The data comes in like this:

<a href="https://ibb.co/huzv8w"><img src="https://preview.ibb.co/h2htvb/Screen_Shot_2017_09_29_at_2_28_53_PM.png" alt="Screen_Shot_2017_09_29_at_2_28_53_PM" border="0"></a>

And all I want is a simple table that outputs the level as a number. Something like this:

<a href="https://imgbb.com/"><img src="https://image.ibb.co/heooTw/Screen_Shot_2017_09_29_at_2_35_41_PM.png" alt="Screen_Shot_2017_09_29_at_2_35_41_PM" border="0"></a>

Does that help?