Please Help with comparing columns!
Closed
Report
shay

RayH
RayH
 Posts
 122
 Registration date
 Tuesday August 31, 2010
 Status
 Contributor
 Last seen
 June 20, 2016
Related:
 Please Help with comparing columns!
 Excel  Compare column A & B give results in column C  HowTo  Excel
 Comparing columns and returning value ✓  Forum  Excel
 Compare columns and create list of duplicates ✓  Forum  Excel
 Comparing columns in Excel 2010 ✓  Forum  Excel
 Compare Column A to Column B and return Column C ✓  Forum  Excel
5 replies
shay
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.43664 c5000
C5010 733.43664 c5001
C5020 733.43664 c5011
C5040 733.43664 c5021
C5050 733.43664 c5031
C5060 733.43664 c5071
C5070 733.43664 c5081
C5071 733.43619 c5091
C5080 733.43664 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.
Here is an example of the columns:
A B C D
C5000 733.43664 c5000
C5010 733.43664 c5001
C5020 733.43664 c5011
C5040 733.43664 c5021
C5050 733.43664 c5031
C5060 733.43664 c5071
C5070 733.43664 c5081
C5071 733.43619 c5091
C5080 733.43664 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.
RayH
 Posts
 122
 Registration date
 Tuesday August 31, 2010
 Status
 Contributor
 Last seen
 June 20, 2016
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.
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.
shay
When I use this I get a "falsepositive". 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.
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.
shay06
 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?
RayH
 Posts
 122
 Registration date
 Tuesday August 31, 2010
 Status
 Contributor
 Last seen
 June 20, 2016
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.
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.
Blocked Profile
Hi there,
Please have the problem solved by getting through the below instructions.
https://ccm.net/faq/5278howtocomparetwoworkbooksworksheets
Thank you.
Please have the problem solved by getting through the below instructions.
https://ccm.net/faq/5278howtocomparetwoworkbooksworksheets
Thank you.
shay
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.43664 c5000
C5010 733.43664 c5001
C5020 733.43664 c5011
C5040 733.43664 c5021
C5050 733.43664 c5031
C5060 733.43664 c5071
C5070 733.43664 c5081
C5071 733.43619 c5091
C5080 733.43664 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.
Here is an example of the columns:
A B C D
C5000 733.43664 c5000
C5010 733.43664 c5001
C5020 733.43664 c5011
C5040 733.43664 c5021
C5050 733.43664 c5031
C5060 733.43664 c5071
C5070 733.43664 c5081
C5071 733.43619 c5091
C5080 733.43664 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.