Finding One Value, Matching Three Criteria

C

cattle mgr

This is the table that has the values that we are looking up:

Gain 1.6 1.6 1.8 1.8 2.0 2.0
Sex S H S H S H
Body Weight
400 17.4 19.2 18.4 20.5 19.5 21.8
410 17.7 19.6 18.8 20.9 19.9 22.2
420 18.0 19.9 19.1 21.3 20.2 22.6
430 18.3 20.3 19.4 21.6 20.6 23.0
440 18.7 20.6 19.8 22.0 20.9 23.4
450 19.0 21.0 20.1 22.4 21.3 23.8


This the area of the spreadsheet that contains the criteria to matc
for the lookup:

Sex Gain Body Weight Value ?
H 1.8 430 ?
S 1.6 450 ?

Can someone tell me how to write a formula that will find the value i
the table when "sex", "gain", and "body weight" match
 
D

Domenic

Assumptions:

B1:G1 contains the 'Gain'

B2:G2 contains the 'Sex"

A4:A9 contains the 'Body Weight'

B4:G9 contains the data


Formula:

=INDEX($B$4:$G$9,MATCH(C15,$A$4:$A$9,0),MATCH(B15,$B$1:$G$1,0)+(A15="H"))

....where C15 contains the 'Body Weight' of interest, B15 contains the
'Gain' of interest, and A15 contains the 'Sex' of interest.

Hope this helps!
 
C

cattle mgr

Domenic

:) Thank you - it worked like a charm!!! This will save me a lot of
time from having to look up these values from my feed tables and insert
them manually. Again, thanks for your help.

cattle mgr
 
Top