Q:

Fill in missing values using Excel

For a variable, I have observations for each workday (i.e. from monday to friday) for several years. But no observation for saturday and sunday.
Date        X
03.01.2005  2.154
04.01.2005  2.151
05.01.2005  2.151
06.01.2005  2.15
07.01.2005  2.146
10.01.2005  2.146
11.01.2005  2.146
12.01.2005  2.145
13.01.2005  2.144
14.01.2005  2.144
17.01.2005  2.143
as you can see the dates 08.01, 09.01, 15.01 and 16.01 are missing since they are weekend days. Now I am looking for a way to fill the weekend days with the value of the friday before the given weekend.
    Date    X
03.01.2005  2.154
04.01.2005  2.151
05.01.2005  2.151
06.01.2005  2.15
07.01.2005  2.146
08.01.2005  2.146
09.01.2005  2.146
10.01.2005  2.146
11.01.2005  2.146
12.01.2005  2.145
13.01.2005  2.144
14.01.2005  2.144
15.01.2005  2.144
16.01.2005  2.144
17.01.2005  2.143

as you can see from the second data sample, the values for the dates 08.01 and 09.01 are filled with the value from friday 07.01. And the dates 15.01 and 16.01 contain the values from friday, 14.01. How can this be done in Excel? Many thanks!

A:

The vlookup() function handles this for you.  Given this simple example:

I named A1..B5 with the name data to make the formula easier to do.  Then in columns D..E, you can see that it does just what you want.  The actual formula is in column E.  I just typed it to the right to make it easier to see...
By setting the 4th parameter of the vlookup() function to TRUE, it finds the best approximate match, which is exactly what you want.  Just remember that the way the you sort the values in the data affects how vlookup() finds the values.

