Looking for formula to extract values in sheet named Product into Outcome Sheet if the 2 date criteria are met. Would also like a formula to extract values in sheet named Product into the sheet named Outcome_II using values extracted into the sheet named Outcome.
See sample file.
Last edited by bjnockle; 06-24-2017 at 10:55 PM.
try Pivot Table
Last edited by sandy666; 06-24-2017 at 09:30 PM. Reason: file updated
Looking for formula and not Pivot.
Thanks.
no problem, good luck
For the 1st part, use this, copied down and across...
=INDEX(Product!$D$4:$O$27,MATCH($A4&" "&$B4&" "&$C4,INDEX(Product!$A$4:$A$27&" "&Product!$B$4:$B$27&" "&Product!$C$4:$C$27,0),0),MATCH(D$3,Product!$D$3:$O$3,0))
1. Use code tags for VBA. [code] Your Code [/code] (or use the # button)
2. If your question is resolved, mark it SOLVED using the thread tools
3. Click on the star if you think someone helped you
Regards
Ford
For the 2nd 1...
=INDEX(Product!$D$4:$O$27,MATCH(C$2&" "&$A4&" "&$B4,INDEX(Product!$A$4:$A$27&" "&Product!$B$4:$B$27&" "&Product!$C$4:$C$27,0),0),MATCH(C$3,Product!$D$3:$O$3,0))
copied down and across
FDibbins: formula not pulling correct values. Noticed that the Year criteria in the Product sheet (B1 and C1) was not used in the formula to extract only 2014 and 2015 values into Outcome sheet. Please check.
Thanks.
FDibbins: for Outcome_II sheet, formula is returning #N/A values. Please check.
Thanks.
BOTH formulae give the correct results i.e. match your manual results, GIVEN that that Columns A,B,C (In OUTCOME)and A, B (in OUTCOME II) are pre-defined.
JohnTopley: Columns A, B, C are not predefined in Outcome and Outcome_II. The values in columns A,B, C in Outcome and Outcome_II are to be extracted in these columns. For the Outcome sheet, the two criteria for the formula to pull year 2014 and 2015 are in B1 and C1 of Product.
Thanks.
For "Outcome"
in A4
=IFERROR(INDEX(Product!B$4:B$27,SMALL(IF((Product!$B$1=Product!$A$4:$A$27)+(Product!$C$1=Product!$A$4:$A$27),ROW(Product!$A$4:$A$27)-ROW($A$4)+1,""),ROWS($A$4:B4))),"")
...confirmed by pressing CTRL+SHIFT+ENTER to activate the array, not just ENTER. You will know the array is active when you see curly braces { } appear around your formula. If you do not CTRL+SHIFT+ENTER you will get an error or a clearly incorrect answer.
copy down
I'll look at "Outcome II" .
Last edited by JohnTopley; 06-25-2017 at 12:56 PM.
In "Column II"
in A4
=IFERROR(INDEX(Product!B$4:B$27,SMALL(IF(Product!$B$1=Product!$A$4:$A$27,ROW(Product!$A$4:$A$27)-ROW($A$4)+1,""),ROWS($A$4:B4))),"")
...confirmed by pressing CTRL+SHIFT+ENTER to activate the array, not just ENTER. You will know the array is active when you see curly braces { } appear around your formula. If you do not CTRL+SHIFT+ENTER you will get an error or a clearly incorrect answer.
Copy across and down
In C3
=DATE(Product!$B$1,COLUMNS($C:C),1)
Copy across: format cells as"mmm"
in C2
=C3
Copy across: format cells as "yyyy"
All your criteria are being used...A4 and B4 and C4 for teh row and D3 for the column...Originally Posted by bjnockle
=INDEX(Product!$D$4:$O$27,MATCH($A4&" "&$B4&" "&$C4,INDEX(Product!$A$4:$A$27&" "&Product!$B$4:$B$27&" "&Product!$C$4:$C$27,0),0),MATCH(D$3,Product!$D$3:$O$3,0))
The results I got where identical to every sample answer you gave. If you enter the formula as I provided, it replicates your answers
Again, my results match exactly what your sample answers are. If your actual data table structure is different to your sample, update the sample please
aahh well you didnt say that in your 1st post, did you?
lol
There are currently 1 users browsing this thread. (0 members and 1 guests)
Posting Permissions
LinkBack URL
About LinkBacks
Register To Reply
Bookmarks