CODEWITHSUNDEEP
excelexcel-formula
raphnguyen
raphnguyen

Reputation: 3605

Excel Table lookup

       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)

Upvotes: 3

Views: 243

Answers (1)

RocketDonkey
RocketDonkey

Reputation: 37279

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.

Upvotes: 2

Related Questions