Help needed with lists.

  • Thread starter Thread starter Chris Mitchell
  • Start date Start date
C

Chris Mitchell

I have two lists on separate sheets in the same book; each contains a common
field in column A. One list is much longer than the other, and both are
over 5K rows.

In the longer list I need to show if the common field appears in the other,
shorter list. I would like to add a column, 'Exists in shorter list' to the
longer list and have a formulae enter 'Yes' or 'No' as appropriate.

I've tried VLOOKUP, but this doesn't work across different sheets.

How can I do this?

TIA

Chris.
 
I have two lists on separate sheets in the same book; each contains a
common field in column A. One list is much longer than the other, and
both are over 5K rows.

In the longer list I need to show if the common field appears in the
other, shorter list. I would like to add a column, 'Exists in shorter
list' to the longer list and have a formulae enter 'Yes' or 'No' as
appropriate.

I've tried VLOOKUP, but this doesn't work across different sheets.

How can I do this?

TIA

Chris.
Click to expand...

if Sheet1 starting Cell A1 is your longer list
and Sheet2 Column A is your shorter list
then copy&paste this formula in Sheet1 cell B1

=IF(COUNTIF(Sheet2!A:A,A1)>0,"Yes","No")

then copy&paste down.

the countif() looks at sheet2 and counts cell A1 contents if theres more
then zero. the if() is just the usual true/false.
 
You can use =match() to look for matches.

=match(a1,sheet2!a:a,0)

will return a number (the index into sheet2!a:a) if there is a match.

It will return an error if there is no match.

So you could use:

=isnumber(match(a1,sheet2!a:a,0))
to see true for matches and false for no match.

You may want to look at Chip Pearson's site. He has lots of techniques to work
with duplicates:
http://www.cpearson.com/excel/Duplicates.aspx
 
Hi Chris,

VLOOKUP does work across different sheets, for example your formula would be
something like this:

=IF(ISNA(VLOOKUP(A1,Sheet1!A$1:A$182,1,FALSE)),"No","Yes")

or

=IF(ISNA(VLOOKUP(J5,Sheet1!A$1:A$182,1,FALSE)),"No","Yes")

or

=IF(ISNA(VLOOKUP(J5,Sheet1!A$1:A$182,1,)),"No","Yes")

Where the values you want to compare are in the current spreadsheet starting
in cell A1 and the other values are in column A of the other sheet (here
Sheet1).
 
Thanks to all who responded.

I have now been able to get this to work as I wanted.

Thanks again.

Chris.
 
Back
Top