# Understanding this formula...

1. ## Understanding this formula...

Good morning,

I recently gained the responsibility of an excel spreadsheet, which is used to track the training progress of individual sailors. However, my excel knowledge is basic if not non-existent. I'm having a hard time understanding what this particular formula is doing...

Screen Shot 2018-10-04 at 11.20.14 PM.png

If anyone can help break down what each formula is doing to the overall formula I'd much appreciate it. Thank you in advance.  Register To Reply

2. ## Re: Understanding this formula...

Hello and welcome to the forum.

That formula is a way to return multiple matches (and can actually be shortened if you wanted to).

The SMALL(IF(...(ROW(...)))) portion of the formula returns the first row number (because ROW(A1)=1) where the criteria, Personnel_ACTC_Level="SS4 (100)", is TRUE.
Then when you drag the formula down a row, it returns the second row number (because ROW(A2)=2) where that criteria is TRUE.
etc.

The INDEX function then looks at Personnel_Names and returns the matching value given the row number from above.  Register To Reply

3. ## Re: Understanding this formula...

Hi, welcome to the forum 1. Also note that that is an ARRAY formula, ...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.

2. Not sure if you looked at the pic you provided, but my tired old eyes can barely read anything, it is so small. To be of more help, upload a small (clean) sample workbook (not a pic) of what you are working with, and what your expected outcome would look like.  Register To Reply