Vlookup or Index/Match
 

I have a worksheet of data (cells named PV_Data), extracted from a d/
b, column A contains a "week commencing" date (cells named "Week_Of")
and column B contains a list of names (cells named "Resources"). I
have a second worksheet that I am trying to populate based upon the
contents of the first sheet. In this worksheet, Column A contains the
list of names I am looking for, Column B, C, D, E and F are labeled/
column headers with the Week Commencing dates, to be found in the
first worksheet. What I want to do is put an x in the appropriate
column when I find the name in the second worksheet
Sheet 1
01/04/2008 Fred
01/04/2008 Jim
07/04/2008 Jim
07/04/2008 Joe
14/04/2008 Fred
14/04/2008 Jim
14/04/2008 Joe
21/04/2008 Jim
21/04/2008 Joe

Sheet 2
Names 01/04/2008 07/04/2008 14/04/2008 21/04/2008 .....
Fred X X
Jim X X
X X
John
Joe X
X X

I believe I need to use Index and Match, but I can't seem to get
beyond the #N/A result

Any suggestion gratefully received
Fred

Vlookup or Index/Match
 

In sheet 2

B2: =IF(SUMPRODUCT((Name=$A2)*(Date=B$1))0,"x","")


"Fred" wrote:

I have a worksheet of data (cells named PV_Data), extracted from a d/
b, column A contains a "week commencing" date (cells named "Week_Of")
and column B contains a list of names (cells named "Resources"). I
have a second worksheet that I am trying to populate based upon the
contents of the first sheet. In this worksheet, Column A contains the
list of names I am looking for, Column B, C, D, E and F are labeled/
column headers with the Week Commencing dates, to be found in the
first worksheet. What I want to do is put an x in the appropriate
column when I find the name in the second worksheet
Sheet 1
01/04/2008 Fred
01/04/2008 Jim
07/04/2008 Jim
07/04/2008 Joe
14/04/2008 Fred
14/04/2008 Jim
14/04/2008 Joe
21/04/2008 Jim
21/04/2008 Joe

Sheet 2
Names 01/04/2008 07/04/2008 14/04/2008 21/04/2008 .....
Fred X X
Jim X X
X X
John
Joe X
X X

I believe I need to use Index and Match, but I can't seem to get
beyond the #N/A result

Any suggestion gratefully received
Fred

Vlookup or Index/Match
 

Have you thought of adding headers and then creating a pivottable?

Drag the header for the date to the column field.
Drag the header for the name to the row field.
and drag the name header to the data field.

You'll see a count of names instead of an X, but you could select the
pivottable, copy|paste special|values and then do a couple of edit|replaces to
change the 1's to X's (if you really wanted them).

Fred wrote:

I have a worksheet of data (cells named PV_Data), extracted from a d/
b, column A contains a "week commencing" date (cells named "Week_Of")
and column B contains a list of names (cells named "Resources"). I
have a second worksheet that I am trying to populate based upon the
contents of the first sheet. In this worksheet, Column A contains the
list of names I am looking for, Column B, C, D, E and F are labeled/
column headers with the Week Commencing dates, to be found in the
first worksheet. What I want to do is put an x in the appropriate
column when I find the name in the second worksheet
Sheet 1
01/04/2008 Fred
01/04/2008 Jim
07/04/2008 Jim
07/04/2008 Joe
14/04/2008 Fred
14/04/2008 Jim
14/04/2008 Joe
21/04/2008 Jim
21/04/2008 Joe

Sheet 2
Names 01/04/2008 07/04/2008 14/04/2008 21/04/2008 .....
Fred X X
Jim X X
X X
John
Joe X
X X

I believe I need to use Index and Match, but I can't seem to get
beyond the #N/A result

Any suggestion gratefully received
Fred

--

Dave Peterson

Vlookup or Index/Match
 

Hmm, not quite, that guve me a #NUM! error

Regards
Fred

On May 16, 2:49*pm, Teethless mama
wrote:
In sheet 2

B2: =IF(SUMPRODUCT((Name=$A2)*(Date=B$1))0,"x","")



"Fred" wrote:
I have a worksheet of data (cells named PV_Data), extracted from a d/
b, column A contains a "week commencing" date (cells named "Week_Of")
and column B contains a list of names (cells named "Resources"). *I
have a second worksheet that I am trying to populate based upon the
contents of the first sheet. In this worksheet, Column A contains the
list of names I am looking for, Column B, C, D, E and F are labeled/
column headers with the Week Commencing dates, to be found in the
first worksheet. *What I want to do is put an x in the appropriate
column when I find the name in the second worksheet
Sheet 1
01/04/2008 * * * *Fred
01/04/2008 * * * *Jim
07/04/2008 * * * *Jim
07/04/2008 * * * *Joe
14/04/2008 * * * *Fred
14/04/2008 * * * *Jim
14/04/2008 * * * *Joe
21/04/2008 * * * *Jim
21/04/2008 * * * *Joe

Sheet 2
Names * * *01/04/2008 * 07/04/2008 * 14/04/2008 * 21/04/2008 .....
Fred * * * * * * * *X * * * * * * * * * * * * * * * * * * X
Jim * * * * * * * * X * * * * * * * * *X X * * * * * * * * * X
John
Joe * * * * * * * * * * * * * * * * * * X X * * * * * * * * *X

I believe I need to use Index and Match, but I can't seem to get
beyond the #N/A result

Any suggestion gratefully received
Fred- Hide quoted text -

- Show quoted text -