Array (MATCH function?)

[bob]

The worksheet "Arenas" contains a list of names in colA. The worksheet
"Chart1" contains a list of dates in colA and a list of names in colE.

From the Arenas worksheet, I want to populate cell E4 with a value of 1 for
when a name in Arenas:colA matches a name in Chart1:colE and has a unique
date in Chart1:colA. If there are multple matches for a given date, I still
want cell E4 to have a value of 1 (instead of the number of times a match
occurs for that date). If no matches exist, E4 should have a value of 0.

Can anyone kindly help? Thank you.

Bob

 

[Sheeloo]

The following entered in Col B of Arenas will give you 1 if the name in Col A
matches
with a name in Chart1:colE
=ISNA(VLOOKUP(A1,Charts!E:E,1,False),0,1)

I did not understand your reference to E4...

I don't think this is what you want... Let us know

 

[T. Valko]

I think we'll probably need to see an example.

 

[bob]

Sorry if i wasn't clear, the reference to E4 is the cell in which the formula
will go. Note sure how to provide an example (file) for you in this forum.

The formula you suggested returns an error and points to the zero as the
problem.

Bob

 

[bob]

I am providing an example as to the above question.

I am trying to calculate how many games a basketball player has played based
on the entries in the worksheets "Arenas" and "Chart1." In Arenas, the
entries in col A are as follows:

COL A
Arenas
Butler
Haywood
Thomas

In Chart1 the entries in cols A and E are as follows:

COL A COL E
4/23/08 Arenas
4/24/08 Butler
4/24/08 Arenas
4/23/08 Haywood
4/23/08 Arenas
4/26/08 Thomas

In this example, it is evident from the data in Chart1 that Arenas has
played in 2 games (4/23 and 4/24) even though his name populates 3 cells, 2
of which are on the same date (4/23). In the Arenas sheet, I want a formula
that will calculate the number of games a player has played when his name
appears in both Chart1 and Arenas.

Make sense?

Thanks,
Bob

 

[Luke M]

Thanks for clearing it up Bob.

Afraid I don't have time to think too far on this, but could you setup a
PivotTable (Data -> PivotTable) to display the data you want? Possibly
combine a PivotTable with a COUNT function.

I realize that's not a very elegant solution, but hopefully it provides some
ideas.