2 pieces of data on the same row in one list also in same row on other?

[willg]

Hi

I wonder if anyone has any advice on the following? I'm sure there
must be a simple way of doing this but I'm being too dim at the
moment.

I have two lists, each relating a name to a cost centre, both in the
following format:

cost centre name
89 Fred Bloggs
89 Steve Jones
36 Jane Smith
65 Jane Smith
23 Englebert Humpledink

etc.

I need to find which pairs of name and cost centre appear against one
another in both lists. My stumbling block is that there are multiple
identical values in both the cost centre and the name column.

My first thought was to use something like vlookup / match to identify
an instance of a given cost centre, then offset and an if statement to
check if the adjacent name also matches, but this is no good as they
only return on the first instance of the value.

Forgive my ignorance, but please can anyone help?

Many thanks!

 

[Peo Sjoblom]

You can probably use something from this site

=SUMPRODUCT((I2:I100=A2)*(E2:E100="S"))

http://www.cpearson.com/excel/duplicat.htm

 

[Peo Sjoblom]

Oops!

the formula does not belong there, it was for another post. Just go with
link.
I suppose the formula was in the clipboard and I pasted it w/o thinking
and then obviously didn't see it when I fired off the post!

--

Regards,

Peo Sjoblom

You can probably use something from this site

=SUMPRODUCT((I2:I100=A2)*(E2:E100="S"))

http://www.cpearson.com/excel/duplicat.htm

 

[willg]

Hi Peo

Many thanks for your help - much appreciated.

I had a look at the link, which was instructive.

It was an array formula I managed to bodge into working which gave the
output I was after:

{=MATCH(1,((('list A'!$C$2:$C$287)=('list B'!H2))*(('list
A'!$D$2:$D$287)=('list B'!I2))),0)}

This returned the array reference of the row where the matching pair in
the second list was, and #N/A where the pair did not appear.

Thanks

Will