Using a formula to locate a value using three columns of Data

  • Thread starter Thread starter Dave
  • Start date Start date
D

Dave

I am attempting to construct a formula that will return a
value from three columns of data. In the following
example, I would be looking for the value in Column C that
corresponds to a match of two variables found in Cols. A &
B. (i.e. Type1, Asset3 = 39) Any ideas on how to construct
a formula to search through a data base to locate a value
based on two matching criteria would be greatly
appreciated.

COL A COL B COL C
Type1 Asset1 50
Type1 Asset2 46
Type1 Asset3 39
Type2 Asset1 65
Type2 Asset2 77
Type2 Asset3 54
Type3 Asset1 55
Type3 Asset2 44
Type3 Asset3 49
 
try where all ranges match in size a1:a100, b1:b100
=sumproduct((rngA="Type 1")*(rngB="Asset 1")*rngC)
 
One way

=INDEX($C$2:$C$200,MATCH(1,($A$2:$A$200=$D$1)*($B$2:$B$200=$E$1),0))

entered with ctrl + shift & enter

where D1 and E1 holds the conditions Type1 and Asset3
 
Thanks to all who responded. Andy's response appears to be
the easiest (similar to Don's). I could not get Peo's to
return a value.

Thanks Again,

Dave
 
Back
Top