Formula to match and extract multiple rows?

flammabubble

New Member
Joined
Aug 19, 2015
Messages
14
Hi Mr Excel forum.

I'm sure there must be a fairly straightforward way to do this but for the life of me I haven't been able to figure it out. I should preface by mentioning that I'm using google sheets.

Basically I have one spreadsheet which is a big table of match results for different players (Column C is player name, D is date, E is the result etc). Players appear multiple times in column C on varying dates.

What I want to be able to do is, on a different sheet, put a player name (let's say in cell A1), and then a formula in cell A2 will search the match results and return all rows of data that refer to that player (much the same way that using autofilter would if you filtered by player name).

I need this extracted to a separate sheet for various reasons so I can't just use autofilter. I'm guessing I need some kind of array formula but would be grateful for advice.

Thanks!
 

Some videos you may like

Excel Facts

Select a hidden cell
Somehide hide payroll data in column G? Press F5. Type G1. Enter. Look in formula bar while you arrow down through G.

Toadstool

Well-known Member
Joined
Mar 5, 2018
Messages
819
Office Version
2016
Platform
Windows
Hi Flammabubble,

I'm not a Google Sheets expert but would =FILTER(Sheet1!C1:E9999,Sheet1!C1:C9999=A1) do what you ask?
 

flammabubble

New Member
Joined
Aug 19, 2015
Messages
14
Hi Flammabubble,

I'm not a Google Sheets expert but would =FILTER(Sheet1!C1:E9999,Sheet1!C1:C9999=A1) do what you ask?
Needed a little tinkering to actually get the filter to work for some reason, but yeah that's exactly what I needed, thank you! Way simpler than everything I had been looking at haha.
 

Toadstool

Well-known Member
Joined
Mar 5, 2018
Messages
819
Office Version
2016
Platform
Windows
You're welcome!
Thanks for the feedback.
 

Watch MrExcel Video

Forum statistics

Threads
1,102,565
Messages
5,487,592
Members
407,605
Latest member
PACULA

This Week's Hot Topics

Top