Hi,

I have 2 column of data in sheet 1. Column A is date and B is value. I
want the value in column B sheet 1 to copy column B sheet 2 according
to date.

SHEET1
A B
27/01/2010 230
31/01/2010 110
03/02/2010 34
11/02/2010 10
18/02/2010 5
12/02/2010 78
20/02/2010 22
27/02/2010 78


SHEET 2

A B
01/01/2010
02/01/2010
03/01/2010
04/01/2010
05/01/2010
06/01/2010
07/01/2010
08/01/2010
09/01/2010
10/01/2010
11/01/2010
12/01/2010
13/01/2010
14/01/2010
15/01/2010
16/01/2010
17/01/2010
18/01/2010
19/01/2010
20/01/2010
21/01/2010
22/01/2010
23/01/2010
24/01/2010
25/01/2010
26/01/2010
27/01/2010
28/01/2010
29/01/2010
30/01/2010
31/01/2010
01/02/2010
02/02/2010
03/02/2010
04/02/2010
05/02/2010
06/02/2010
07/02/2010
08/02/2010
09/02/2010
10/02/2010
11/02/2010
12/02/2010
13/02/2010
14/02/2010
15/02/2010
16/02/2010
17/02/2010
18/02/2010
19/02/2010
20/02/2010
21/02/2010
22/02/2010
23/02/2010
24/02/2010
25/02/2010
26/02/2010
27/02/2010
28/02/2010

What is the formula that i should use?

Thank you

dave
United Kingdom

What is the formula that i should use?

Take a look VLOOKUP function in help menu


"dave" wrote:

Hi,

I have 2 column of data in sheet 1. Column A is date and B is value. I
want the value in column B sheet 1 to copy column B sheet 2 according
to date.

SHEET1
A B
27/01/2010 230
31/01/2010 110
03/02/2010 34
11/02/2010 10
18/02/2010 5
12/02/2010 78
20/02/2010 22
27/02/2010 78


SHEET 2

A B
01/01/2010
02/01/2010
03/01/2010
04/01/2010
05/01/2010
06/01/2010
07/01/2010
08/01/2010
09/01/2010
10/01/2010
11/01/2010
12/01/2010
13/01/2010
14/01/2010
15/01/2010
16/01/2010
17/01/2010
18/01/2010
19/01/2010
20/01/2010
21/01/2010
22/01/2010
23/01/2010
24/01/2010
25/01/2010
26/01/2010
27/01/2010
28/01/2010
29/01/2010
30/01/2010
31/01/2010
01/02/2010
02/02/2010
03/02/2010
04/02/2010
05/02/2010
06/02/2010
07/02/2010
08/02/2010
09/02/2010
10/02/2010
11/02/2010
12/02/2010
13/02/2010
14/02/2010
15/02/2010
16/02/2010
17/02/2010
18/02/2010
19/02/2010
20/02/2010
21/02/2010
22/02/2010
23/02/2010
24/02/2010
25/02/2010
26/02/2010
27/02/2010
28/02/2010

What is the formula that i should use?

Thank you

dave
United Kingdom
.

Hi Dave,

Assuming that your data has column headers and therefore the data starts on
row 2 then in cell B2 of Sheet2 insert the following formula and copy it down
the column.

=VLOOKUP(A2,Sheet1!$A$2:$B$9,2,FALSE)

However, the above formula will return #N/A for each date that does not have
a matching value. The following extension of the formula will display a blank
cell for the non matching dates. Note the formula is one line and you might
need to edit it in the formula bar to remove a line ffed if you copy and
paste it into your worksheet.

=IF(ISNA(VLOOKUP(A2,Sheet1!$A$2:$B$9,2,FALSE)),"", VLOOKUP(A2,Sheet1!$A$2:$B$9,2,FALSE))

Copy and paste the formula down the column.

--
Regards,

OssieMac


"dave" wrote:

Hi,

I have 2 column of data in sheet 1. Column A is date and B is value. I
want the value in column B sheet 1 to copy column B sheet 2 according
to date.

SHEET1
A B
27/01/2010 230
31/01/2010 110
03/02/2010 34
11/02/2010 10
18/02/2010 5
12/02/2010 78
20/02/2010 22
27/02/2010 78


SHEET 2

A B
01/01/2010
02/01/2010
03/01/2010
04/01/2010
05/01/2010
06/01/2010
07/01/2010
08/01/2010
09/01/2010
10/01/2010
11/01/2010
12/01/2010
13/01/2010
14/01/2010
15/01/2010
16/01/2010
17/01/2010
18/01/2010
19/01/2010
20/01/2010
21/01/2010
22/01/2010
23/01/2010
24/01/2010
25/01/2010
26/01/2010
27/01/2010
28/01/2010
29/01/2010
30/01/2010
31/01/2010
01/02/2010
02/02/2010
03/02/2010
04/02/2010
05/02/2010
06/02/2010
07/02/2010
08/02/2010
09/02/2010
10/02/2010
11/02/2010
12/02/2010
13/02/2010
14/02/2010
15/02/2010
16/02/2010
17/02/2010
18/02/2010
19/02/2010
20/02/2010
21/02/2010
22/02/2010
23/02/2010
24/02/2010
25/02/2010
26/02/2010
27/02/2010
28/02/2010

What is the formula that i should use?

Thank you

dave
United Kingdom
.