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?

 

[hsandeep]

Both formulas are probably not correct..since not working. Can someone provide me the formula. There is a typo in the line 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. The correct line is From that identified column, get the value from the range S2001:W2015 where the row number matches the row in I2001:I2015 where the value equals the value in cell I67.

 

[Alex Blakenburg]

What version of Excel are you using ? Your profile says 2010 but your first option looks like an array formula to me which makes 2010 unlikely.
I can't see any reason to use your first formula unless there is something I am not understanding.
The 2nd formula looks fine assuming you are only wanting to return a single value.

 

[hsandeep]

What version of Excel are you using ? Your profile says 2010 but your first option looks like an array formula to me which makes 2010 unlikely.
I can't see any reason to use your first formula unless there is something I am not understanding.
The 2nd formula looks fine assuming you are only wanting to return a single value.

Thanks Alex for your time & efforts. I am trying with the below formula:I need your help to check if it is okay?
New formula below: (still checking...is it okay?)

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

Using Excel 2010 version Sir

 

[hsandeep]

What version of Excel are you using ? Your profile says 2010 but your first option looks like an array formula to me which makes 2010 unlikely.
I can't see any reason to use your first formula unless there is something I am not understanding.
The 2nd formula looks fine assuming you are only wanting to return a single value.

Alex:
Last New formula also didn't worked. I am trying
2nd New formula:

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

 

[Alex Blakenburg]

I am missing something.
In the first part of all your formulas, what is the purpose of identifying the column that has a 1 in the heading row if you are always going to return the value in column S ?
Maybe tell me what is in I67 and then give me a picture including Row and Column references with the expected result highlighted.

 

[hsandeep]

I am missing something.
In the first part of all your formulas, what is the purpose of identifying the column that has a 1 in the heading row if you are always going to return the value in column S ?

Very glad to reply Alex,
There are 2 scenarios:
First: Either of a cell in the range S1987:W1987=1 & if true I want to get the corresponding column's value from S2001:W2015 & the row should be identified where the value in I2001:I2015 matches I67.
Second: Either of a cell in the range K1987:R1987=1 & if true I want to get the corresponding column's value from K2001:K2015 & the row should be identified where the value in I2001:I2015 matches I67.
else 0.
Note: Only 1 cell from the range S1988:W1987 will be =1 & only 1 cell from the range K1988:R1987 will be=1. The positioning of the IF in the formula is important.

 

[hsandeep]

Alex:
Last New formula also didn't worked. I am trying
2nd New formula:

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

The 2nd New formula works (but still I have some apprehensions);

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

So I am trying 3rd New Formula:

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

I need help which formula is better to achieve the same result with less complexity, reducing the chance of unforeseen errors?

 

[Alex Blakenburg]

This is your post #1 2nd formula with only 1 change and works as far as I can tell.
> Changed INDEX(K2001:K2015 to INDEX(K2001:R2015

=IFERROR(
          INDEX(S2001:W2015, MATCH(I67, I2001:I2015, 0), MATCH(1, S1987:W1987, 0)),
          IFERROR(
          INDEX(K2001:R2015, MATCH(I67, I2001:I2015, 0), MATCH(1, K1987:R1987, 0)),
          0))

 

[hsandeep]

This is your post #1 2nd formula with only 1 change and works as far as I can tell.
> Changed INDEX(K2001:K2015 to INDEX(K2001:R2015

=IFERROR(
          INDEX(S2001:W2015, MATCH(I67, I2001:I2015, 0), MATCH(1, S1987:W1987, 0)),
          IFERROR(
          INDEX(K2001:R2015, MATCH(I67, I2001:I2015, 0), MATCH(1, K1987:R1987, 0)),
          0))

1 question:
Will changing INDEX(K2001:K2015 to INDEX(K2001:R2015 -->>pull values from column other than K (L, M, N, O, P, Q, R) when 1 is in either of K1987:R1987 in the second IF condition? I want to pull values from column K only (K2001:K2015) when 1 is in either of K1987:R1987 & not from other columns (L, M, N, O, P, Q, R).