Please Help with comparing columns!

Closed
Report
-
Posts
122
Registration date
Tuesday August 31, 2010
Status
Contributor
Last seen
June 20, 2016
-
Hello,

I have an excel worksheet that has two columns of information that I need to compare. For example, If any of the values in column b = a value in column a then I need it to mark an "x" in column c. If there are no matches then the cell in column c should be left blank.

I hope that makes sense.

5 replies

in column c:
=IF(A1=B1,"x","")
0
I tried the suggested solution, it didn't work. Let me be more specific. I have one worksheet that has multiple columns. The data I have to compare is in two different columns, (I have to compare the list in column D with the list in column A), if the value in column D matches a value in column A then I need an "x" placed in column C.

Here is an example of the columns:

A B C D
C5000 733.436-64 c5000
C5010 733.436-64 c5001
C5020 733.436-64 c5011
C5040 733.436-64 c5021
C5050 733.436-64 c5031
C5060 733.436-64 c5071
C5070 733.436-64 c5081
C5071 733.436-19 c5091
C5080 733.436-64 c5101

I've tried an if- match formula, and index - match formula, I don't think vlookup would work..I'm sure this can be done, I'm just not having any luck with the formulas I've tried.
0
the columns won't necessarily correspond to an adjacent cell
0
Posts
122
Registration date
Tuesday August 31, 2010
Status
Contributor
Last seen
June 20, 2016
24
Why do you think vlookup wont work?
In column c:
=IF(ISNA(VLOOKUP(D1,A:A,1,FALSE)),"",VLOOKUP(D1,A:A,1,FALSE))

This matches the 2 hits in your example and takes care of the #N/A if no match is found.
0
When I use this I get a "false-positive". Meaning, there is an "x" in a cell that does not have a corresponding match. At first it appears as if the code is working. Then I drag the formula down and it's returning incorrect data.

I didn't think vlookup would work because of the information being alphanumeric and not being consistent in case usage. Also, it seems to me that when I use a "1" for the index number it keeps reverting to returning the value for the uppermost left column.
0
Also, I tried: IF(ISNA(A2,$D$2:$D$9,1,FALSE)),"","X") - but I have the same issue. When I walked through it step by step in excel the first result was correct, but none of the rest are.
0
Posts
1
Registration date
Tuesday October 26, 2010
Status
Member
Last seen
October 26, 2010

Also, when researching vlookup and how it works it states that for table_array I have to use two or more columns of data sorted in ascending order. I only have one column of data for my table_array. Could that be what's causing it not to work properly?
0
Posts
122
Registration date
Tuesday August 31, 2010
Status
Contributor
Last seen
June 20, 2016
24
This doesn't syntax check. IF(ISNA(A2,$D$2:$D$9,1,FALSE)),"","X")

The solution I gave works by matching the value in column D agaisnt all values in column A. I used 1 to return the first column seeing as there is only one column in the search.
C5000 and C5071 are found.
Are you dragging the formula down through all the rows? i.e. row 2 should be D2 row 3 D3 etc. Maybe we need more data to test with.
0

Hi there,

Please have the problem solved by getting through the below instructions.

https://ccm.net/faq/5278-how-to-compare-two-workbooks-worksheets

Thank you.
-1
I tried the suggested solution, it didn't work. Let me be more specific. I have one worksheet that has multiple columns. The data I have to compare is in two different columns, (I have to compare the list in column D with the list in column A), if the value in column D matches a value in column A then I need an "x" placed in column C.

Here is an example of the columns:

A B C D
C5000 733.436-64 c5000
C5010 733.436-64 c5001
C5020 733.436-64 c5011
C5040 733.436-64 c5021
C5050 733.436-64 c5031
C5060 733.436-64 c5071
C5070 733.436-64 c5081
C5071 733.436-19 c5091
C5080 733.436-64 c5101


I've tried an if- match formula, and index - match formula, I don't think vlookup would work..I'm sure this can be done, I'm just not having any luck with the formulas I've tried.
0
for clarification, since the data above squished together, A coressponds to the C5000 column, B corresponds to the 733.436-64 column, the C column is empty, and D corresponds to the c5000 column.

Thank you!
0