Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
Look up two values in different columns and return matching value
I am trying to create a lookup formula that looks up a value in one column
and then looks up a second value in the second column and returns the value in the third column corresponding to the two values. Eg, 1 3 45 1 4 56 2 2 34 2 5 67 Look up 1 in column 1 then 3 in column 2 and return 45 Please help -- Ash :) |
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
Look up two values in different columns and return matching value
Hi,
This will work provided the combination is unique. But if it is not you will need to tell us what you want, that is if there are two items that match the criteria: =SUMPRODUCT((A1:A4=E1)*(B1:B4=F1)*C1:C4) Where E1 contains the first item you want to look up and F1 the second. -- If this helps, please click the Yes button. Cheers, Shane Devenshire "Ash" wrote: I am trying to create a lookup formula that looks up a value in one column and then looks up a second value in the second column and returns the value in the third column corresponding to the two values. Eg, 1 3 45 1 4 56 2 2 34 2 5 67 Look up 1 in column 1 then 3 in column 2 and return 45 Please help -- Ash :) |
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
Look up two values in different columns and return matching value
One way...
E1 = 1 F1 = 3 =SUMPRODUCT(--(A1:A4=E1),--(B1:B4=F1),C1:C4) This will only work if the value to be returned in a number. This array formula** will work with any data type: =INDEX(C1:C4,MATCH(1,(A1:A4=E1)*(B1:B4=F1),0)) ** array formulas need to be entered using the key combination of CTRL,SHIFT,ENTER (not just ENTER). Hold down both the CTRL key and the SHIFT key then hit ENTER. -- Biff Microsoft Excel MVP "Ash" (donotspam) wrote in message ... I am trying to create a lookup formula that looks up a value in one column and then looks up a second value in the second column and returns the value in the third column corresponding to the two values. Eg, 1 3 45 1 4 56 2 2 34 2 5 67 Look up 1 in column 1 then 3 in column 2 and return 45 Please help -- Ash :) |
#4
Posted to microsoft.public.excel.misc
|
|||
|
|||
Look up two values in different columns and return matching va
Thanks, but I am not wanting to multiply the numbers together, its a LOOKUP
formula I am after. Here is the actual problem: Input cells: Eye height A1 Object Height A2 Constant (C) Formula required Look up table: Eye Height Object Height Constant (C) 1.05 0 230 1.05 0.15 461 1.05 0.4 581 1.05 0.6 682 1.05 1.05 920 2.4 0.6 1200 2.4 1.05 1500 Say you input 1.05 into A1 and 0.6 into A2, i need a look up formula that will look up 1.05 in the eye height column and 0.6 in the object height column and return the corresponding Constant (C), in this case 682 out of the lookup table. Hope this is a clearer description of the question. Thanks -- Ash :) "Shane Devenshire" wrote: Hi, This will work provided the combination is unique. But if it is not you will need to tell us what you want, that is if there are two items that match the criteria: =SUMPRODUCT((A1:A4=E1)*(B1:B4=F1)*C1:C4) Where E1 contains the first item you want to look up and F1 the second. -- If this helps, please click the Yes button. Cheers, Shane Devenshire "Ash" wrote: I am trying to create a lookup formula that looks up a value in one column and then looks up a second value in the second column and returns the value in the third column corresponding to the two values. Eg, 1 3 45 1 4 56 2 2 34 2 5 67 Look up 1 in column 1 then 3 in column 2 and return 45 Please help -- Ash :) |
#5
Posted to microsoft.public.excel.misc
|
|||
|
|||
Look up two values in different columns and return matching va
Thanks, that worked.
-- Ash :) "T. Valko" wrote: One way... E1 = 1 F1 = 3 =SUMPRODUCT(--(A1:A4=E1),--(B1:B4=F1),C1:C4) This will only work if the value to be returned in a number. This array formula** will work with any data type: =INDEX(C1:C4,MATCH(1,(A1:A4=E1)*(B1:B4=F1),0)) ** array formulas need to be entered using the key combination of CTRL,SHIFT,ENTER (not just ENTER). Hold down both the CTRL key and the SHIFT key then hit ENTER. -- Biff Microsoft Excel MVP "Ash" (donotspam) wrote in message ... I am trying to create a lookup formula that looks up a value in one column and then looks up a second value in the second column and returns the value in the third column corresponding to the two values. Eg, 1 3 45 1 4 56 2 2 34 2 5 67 Look up 1 in column 1 then 3 in column 2 and return 45 Please help -- Ash :) |
#6
Posted to microsoft.public.excel.misc
|
|||
|
|||
Look up two values in different columns and return matching va
You're welcome. Thanks for the feedback!
-- Biff Microsoft Excel MVP "Ash" (donotspam) wrote in message ... Thanks, that worked. -- Ash :) "T. Valko" wrote: One way... E1 = 1 F1 = 3 =SUMPRODUCT(--(A1:A4=E1),--(B1:B4=F1),C1:C4) This will only work if the value to be returned in a number. This array formula** will work with any data type: =INDEX(C1:C4,MATCH(1,(A1:A4=E1)*(B1:B4=F1),0)) ** array formulas need to be entered using the key combination of CTRL,SHIFT,ENTER (not just ENTER). Hold down both the CTRL key and the SHIFT key then hit ENTER. -- Biff Microsoft Excel MVP "Ash" (donotspam) wrote in message ... I am trying to create a lookup formula that looks up a value in one column and then looks up a second value in the second column and returns the value in the third column corresponding to the two values. Eg, 1 3 45 1 4 56 2 2 34 2 5 67 Look up 1 in column 1 then 3 in column 2 and return 45 Please help -- Ash :) |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Lookup 3 matching values of which one is a range and return a sum | Excel Worksheet Functions | |||
return values from an array based on matching text value | Excel Worksheet Functions | |||
Return across Row Numeric Values Matching EXACT Month & Year for Criteria | Excel Worksheet Functions | |||
Return Numeric Values Matching EXACT Date for Criteria | Excel Worksheet Functions | |||
Return all matching values | Excel Worksheet Functions |