Excel Table lookup

3
votes
       A         B  C   D   E   F   G   H   I   J   K   L   M   N   O   P
1 In Use        |1  0   0   0   0   0   0   0   0   0   0   0   0   0   0|
2 Item Value    |1  0   0   0   0   0   1   1   0   1   1   1   0   0   0|
3 Data          |3  4   5   6   7   8   9   1   2   3   4   5   6   7   8|

I'm looking for an excel function that will compare the values of Rows 1 and 2 to see if they both equal 1. If they are both 1, I would like for it to return the value of the third row (of the corresponding column who has both values equal to 1) to the cell. So in this example, I am looking for the function to return the value 3.

What is the best function to achieve the desired result? I have tried the AND() function like so:

=and(B1:P1=1,B2:P2=1,B3:P3,0)
1
excelexcel-formula
Whether or not there is only 1 occurrence of 1/1 pairs makes a big difference here. – enderland
Yes, for my purposes I am assuming that there will only be one occurrence of a 1/1 pair. – raphnguyen

1 Answers

2
votes

Will this go in each column? Or are you looking for a single cell that sums them all? If you are looking for one formula to check everything, would this work?

=SUMPRODUCT(B1:P1,B2:P2,B3:P3)

Note that this depends on the values being 1 or 0, and that only one pair of (1,1) exists.