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 :)

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 :)

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 :)

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 :)

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 :)

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 :)