lookup pair of values

  • Thread starter Thread starter Allison Faris
  • Start date Start date
A

Allison Faris

I have a fairly simple problem. I have a three column
array and want to retrieve the value from the third column
that corresponds to a pair of values, e.g. if my array is:
1,1,1
1,2,3
1,3,4
2,1,3
2,2,4
2,3,5
3,1,4
3,2,5
3,3,6
and I want to retrieve the value in the third column
corresponding to the pair (2,3), I would get 5.
Any ideas on how to achieve this? Thanks.
 
As long as the values in Column C are numbers

=SUMPRODUCT((A1:A9=2)*(B1:B9=3)*(C1:C9))

Alan Beban
 
I put your three columns in A1:C20.

I put the two values to match in D1 and E1.

then I used this array formula:

=INDEX($C$1:$C$20,MATCH(D1&CHAR(1)&E1,$A$1:$A$20&CHAR(1)&$B1:$B$20,0))

This is an array formula. Hit ctrl-shift-enter instead of enter. If you do it
correctly, excel will wrap curly brackets {} around your formula. (don't type
them yourself.)

the char(1) stuff makes it so if you have:

11,2,3 in one row and
1,12,3 in another

xl can tell the difference: 11-2 or 1-12 (where - represents the char(1))
 
Back
Top