Using Formulas to extract data

Morning all,

I have a VERY large database of results for a club.

I want to be able the extract the results and info from the Data tab and show it on a separate sheet. I have the main bit set up like:

• a named range for the teams played against
• The area and sheet I want the results displayed
• The data validation list dropdown to select the team

Now comes the problem. I have tried to look up how to do this on YouTube and google and looks like I need to be using things like Index, match, vlookup and combining them in to one big functions but I've gotten more confused the more I read and watch.

I have attached a sample of data in excel and I have put annotation in a text box for clarity.

Can someone help please ?

Thanks in advance,

N.

EDIT: I am open to it being a vba if this will be better to search and help stop the excel taking an age to open.

In E2, copied across to N2 and down:
=IFERROR(INDEX(Data!A:A,AGGREGATE(15,6,ROW(Data!$C$2:$C$30)/(Data!$C$2:$C$30=$A$2),ROWS($1:1))),"")

In O2, copied across to and down:
=IFERROR(INDEX(Data!K:K,AGGREGATE(15,6,ROW(Data!$C$2:$C$30)/(Data!$C$2:$C$30=$A$2),ROWS($1:1)))&"","")

WOW thanks Glenn for the quick reply and the info.

I am looking at both formulas and can only see one difference and that is in the second one it has &"" which I'm a bit confused on.

Right so the maximum the team has played an opponent is about 280 times (my data goes back to 1926) therefore so I will need to copy it all the way down to line 281 (maybe 290 for safety).

Thanks again

Correct. The &"" turns 0, in cells where there is no match, and whereyou are expecting a TEXT result, into a blank. Without it you'd see a sea ofzeros.

To copy down, just adjust the range $C$30 to suit your raw data... and away you go.

Your formula: =IFERROR(INDEX(Data!A:A,AGGREGATE(15,6,ROW(Data!$C$2:$C$30)/(Data!$C$2:$C$30=$A$2),ROWS($1:1))),"")

I have changed it to suit the actual DB:

=IFERROR(INDEX(Data!F:F,AGGREGATE(15,6,ROW(Data!$H$2:$H$5000)/(Data!$H$2:$H$5000=$A$2),ROWS($1:1))),"")

And it is working

=IFERROR(INDEX(Data!K:K,AGGREGATE(15,6,ROW(Data!$C$2:$C$30)/(Data!$C$2:$C$30=$A$2),ROWS($1:1)))&"","") is now changed to

=IFERROR(INDEX(Data!P:P,AGGREGATE(15,6,ROW(Data!$H$2:$H$5000)/(Data!$H$2:$H$5000=$A$2),ROWS($1:1)))&"","")

Also working.

I've got a few other bits to add to the sheet stat wise so I maybe back

Thanks Glenn you're a star

That should do it all! You're welcome and thanks for the rep.