Comparing data in 2 columns

March 17, 2009 at 16:02:37
Specs: Windows XP
I have 2 colums, with a series of codes. I want to check if all the codes in column A are in column A. When ever I try a VLOOKUP it returns the value of column B. What am I doing wrong? Is there a better formula?
Thanks.


See More: Comparing data in 2 columns

Report •


#1
March 17, 2009 at 16:15:42
re: I want to check if all the codes in column A are in column A

Trust me, all the codes in column A are in column A.


Report •

#2
March 18, 2009 at 05:28:36
The VLOOKUP formula will look in the left most column, in this case, being A and return a value from a numbered amount of columns to it's right so if you say 1 column then it will be column B, 2 would be C and so on.

So look in your VLOOKUP formula noting it cannot return a value from the same column it is looking from.

HTH
Bryan


Report •

#3
March 18, 2009 at 08:28:52
Bryan,

Maybe I'm missing your point, but I believe your understanding of the VLOOKUP function is incorrect.

The col_index_num can be any column in the table_array, including the left most column, which is col_index_num 1. 2 would be the second column in the array, not the second column away from the left most column, etc.

This will look at Column A and return the value from Column A if it is found.

=VLOOKUP(C1,A1:B5,1)

re: So look in your VLOOKUP formula noting it cannot return a value from the same column it is looking from.

As noted above, it can.


Report •

Related Solutions

#4
March 18, 2009 at 09:15:48
Sorry, I meant to say check to see if all the codes in column A are in column B.

Report •

#5
March 18, 2009 at 10:03:07
This question has been answered before.

Try This:

1. Insert a column between Column A & Column B

2. Type the following formula in cell B1:

=IF(ISERROR(MATCH(A1,$C$1:$C$150,0)),"","Match in Col C")

3. Select cells B1:B150
4. Then "Edit", select "Fill" on the Edit menu, and then click “Down”.


This will compare Column A against Column C and insert the comment
"Match in Col C" next to the name in Column A matched in Column C.

The columns do not have to be sorted, although it might be easier.
I Started at Row 1, change the formula if needed.
I used only 150 rows to check, again change if needed.

MIKE


Report •


Ask Question