Suggest me out of 2 formulas which is more reliable

[hsandeep]

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:

=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)

Second:

=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?

 

[Alex Blakenburg]

Seems a bit of a strange requirement but if in the 2nd part you only want to return the value from Column K regardless of which column contains the 1 then try this version:
(added the >0 to the column match in blue)

=IFERROR(
          INDEX(S2001:W2015, MATCH(B1988, I2001:I2015, 0), MATCH(1, S1987:W1987, 0)),
          IFERROR(
          INDEX(K2001:K2015, MATCH(B1988, I2001:I2015, 0), MATCH(1, K1987:R1987, 0)>0),
          0))

 

[hsandeep]

Seems a bit of a strange requirement but if in the 2nd part you only want to return the value from Column K regardless of which column contains the 1 then try this version:
(added the >0 to the column match in blue)

=IFERROR(
          INDEX(S2001:W2015, MATCH(B1988, I2001:I2015, 0), MATCH(1, S1987:W1987, 0)),
          IFERROR(
          INDEX(K2001:K2015, MATCH(B1988, I2001:I2015, 0), MATCH(1, K1987:R1987, 0)>0),
          0))

I successfully got the correct formula

=IF(COUNTIF(S1987:W1987, 1) = 1, INDEX(S2001:W2015, MATCH(I67, I2001:I2015, 0), MATCH(1, S1987:W1987, 0)), IF(COUNTIF(K1987:R1987, 1) = 1, INDEX(K2001:K2015, MATCH(I67, I2001:I2015, 0)), 0))

Thanks a lot Alex for your time & efforts.

 

[Alex Blakenburg]

No problem. Glad you found something that worked for you.