USING IF/VLOOKUP STATEMENT TO VERIFY A VALUE

Closed
Report
-
Posts
1864
Registration date
Sunday June 14, 2009
Status
Contributor
Last seen
August 7, 2021
-
Hello,

I have 2 columns in an Excel spreadsheet, Column A and Column B and I am trying to verify if the data that exists in Column B, also exists in Column A.

I know that if I put my IF statement in Column C then I can generate a yes or a no answer.

Is there a better way of ascertaining if the data thats in Column B exists in Column A,

if not can you please help me with the formula to get this to work.

Thank you,

2 replies

Posts
1864
Registration date
Sunday June 14, 2009
Status
Contributor
Last seen
August 7, 2021
804
suppose your data from row one dwon in column A and B are as follows

1 6
2 7
3 8
4 9
5 3


in C1 type this formula

=IF(ISNA(VLOOKUP(A1,$B$1:$B$100,2,0)),"no","yes")
copy c1 down you will get

1 6 no
2 7 no
3 8 yes
4 9 no
5 3 no
5
Posts
30
Registration date
Sunday August 9, 2009
Status
Member
Last seen
April 20, 2010
12
Use "CONDITIONAL FORMATING"
by select B column and use "conditional formating"
select "formula is"
=NOT(ISERROR(VLOOKUP(B1,A:A,1,0)))

and select format that you need to see when value in column B matched value in column A.

Please try...it's may be help you


Best regards,
Kaiysit Phanmakorn
kaiyasitp@gmail.com
1