Google Spreadsheet Filter data from two sheets to third sheet

Question:

I have a sales data spreadsheet with 3 sheets.  In the 3rd sheet, I am trying to extract data from the other 2 sheets based on date and name.  For example:

Sheet 1

DATE   |      NAME    |    PHONE

9/9/2012     Stephen      206-999-9999 

9/10/2012   Marcus       360-999-9999

9/11/2012   Kishan        253-999-9999

Sheet 2

NAME    |    NOTES

Stephen      deliver to home address

Marcus        needs 3 orders

Kishan        is the best support ever

What I am trying to do is pull data from Sheets 1 and 2 into Sheet 3 based on date and name. In Sheet 3, I want to enter a date in one cell, then based on that date populate the rest of the spreadsheet using data from Sheets1 and 2.

For example, Sheet 3 looks like this:

A1 9/9/2012 (manually enter)

A2  NAME  (set header)

A3  I need a formula that would look at Sheet 1, find each row with the date 9/9/2012 (in the date column), then populate cells A3, A4, A5... (depending how many sales there was on that date) with all the customer names (in the name column) that placed orders on that date

B2  PHONE (set header)

B3  I need a formula that based on the name in A3, A4, A5... in Sheet 3, would look at Sheet 1, find each name (in the name column), then populate cells B3, B4, B5... with the phone numbers of that particular customer (in the phone column) that placed an order on that date

C2  NOTES  (set header)

C3  I need a formula that based on the name in A3, A4, A5... in Sheet 3, would look at Sheet 2, find each name (in the name column), then populate cells C3, C4, C5... with the notes (in the notes column) that are associated with that name

Can you please help me with this?

Thank you!

Stephen

Solution:


Following is the Screenshot of Sheet1:

Following is the Screenshot of Sheet2:

Following is the Screenshot of Sheet3:

Put the following formula in Cell A3 of Sheet3:
=FILTER('Sheet1'!B2:C;'Sheet1'!A2:A=A1)

Put the following formula in Cell C3 of Sheet3:
=FILTER('Sheet2'!B2:B;MATCH('Sheet2'!A2:A;A3:A))

I hope the above solution will help you, and if you need more help then please do comment below on this blog itself, I will try to help you out.

I also take up private or confidential projects:

http://igoogledrive.blogspot.in/p/paid-projects.html

If this blog post was helpful to you, and if you think you want to help me too and make my this blog survive then please donate here: http://igoogledrive.blogspot.com/2012/09/donate.html 

Thanks,

Kishan,

iGoogleDrive