INDEX & MATCH Function across two Worksheets

[JessS]

Hi All,

I could really do with some help on the following.

I have two worksheets, namesd as follows:

Worksheet1: SUMMARY
Worksheet2: ART 2013

'ART 2013' contains a list of staff numbers in column C16:C752 and a list of dates in column M16:M752 (formated as "mm-yyyy")

Within 'SUMMARY' cell G12 contains the date 04-2013.

What I would like to do is achieve a function that will look at 'ART 2013' columns C and M in and where the date in Column M is equal to cell G12 (04-2013) of the 'SUMMARY' worksheet excel will return the Staff Number in 'ART 2013' column C.

Is this possible? I really hope I have explained it OK!

I have looked at another forum and got the example to work, but only on a small set of data and not across two work sheets. I am a bit confused and could really do with some expert advise.

Many Thanks

 

[Dryver14]

You are looking up 736 rows, I am guessing 04-2013 comes up several times, roughly how many names are returned?

 

[miss_ell]

Hello,

I would think a pivot table will work very well to get what you need.

Place your cursor in the table in the ART 2013 worksheet, choose Data, Pivot Table and Chart Report, Next (at this point Excel should automatically select the whole table. Make sure it does before continuing), Next again, leave it at New Worksheet then press Finish.

You should now have a blank Pivot table with a list of your column headings. Drag the column heading in the M column (the date) to the very top where it says "Page", then drag the column heading for C in the Row section (along with any other information you might need).

Now you can click on the top drop-down menu, choose the date you wish and the list of staff numbers for that date should automatically appear below.

I hope this helps. If you have any queries please let me know.

 

[JessS]

Thank you Dryver14. 04-2013 comes up between 40 - 100 times.

 

[JessS]

ADVERTISEMENT

Thank you Miss_ell too. I have wondered about a Pivot Table having seen them in action, but I have to confess I have no idea how they work... (until now hopefully) I will give it a go. Thank you

 

[hiker95]

JessS,

See if one of the below links will help your.

Pivot Intro
Using Pivot Tables and Pivot Charts in Microsoft Excel

Office 2010 Class #36: Excel PivotTables Pivot Tables 15 examples (Data Analysis) - YouTube
Office 2010 Class #36: Excel PivotTables Pivot Tables 15 examples (Data Analysis) - YouTube

Getting Started with Pivot Tables
Excel Pivot Table -- Dynamic Data Source

Overview of PivotTable and PivotChart reports
Overview of PivotTable and PivotChart reports - Excel - Office.com

 

[JessS]

Hi All,

Thanks so much for all your input . The Pivot Table worked perfectly!!!

Thank you

 

[hiker95]

JessS,

Thanks for the feedback.

You are very welcome. Glad we could help.

Come back anytime.