Suggest me out of 2 formulas which is more reliable

H

hsandeep

Well-known Member
Joined
Dec 6, 2008
Messages
1,226
Office Version
  1. 2010
Platform
  1. Windows
  2. 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:
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)
Second:
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?
 

Excel Facts

How to total the visible cells?
From the first blank cell below a filtered data set, press Alt+=. Instead of SUM, you will get SUBTOTAL(9,)
Sort by date Sort by votes
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.
 
Upvote 0
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.
 
Upvote 0
Alex Blakenburg said:
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.
Click to expand...
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?)
Excel Formula: 
=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
 
Upvote 0
Alex Blakenburg said:
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.
Click to expand...
Alex:
Last New formula also didn't worked. I am trying
2nd New formula:
Excel 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))
 
Upvote 0
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.
 
Upvote 0
Alex Blakenburg said:
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 ?
Click to expand...
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.
 
Upvote 0
hsandeep said:
Alex:
Last New formula also didn't worked. I am trying
2nd New formula:
Excel 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))
Click to expand...
The 2nd New formula works (but still I have some apprehensions);
Excel 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))

So I am trying 3rd New Formula:
Excel 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?
 
Upvote 0
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
Excel Formula: 
=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))
 
Upvote 0
Alex Blakenburg said:
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
Excel Formula: 
=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))
Click to expand...
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).
 
Upvote 0
You must log in or register to reply here.

Similar threads

H
  • Question Question
To fill 2 target ranges matching certain conditions
Replies
3
Views
269
hsandeep
H
M
  • Solved
Dynamically Plot Data Points in Scatterplot
Replies
5
Views
548
felixstraube
F
A
  • Question Question
Dynamically add new rows via VBA when using IFERROR TEXTJOIN and FILTER formulas from Table?
Replies
0
Views
517
AlfredDB
A
T
  • Question Question
Lookup within a lookup
2
Replies
10
Views
764
TimeForTim
T
T
  • Solved
MATCH function
2
Replies
14
Views
776
Fluff
Fluff

Forum statistics

Threads
1,223,885
Messages
6,175,184
Members
452,615
Latest member
bogeys2birdies

Share this page

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top