Matching dates and returning another column

**jmoorc** · 11-29-2011, 08:58 PM

Hi all

I have a summary sheet with a list of dates in column B, I want to check data sheet with dates in column C, when a date matches, return the data in column G. Should be simple.....but I cannot for the life of me get it.

To complicate matters a bit, the date cells are groups of nine rows.....merged, but the data needs to come from the individual rows....

I have attached a stylised sheet.....what I am aiming to achieve is, look at sheet one date, match the date on sheet 2 and return the instructor requirement by period for that date, and then same for sheet 3 etc etc.

I am sure it is a simple fix for the gurus out there!

JM

**jeffreybrown** · 11-29-2011, 09:03 PM

So are you saying in cell E11 on Sheet1 you want to return 1 and cell E20 on Sheet1 return 2 and so on?

**jmoorc** · 11-29-2011, 09:53 PM

Hi,

that is correct, and on Sheet 1 E38 it would add both sheet 2 and sheet 3 for 19 Jan 12 as the date matches, so would equal 3

Thanks for the help

JM

**jeffreybrown** · 11-29-2011, 10:20 PM

Could this work for you...

**jmoorc** · 11-29-2011, 11:50 PM

Thanks alot for that.....not quite there.....need to pick up the date and period I guess to as it seems to just return the last result against a date.....

I have updated the date part to use date and period....not quite sure which part to replicate and point at the period column (C)

Cheers
JM

**jmoorc** · 11-29-2011, 11:51 PM

Sorry attached

**jeffreybrown** · 11-30-2011, 07:41 PM

This seems to do it. I introduced the use of Indirect into the formula so you can just enter the formula in E3 (confirmed with Ctrl + Shift + Enter) and then drag over to F3 and then drag down.

The Indirect part of the formula looks at E2 and F2 for the sheet name which makes the formula dynamic. The one draw back, the Indirect function is volatile so you want to use it sparingly in your spreadsheet.

If you happen to have 2007+ you can invoke the IFERROR in lieu of ISNA which would reduce the formula to...

CSE
=IFERROR(INDEX(INDIRECT("'" & E$2 & "'!$G$4:$G$138"),MATCH(1,IF(INDIRECT("'" & E$2 & "'!$C$4:$C$138")=$B3,IF(INDIRECT("'" & E$2 & "'!$D$4:$D$138")=$C3,1))),0),"")

Could be a smarter way, but this is the route I would go...

**jmoorc** · 11-30-2011, 07:46 PM

Hi Jeff

Many thanks that has broken the problem for me.....you are a star!

cheers
JM

**jeffreybrown** · 11-30-2011, 07:49 PM

You're most welcome...Glad I could be of some help

If you are satisfied with the solution(s) provided, please mark your thread as Solved.

How to mark a thread Solved
Go to the first post
Click edit
Click Go Advanced
Just below the word Title you will see a dropdown with the word No prefix.
Change to Solved
Click Save