how do I compare columns in two different workbooks?
 

I need to compare the information in one column of an excel workbook to
another column of an excel workbook. Then need the system to report match or
non-match.

Thanks

What exactly do you mean by a match? For example:
If we are using cells A1:A10 and B1:B10, would be a match if the number '5'
appeared in A1 and B5? Or, would the number '5' have to appear in A1 and B1
to constitute a match?

If the match can be anywhere in the second column, this might do it:
=IF(ISNA(VLOOKUP(A1,$B$1:$B$10,1,0)),"No Match","Match")

If the match has to be in the same location:
=IF(A1=B1,"Match","No Match")

In both cases, copy down as far as needed.

tj

"Provider98122" wrote:

I need to compare the information in one column of an excel workbook to
another column of an excel workbook. Then need the system to report match or
non-match.

Thanks

The title of your message got cut off on my machine--tghe methods I posted
won't work from one file to another. Sorry about that.

tj

"Provider98122" wrote:

I need to compare the information in one column of an excel workbook to
another column of an excel workbook. Then need the system to report match or
non-match.

Thanks

I am doing something where I am comparing Column S to Column A. I used the
formula you provided, but I don't want it to say "Match" or "No Match". I
want it to say whatever is in the cell (the date). What would I do to make
it return the value of the cell?

Here's my modified version:
=IF(ISNA(VLOOKUP(Sheet1!S3,Sheet1!$A$3:$A$400,1,0) ),"No Match","Match")

Also, if the date does match, I want to return columns T-AI on Sheet1 in
columns T-AI on Sheet2 that correspond to the cell that it matched.

Example:
Sheet1
Columns
A B C ......... S T U
10/1 R Y 9/29 R G
10/2 G G 10/2 R R

Sheet2 would return:
Columns
A B C ......... S T U
10/2 R R

Thanks,

Sharon


"tjtjjtjt" wrote:

What exactly do you mean by a match? For example:
If we are using cells A1:A10 and B1:B10, would be a match if the number '5'
appeared in A1 and B5? Or, would the number '5' have to appear in A1 and B1
to constitute a match?

If the match can be anywhere in the second column, this might do it:
=IF(ISNA(VLOOKUP(A1,$B$1:$B$10,1,0)),"No Match","Match")

If the match has to be in the same location:
=IF(A1=B1,"Match","No Match")

In both cases, copy down as far as needed.

tj

"Provider98122" wrote:

I need to compare the information in one column of an excel workbook to
another column of an excel workbook. Then need the system to report match or
non-match.

Thanks

I'm not sure I understand what you are asking. If the below options don't
help you get the answer you are looking for, please post back with step by
step instructions as to what you are looking for. It's time for me to retire
for the evening, but I will check back tomorrow to make sure you ulitimately
received a satisfactory answer.

If you want to check for a match and return the date only if there is a match:
=IF(ISNA(VLOOKUP(Sheet1!S3,Sheet1!$A$3:$A$400,1,0) ),"No
Match",VLOOKUP(Sheet1!S3,Sheet1!$A$3:$A$400,1,0))

**I typed this formula here manually. If it malfunctions, it's probably
because I left out a parentheses**

As for the sheet2 and sheet1 issue, I don't know what cell in column T you
started with, so I assumed T3 only because it matches S3 in the other formula:
If you want to return Columns T-AI only if the value in Column S matches any
value in Column S, then put this in T3 on Sheet2:

IF(ISNA(VLOOKUP(Sheet1!S3,Sheet1!$A$3:$A$400,1,0)) ,"",Sheet1!T3)

Copy this over to AI. Copy down as far as needed as well.

Here is where I'm a bit confused with your post:

I am doing something where I am comparing Column S to Column A. I used the
formula you provided, but I don't want it to say "Match" or "No Match". I
want it to say whatever is in the cell (the date). What would I do to make
it return the value of the cell?


What do you mean by 'whatever is in the cell'? You want to return some value
even if there isn't a match? If so, the value from what cell?

To be clear, can S3 match any value in A3:A400? Or does it have to match A3?
The answer to this question may make a different approach to the one I am
taking a better option.

tj

Thanks for your response. I will try to implement what you've suggested so
far and see if it works.

Sorry for the confusion. By "whatever" I meant whatever value is in the
cell, should be returned. It might say "red", "green", "yellow", "no", or it
might contain a date.

Also, the matching that I'm wanting to accomplish is looking for a date in
column S and if it is anywhere in Column A, then return the date value from
column s on sheet 2. The corresponding columns T-AI would be for the same
row as where the date came from in Column S. I hope that makes sense.

I'll post again in a new thread tomorrow if I can't get it to work.

Thanks again,

Sharon

"tjtjjtjt" wrote:

I'm not sure I understand what you are asking. If the below options don't
help you get the answer you are looking for, please post back with step by
step instructions as to what you are looking for. It's time for me to retire
for the evening, but I will check back tomorrow to make sure you ulitimately
received a satisfactory answer.

If you want to check for a match and return the date only if there is a match:
=IF(ISNA(VLOOKUP(Sheet1!S3,Sheet1!$A$3:$A$400,1,0) ),"No
Match",VLOOKUP(Sheet1!S3,Sheet1!$A$3:$A$400,1,0))

**I typed this formula here manually. If it malfunctions, it's probably
because I left out a parentheses**

As for the sheet2 and sheet1 issue, I don't know what cell in column T you
started with, so I assumed T3 only because it matches S3 in the other formula:
If you want to return Columns T-AI only if the value in Column S matches any
value in Column S, then put this in T3 on Sheet2:

IF(ISNA(VLOOKUP(Sheet1!S3,Sheet1!$A$3:$A$400,1,0)) ,"",Sheet1!T3)

Copy this over to AI. Copy down as far as needed as well.

Here is where I'm a bit confused with your post:

I am doing something where I am comparing Column S to Column A. I used the
formula you provided, but I don't want it to say "Match" or "No Match". I
want it to say whatever is in the cell (the date). What would I do to make
it return the value of the cell?


What do you mean by 'whatever is in the cell'? You want to return some value
even if there isn't a match? If so, the value from what cell?

To be clear, can S3 match any value in A3:A400? Or does it have to match A3?
The answer to this question may make a different approach to the one I am
taking a better option.

tj