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.

re: I want to check if all the codes in column A are in column ATrust me, all the codes in column A are in column A.

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

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

The

col_index_numcan be any column in thetable_array, including the left most column, which iscol_index_num1. 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.

Sorry, I meant to say check to see if all the codes in column A are in column B.

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

Ask Your Question

Weekly Poll

Would you ride in a self-driving car from Tesla?

Discuss in The Lounge

Poll History