hsandeep
Well-known Member
- Joined
- Dec 6, 2008
- Messages
- 1,217
- Office Version
- 2010
- Platform
- Windows
- Mobile
I needed a formula with the following logic:
Check if any of the cells in the range S1987:W1987 contains the value 1.
If true, identify the corresponding column (S, T, U, V, or W) that contains the value 1.
From that identified column, get the value from the range S2001:2015 where the row number matches the row in I2001:I2015 where the value equals the value in cell I67.
If none of the cells in S1987:W1987 contains 1, check if any of the cells in the range K1987:R1987 contains the value 1.
If true, identify the corresponding column (K, L, M, N, O, P, Q, or R) that contains the value 1.
From that identified column, get the value from the range K2001:K2015 where the row number matches the row in I2001:I2015 where the value equals the value in cell I67.
If neither of the above conditions are met, return 0.
I have 2 formulas for this:
First:
Second:
My query is: Which formula should I use based on reliability, functionality & to meet any 'unforeseen circumstances' leading to unwanted results?
Check if any of the cells in the range S1987:W1987 contains the value 1.
If true, identify the corresponding column (S, T, U, V, or W) that contains the value 1.
From that identified column, get the value from the range S2001:2015 where the row number matches the row in I2001:I2015 where the value equals the value in cell I67.
If none of the cells in S1987:W1987 contains 1, check if any of the cells in the range K1987:R1987 contains the value 1.
If true, identify the corresponding column (K, L, M, N, O, P, Q, or R) that contains the value 1.
From that identified column, get the value from the range K2001:K2015 where the row number matches the row in I2001:I2015 where the value equals the value in cell I67.
If neither of the above conditions are met, return 0.
I have 2 formulas for this:
First:
Excel Formula:
=IFERROR(IF(SUMPRODUCT((S1987:W1987=1)*(I2001:I2015=I67)*(S2001:W2015)), INDEX((S2001:W2015)*(I2001:I2015=I67), MATCH(1, (S1987:W1987=1)*(I2001:I2015=I67), 0)), IF(SUMPRODUCT((K1987:R1987=1)*(I2001:I2015=I67)*(K2001:K2015)), INDEX((K2001:K2015)*(I2001:I2015=I67), MATCH(1, (K1987:R1987=1)*(I2001:I2015=I67), 0)), 0)), 0)
Excel Formula:
=IFERROR(INDEX(S2001:W2015, MATCH(I67, I2001:I2015, 0), MATCH(1, S1987:W1987, 0)), IFERROR(INDEX(K2001:K2015, MATCH(I67, I2001:I2015, 0), MATCH(1, K1987:R1987, 0)), 0))My query is: Which formula should I use based on reliability, functionality & to meet any 'unforeseen circumstances' leading to unwanted results?
