CODEWITHSUNDEEP
excelvlookup
Sammy Lynn
Sammy Lynn

Reputation: 1

Excel - Looking up a cell in a table and then using if statements to decide which column to use

I am trying to calculate how many standard deviations a particular performance is away from the mean for many different data points.

For example, I have the standard deviation for all kids in a class separated by hair color and then further separated by eye color. Then using each of those as its own group I want to know how many deviations away from the mean any particular height may be.

So in the attached document, I need to write the formula in column F to look up the standard deviation. Essentially, I want to look up the number in column A to the table in column J, then based on that value lookup the height (column E) and see were it lies with in the the table and have it spout out an answer between k2:Q2

    Hair    Eyes    Mean    Height  SD      Standard Deviation                  
1   Blonde  green   62" 61" ??          -2  -1  0   1   2
2   Blonde  brown   65" 60" ??      1   58" 60" 62" 64" 66"
3   Blonde  hazel   66" 63" ??      2   57" 61" 65" 69" 73"
1   Blonde  green   62" 66" ??      3   60" 63" 66" 69" 72"
4   Blonde  blue    67" 62" ??      4   61" 64" 67" 70" 73"
2   Blonde  brown   65" 63" ??      5   51" 56" 61" 66" 71"
5   Brown   brown   61" 70" ??      6   53" 58" 63" 68" 73"
6   Brown   hazel   63" 60" ??      7   66" 68" 70" 72" 74"
7   Brown   green   70" 68" ??      8   58" 59" 60" 61" 62"
6   Brown   hazel   63" 65" ??      9   58" 61" 64" 67" 70"
6   Brown   hazel   63" 62" ??      10  64" 66" 68" 70" 72"
8   Grey    green   60" 59" ??                          
9   Grey    blue    64" 66" ??                          
10  Grey    hazel   68" 67" ??                          
9   Grey    blue    64" 61" ??

Upvotes: 0

Views: 44

Answers (1)

Scott Craner
Scott Craner

Reputation: 152505

IN F2:

=INDEX($H$2:$L$2,MATCH(E2,INDEX($H$3:$L$12,MATCH(A2,$G$3:$G$12,0),0)))

Then copy down.

enter image description here

One note this will throw an error if the height is less than lowest measurement.

Upvotes: 1

Related Questions