Please Help with comparing columns!
Closed
shay
-
Oct 22, 2010 at 10:41 AM
RayH Posts 122 Registration date Tuesday August 31, 2010 Status Contributor Last seen June 20, 2016 - Oct 26, 2010 at 03:17 PM
RayH Posts 122 Registration date Tuesday August 31, 2010 Status Contributor Last seen June 20, 2016 - Oct 26, 2010 at 03:17 PM
Related:
- Please Help with comparing columns!
- Display two columns in data validation list but return only one - Guide
- Excel change columns to numbers - Guide
- Google sheets right to left columns - Guide
- Excel compare two columns for partial matches - Guide
- How to convert rows into columns in notepad++ ✓ - Excel Forum
5 responses
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.
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.
RayH
Posts
122
Registration date
Tuesday August 31, 2010
Status
Contributor
Last seen
June 20, 2016
26
Oct 25, 2010 at 03:29 PM
Oct 25, 2010 at 03:29 PM
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.
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.
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
Oct 26, 2010 at 12:20 PM
Oct 26, 2010 at 12:20 PM
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
26
Oct 26, 2010 at 03:17 PM
Oct 26, 2010 at 03:17 PM
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.
Didn't find the answer you are looking for?
Ask a question
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.
Please have the problem solved by getting through the below instructions.
https://ccm.net/faq/5278-how-to-compare-two-workbooks-worksheets
Thank you.
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.
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.
