Look up two values in different columns and return matching value

  • Thread starter Thread starter Ash
  • Start date Start date
A

Ash

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
 
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.
 
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.
 
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
 
Thanks, that worked.

--
Ash :)


T. Valko said:
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.
Click to expand...
 
Back
Top