**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.*