Hi,
I'm new here, and I really need your help.
Scenario: I already got the value on the Row (B), is it possible to get also the value on the Row (C) after getting all the value on the Row (B)
This is the formula I used: =IF(ISERROR(INDEX($A:$C,SMALL(IF($A:$A=$F$1,ROW($A:$A)),ROW(1:1)),2)),"",INDEX($A:$C,SMALL(IF($A:$A=$F$1,ROW($A:$A)),ROW(1:1)),2))
Thank you in advance
Screenshot 2016-02-10 at 15.03.20.png
Try
F2=IFERROR(INDEX($A$1:$C$2500,SMALL(IF($A$1:$A$2500=$F$1,ROW($A$1:$A$2500)-ROW($A$1)+1),ROWS($A$1:A1)),2),"") with CRTL+SHIFT+ENTER
A:A is not good with array formula
A1:A2500 or according your ranges is good with array formula
If I helped, Don't forget to add reputation (click on the little star ★ at bottom of this post)
Don't forget to mark threads as "Solved" (Thread Tools->Mark thread as Solved)
Another Without CTRL+SHIFt+ENTER
F2=IFERROR(INDEX($A$1:$C$2500,AGGREGATE(15,6,(ROW($A$1:$A$2500)-ROW($A$1)+1)/($A$1:$A$2500=$F$1),ROWS($A$1:A1)),2),"")
Hi Shukla,
I already tried the formula that you gave but the result is still the same as above.
Is it possible that after getting all the "below expectation" of Row B, F6 would start to search on the "below expectation" of Row C.
Screenshot 2016-02-10 at 16.27.32.png
By the way thanks for answering my question.
OK
try
F2=IFERROR(INDEX($A$1:$C$2500,AGGREGATE(15,6,(ROW($A$1:$A$2500)-ROW($A$1)+1)/($A$1:$A$2500=$F$1),ROWS($A$1:A1)),2),INDEX($A$1:$C$2500,AGGREGATE(15,6,(ROW($A$1:$A$2500)-ROW($A$1)+1)/($A$1:$A$2500=$F$1),ROWS($A$1:A1)),3)) without CRTL+SHIFt+ENTER
or
F2=IFERROR(IFERROR(INDEX($A$1:$C$2500,SMALL(IF($A$1:$A$2500=$F$1,ROW($A$1:$A$2500)-ROW($A$1)+1),ROWS($A$1:A1)),2),INDEX($A$1:$C$2500,SMALL(IF($A$1:$A$2500=$F$1,ROW($A$1:$A$2500)-ROW($A$1)+1),ROWS($A$1:A1)),3)),"") With CRTL+SHIFT+ENTER.
Already tried it.
F2 without CRTL+SHIFt+ENTER is error.
F2 with CRTL+SHIFT+ENTER is good but still don't have value on F6.
In F2 copy paste below then hold control and shift together and then hit enter to make it array formulaFormula:
Please Login or Register to view this content.
array formulas are...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.
-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
WANT TO SAY THANKS, HIT ADD REPUTATION (*) AT THE BOTTOM LEFT CORNER OF THE POST
More we learn about excel, more it shows us, how less we know about it.
for chemistry
https://www.youtube.com/c/chemistrybyshivaansh
Hi Hemesh,
Thank you so much. It works.
@ jrahm, You are welcome !
you may use
below array entered
Formula:Please Login or Register to view this content.
TryOriginally Posted by jrahm23
F2=IFERROR(IFERROR(INDEX($A$1:$C$11,AGGREGATE(15,6,(ROW($A$1:$A$11)-ROW($A$1)+1)/($A$1:$A$11=$F$1),ROWS($A$1:A1)),2),INDEX($A$1:$C$11,AGGREGATE(15,6,(ROW($A$1:$A$11)-ROW($A$1)+1)/($A$1:$A$11=$F$1),ROWS($A$1:A1)-COUNTIFS($A$2:$A$11,$F$1)),3)),"") and rag down
Thanks for the help Hemesh and Shukla.
Hi Hemesh,
Can you help me again on this,
I need to get the Result value of "Below Expectation" on a monthly basis.
I used this formula but I think there's wrong on the formula.
=ArrayFormula(IFERROR(IFERROR(IFERROR(IFERROR(IFERROR(INDEX($A$2:$A$100,SMALL(IF($B$2:$B$100=$I$1,ROW($B$2:$B$100)-ROW($B$2)+1),ROWS($B$1:B1))),INDEX($A$2:$A$100,SMALL(IF($C$2:$C$100=$I$1,ROW($C$2:$C$100)-ROW($B$2)+1),ROWS($C$1:C1)-SUMPRODUCT(--($C$2:$C$100=$I$1))))),INDEX($A$2:$A$100,SMALL(IF($D$2:$D$100=$I$1,ROW($D$2:$D$100)-ROW($B$2)+1),ROWS($D$1:D1)-SUMPRODUCT(--($D$2:$D$100=$I$1)*2)))),INDEX($A$2:$A$100,SMALL(IF($E$2:$E$100=$I$1,ROW($E$2:$E$100)-ROW($B$2)+1),ROWS($E$1:E1)-SUMPRODUCT(--($E$2:$E$100=$I$1)*3)))),INDEX($A$2:$A$100,SMALL(IF($F$2:$F$100=$I$1,ROW($F$2:$F$100)-ROW($B$2)+1),ROWS($F$1:F1)-SUMPRODUCT(--($F$2:$F$100=$I$1)*4)))),""))
Screenshot 2016-02-12 at 12.01.14.png
Your set up of sheet now is different as from Post #1
Last edited by hemesh; 02-12-2016 at 05:31 AM.
find attached, I have created a helper row shown in yellow you can either change the font colour to white or you can hide that row
Hi Hemesh,
Thank you so much. :D
You are welcome and thanks for the feedback
Hi Hemesh,
Base on the excel file you sent, is there also a way that I can get the corresponding month of every result?
Like this.
Screenshot 2016-02-16 at 08.34.44.png
try below in J2 in the sheet I uploaded last
copy paste below then hold control and shift together and then hit enter to make it array formula
Formula:Please Login or Register to view this content.
drag down
according to your screen shot it should be P2 with control shift and enter
Formula:Please Login or Register to view this content.
There are currently 1 users browsing this thread. (0 members and 1 guests)
Posting Permissions
LinkBack URL
About LinkBacks
Register To Reply
Bookmarks