Formula to Extract Specific Group of Data from Main Worksheet

November 26th, 2012 by BenneTan BenneTan

Someone emailed us a question on this:

“As an item is keyed on the Master sheet under specific criteria it will automatically enter on the other sheet.  In my case soccer is divided into divisions(U6, U8, U10 etc).  If a player is entered as a U6 player I want the columns data to go to the U6 sheet.  I have included the sheet I am working on.  Maybe you can help?”

Here’s a link to the file:

NLSA Database

And the solution to key inside the “U6″ cell A2 is an array formula:

=INDEX(‘Master List’!A$2:A$28,SMALL(IF(‘Master List’!$E$2:$E$28=MID(CELL(“filename”,$A$1),FIND(“]”,CELL(“filename”,$A$1))+1,3),ROW(‘Master List’!$E$2:$E$28)-1),ROW(A1)))

This must be pasted inside the formula bar, and confirmed with CTRL + SHIFT + ENTER.  You should see curly brackets surrounding it when done correctly.  The formula will refer to the “Main” worksheet, look for “U6″ (represented by the formula MID(CELL(“filename”,$A$1),FIND(“]”,CELL(“filename”,$A$1))+1,3) in Column E, & extract the individual records.  Copy down & across.

Leave a Reply

You must be logged in to post a comment.