Weekly data and need to match to daily

**lnagell** · 03-08-2013, 02:04 PM

Hello! This is my first post so bare with me.

I receive data that is a weekly value but I need it to matchup daily.

Example:

Weekly Data:
Column A Column B
5/24/1996 Health
5/31/1996 Energy
6/7/1996 Materials

Need it to matchup as follows:
Column C Column D
5/24/1996 Health
5/28/1996 Health
5/29/1996 Health
5/30/1996 Health
5/31/1996 Energy
6/3/1996 Energy
6/4/1996 Energy
6/5/1996 Energy
6/6/1996 Energy
6/7/1996 Materials
6/8/1996 Materials.....etc. etc.

Thanks for your help

**Harribone** · 03-08-2013, 02:10 PM

Hi and welcome to the forum!

Can you upload an example spreadsheet with the results typed in manually so we can see how the data is to be 'matched up' exactly.

This can be done by clicking 'Go Advanced' next to the 'Post Quick Reply' and then you can attach files from there.

**lnagell** · 03-08-2013, 02:33 PM

I think I figured it out. I removed the "0" (which is exact match) at the end of my index, match formula and used less than "1": =INDEX($B$4:$B$898,MATCH(C6,($A$4:$A$898),1))

I think it just takes the closest date. I'm wondering if there is a full-proof formula though. I don't want it to pull in the wrong data.

**oeldere** · 03-08-2013, 02:43 PM

I copied your data to column J and K

Then I seleted the total range.

Excel 2007 => sort => sort on column K from A to Z

after that sort on column J from old to new

Then you get the result in the file

You can also use column A and B for this action, but i copied it just to show you.

**Harribone** · 03-08-2013, 02:45 PM

Yup that works, may need to be -1 depending on how you want it to match.

0: This finds exact match only
1: This finds nearest match less than or equal to lookup value
-1: This finds nearest match greater than or equal to lookup value