Home |
Search |
Today's Posts |
#1
|
|||
|
|||
Finding One Value, Matching Three Criteria
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 match 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 in the table when "sex", "gain", and "body weight" match? -- cattle mgr ------------------------------------------------------------------------ cattle mgr's Profile: http://www.excelforum.com/member.php...o&userid=26718 View this thread: http://www.excelforum.com/showthread...hreadid=400052 |
#2
|
|||
|
|||
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! In article , cattle mgr wrote: 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 match 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 in the table when "sex", "gain", and "body weight" match? |
#3
|
|||
|
|||
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 -- cattle mgr ------------------------------------------------------------------------ cattle mgr's Profile: http://www.excelforum.com/member.php...o&userid=26718 View this thread: http://www.excelforum.com/showthread...hreadid=400052 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
finding matching cells in worksheets | Excel Worksheet Functions | |||
Find largest alphanumeric value matching alpha criteria in databas | Excel Worksheet Functions | |||
Extract multiple records matching criteria from list | Excel Worksheet Functions | |||
Finding the cell reference of a matching search value | Excel Worksheet Functions | |||
Finding a record using multiple combo boxes as my search criteria | Excel Discussion (Misc queries) |