Question description:
This user has given permission to use the problem statement for this
blog.
could someone breakdown this formula for me :D =ArrayFormula(IF(D$5>=ROWS(B$5:B5),INDEX(Sheet1!C$6:C$21,SMALL(IF(Sheet1!D$6:D$21=$E$5,ROW(Sheet1!C$6:C$21)-ROW(Sheet1!C$6)+1),ROWS(B$5:B5))),""))
Solved by C. C. in 40 mins
This is the chat thread from the real Excelchat help session. It contains no private user
information.
User
08/05/2018 - 07:28
hey
Excelchat Expert
08/05/2018 - 07:28
Hello there, thanks for choosing Got It Pro- Excel.
Excelchat Expert
08/05/2018 - 07:29
Can you please attach here your excel document ?
User
08/05/2018 - 07:29
Yea sure
User
08/05/2018 - 07:30
https://docs.google.com/spreadsheets/d/1TX5ux447Aa-L2WOjs5cBZUjDoG6Ok5bZJvzok-w6da4/edit?usp=sharing
User
08/05/2018 - 07:30
its a doc sorry
User
08/05/2018 - 07:30
i mean spreadsheet
Excelchat Expert
08/05/2018 - 07:31
I need a permission to open the spreadsheet.
User
08/05/2018 - 07:31
kk
User
08/05/2018 - 07:32
sorry i cant i will just quickly recreate it using the provided spreadsheet
Excelchat Expert
08/05/2018 - 07:33
Okay, can you explain what do you want to achieve ?
User
08/05/2018 - 07:34
I wish to understand this formula
User
08/05/2018 - 07:34
i am recreating the situation the applying the formula to give you the context
User
08/05/2018 - 07:35
=ArrayFormula(IF(D$5>=ROWS(B$5:B5),INDEX(Sheet1!C$6:C$21,SMALL(IF(Sheet1!D$6:D$21=$E$5,ROW(Sheet1!C$6:C$21)-ROW(Sheet1!C$6)+1),ROWS(B$5:B5))),""))
Excelchat Expert
08/05/2018 - 07:36
You want this formula to understand?=ArrayFormula(IF(D$5>=ROWS(B$5:B5),INDEX(Sheet1!C$6:C$21,SMALL(IF(Sheet1!D$6:D$21=$E$5,ROW(Sheet1!C$6:C$21)-ROW(Sheet1!C$6)+1),ROWS(B$5:B5))),""))
User
08/05/2018 - 07:36
yes
User
08/05/2018 - 07:36
please
User
08/05/2018 - 07:36
ok look at sheet 2
User
08/05/2018 - 07:36
each name
User
08/05/2018 - 07:36
is made by that formula
User
08/05/2018 - 07:36
as you can see
User
08/05/2018 - 07:37
The part i do not understand about the formula is the usage of SMALL() and the +1 being applied to the ROW()
User
08/05/2018 - 07:38
as well as the ROW()-ROW()
Excelchat Expert
08/05/2018 - 07:43
So using Small function in a formula it looks up a list and finds the smallest value in the array.
User
08/05/2018 - 07:44
Ok, now how does that affect this formula in particular?
Excelchat Expert
08/05/2018 - 07:47
So in this ArrayFormula we are looking if D3 match in Rows from B3 to B5, and index with C3:C21, and we search for small value in D3:D21 range if there match with E3 value.
User
08/05/2018 - 07:49
ok, what would return as a result of that using just the SMALL formula?
Excelchat Expert
08/05/2018 - 07:50
But since we don;t have you excel document we can;t now what it should result, because formula include some rows and cells what we don;t have the values.
User
08/05/2018 - 07:52
last question
User
08/05/2018 - 07:52
could you breakdown the ROW()-ROW()+1
Excelchat Expert
08/05/2018 - 07:55
Row C3 :c21 means that we have to check from C3:C21 and then - Row c3 cell
Excelchat Expert
08/05/2018 - 07:56
Adjust the range referenced to suit your needs.
User
08/05/2018 - 07:57
Ok what about the +1 at the end
Excelchat Expert
08/05/2018 - 07:58
ROW() returns 1 inside the formula in C3..
Excelchat Expert
08/05/2018 - 08:00
So if we have for example : (ROW()-5), In this case, the first instance of the formula is in cell D6 so, ROW() returns 6 inside the formula in D6, We want to start with 1, however, so we need to subtract 5, which yields 1.
Excelchat Expert
08/05/2018 - 08:01
Are you there ?
Excelchat Expert
08/05/2018 - 08:05
The time is running out for us.
Excelchat Expert
08/05/2018 - 08:05
Thanks for choosing Got It Pro - Excel.
Excelchat Expert
08/05/2018 - 08:05
Please give your kind feedback for our service.
Excelchat Expert
08/05/2018 - 08:05
Bye, have a good day ahead.
This is the output file from the real Excelchat help session:
This is an example of the expert help you can get. It contains no private user
information.
