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.
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.
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.
1. Use code tags for VBA. [code] Your Code [/code] (or use the # button)
2. If your question is resolved, mark it SOLVED using the thread tools
3. Click on the star if you think someone helped you
Regards
Ford
There are currently 1 users browsing this thread. (0 members and 1 guests)
Posting Permissions
LinkBack URL
About LinkBacks
Register To Reply
Bookmarks