Excel - INDEX Function Problem - Expert Solution

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
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
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
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
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
User 08/05/2018 - 07:36
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.

Get instant expert help with Excel and Google Sheets

Post your problem and you’ll get expert help in seconds.

Your message must be at least 40 characters
Your privacy is guaranteed. Your session will not be used for blog unless you give us persmission.

Click here to get your free Excelchat help session

Subscribe to Excelchat.co
Trusted by people who work at
Amazon.com, Inc
Facebook, Inc
Accenture PLC
Siemens AG
The Allstate Corporation
United Parcel Service
Dell Inc