Formula to match and extract multiple rows?

flammabubble

New Member
Joined
Aug 19, 2015
Messages
21
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!
 

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
1,581
Office Version
  1. 2016
Platform
  1. 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
21
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
1,581
Office Version
  1. 2016
Platform
  1. Windows
You're welcome!
Thanks for the feedback.
 

Watch MrExcel Video

Forum statistics

Threads
1,133,428
Messages
5,658,738
Members
418,467
Latest member
sc356448

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top