CODEWITHSUNDEEP
excelvlookupconditional-formatting
Abbas
Abbas

Reputation: 5044

Vlookup to format cell in excel

I have 2 columns in my spreadsheet, both contains some numbers, column A has list of all numbers, and column B has some of the numbers from column A, now i want to highlight those numbers in column A, which are in column B here's my scenario:

Column A    Column B
20301316    20322063
20302140    20322451
20307329    20326125
20307557    20334858
20314386    20371898
20314840    30368489
20322451    30384472
20326125    30384510
20334858    30384531
20371898    30384661

Here 

20322451
20326125
20334858
20371898

should get highlighted. i used vlookup for this but for some reason it's highlighting all the numbers, here's the formula i used inside Conditional Formatting: (considering column A has values between A1:A10 and B has B1:B10)

=NOT(ISNA(VLOOKUP(B1, A1:B10, 1, 0)))

Could anyone please help me with proper formula.

Upvotes: 0

Views: 898

Answers (2)

teylyn
teylyn

Reputation: 35900

Vlookup returns a value. In this context it is not the best formula to use, especially if you use it to return the value that you are looking up.

MATCH() is a much better fit for this scenario.

=MATCH(A1,$B$1:$B$10,0)

No fuffing around with ISNA() or wrapping in NOT() required. If it's a match it returns a number and will get formatted. If it's not a match, it won't get formatted.

enter image description here

Upvotes: 2

Doug Glancy
Doug Glancy

Reputation: 27478

It looks like you got the formula a bit backwards as it's looking in column A for values from column B. Try this instead:

=NOT(ISNA(VLOOKUP(A1,$B$1:$B$10,1,FALSE)))

Also, note that I made the lookup range an absolute reference by adding dollar signs.

Really though, I'd prefer a COUNTIF formula for this, just because I think it's more obvious:

=COUNTIF($B$1:$B$10,A1)>0

Upvotes: 2

Related Questions