Lookup that isn't

[groitblat]

I'm sure that my inexperience in the lookup functions in Excel is the issue but for the life of me I'm stumped.

Have a table of data on 1 worksheet that refers to some performance testing data. As below
I've created a chart to get HR ranges on another sheet within the workbook.
Am trying to then correspond a calculated HR number aka 110 to what MPH that would be in the following data.

I've tried this: =INDEX('Only needed Data'!G:G,MATCH(('Only needed Data'!D),'Only needed Data'!D,0)) but only gives me the 6.5 result
Tried =INDEX(B27,MATCH(('Only needed Data'!D),'Only needed Data'!D,0)) and get REF error

I'm pretty new at the lookup formula's and my google skills trying to find a solution have failed me - any help is appreciated.

-gr


The chart data looks like this:
Zone Lower HR - BPM (Column B on Chart Sheet)
1 107.1
2 117.8
3 121.4
4 125.0
5 132.1

Comparison Data is this on sheet named Only Needed Data Sheet

Column A Column B Column C
VO2 HR mph
1.252884865 114.5 6
1.374572396 123 6
1.705227494 126 6
1.98260963 130 6
2.300889969 133 6
2.342296124 132.5 6
2.327442169 133 6
2.471115112 133 6
2.228504896 132 6.5
2.377532244 132 6.5
2.441828489 131 6.5
2.427397251 132 6.5
2.270006418 133 6.5
2.45892334 134 6.5
2.503205299 135 6.5
2.426150322 135 6.5
2.531698465 136 7
2.464298487 134 7
2.625844955 135 7
2.429574966 136.5 7
2.619473696 139 7
2.577842951 140 7
2.565523624 141.5 7
2.937273741 142 7
2.571757555 142 7.5
2.660637856 142 7.5
2.657461166 143 7.5
2.55642271 142 7.5
2.936694145 144 7.5
2.756564856 146 7.5
2.945664167 147 7.5
2.795013189 149 7.5

 

[zelgar]

Based upon the data you have available, you'll not be able to get any value for HR values less than 114.5 using any INDEX, MATCH or VLOOKUP command. Also, looking at your data there is no trend with your HR values and the mph.

mph    HR Range
 6     114.5 - 133
 6.5   131 - 135
 7     134 - 142
 7.5   142 - 149

For example, if the HR is 133, you can get a mph of 6 OR 6.5.

 

[groitblat]

Yes starting to see some issues with the ranges....think I got Vlookup working as suggested

Thanks

- gina

 

[lionelhill]

You might not want MATCH with zero as its final parameter. When you are looking up numerical data, sorted in order, "0" will force MATCH to return only an exact match, but you might find you've got data for 135 and 136, and you're looking up 135.5. In this case, you can use -1 or 1 to return either the value for 135 or 136 (nearest best estimates above and below the missing "true" value).

Your data are nearly, but not quite, in numerical order...