Complex data extraction from a table

**Corke** · 03-12-2013, 01:34 PM

Hi there Excel people!

I'm trying to find a way of pulling out bits of data from a table but I can't figure out how to do it! I've put 'complex' in the subject but it might not be for people in these forums.

I'm working on a dinner register and I've attached an example of the data I'm looking at. Basically I want people to be able to add a 'week ending' date into cell AD3 and the 'dinners' column (AE) populates each day of the week from row 4 from the correct table. I'm not sure on the formula to use in AE4 - AE9 to achieve this. HLOOKUP will pull data from below the date but not for each day. Would INDEX and/or MATCH work? It's no problem if I need to alter the format or orientation of the tables.

I hope this makes sense! Any help appreciated!

**FDibbins** · 03-12-2013, 01:56 PM

Hi and welcome to the forum

Try this, copied down...

=OFFSET($A$1,MATCH($AE$3,$A$1:$A$11,0)-1,MATCH($AD$3,$A$1:$AB$1,0)-6+ROW(A1)-1)

BUT, make sure you change the name in A4 to match the heading in the 2nd table. I have set it up so that you can just change the heading in AE3 and it will pull out the relevant data

let me know how you make out?

**ChemistB** · 03-12-2013, 01:59 PM

With the date in AD3
In AE4 copied down
=OFFSET(INDEX($B$1:$AB$1,MATCH($AD$3,$B$1:$AB$1,0)),3,ROW(A1)-6,1,1)

and in AF4 copied down
=SUM(OFFSET(INDEX($B$1:$AB$1,MATCH($AD$3,$B$1:$AB$1,0)),5,ROW(A1)-6,2,1))
See attachment

**Corke** · 03-13-2013, 09:26 AM

Thank you both for the replies. I'll not get chance to look at this properly until tomorrow so I'll give my full attention then and mark as solved as appropriate.

**Corke** · 03-14-2013, 11:49 AM

I'll be using ChemistB's solution as this doesn't require changing headings but thanks again to you both for the input. I just need to get my head round what it's doing so I can use something similar in other spreadsheets!

Expect more questions in the future