confirional formatting using vlookup and wildcards

[mkayz]

Hi experts,

Got a problem wondering if anyone can help.

I have two coloumns

A B
apple apple
apple2 orrange
apple3 pair
apple
pair
etc...

I have a conditional formatting rule as

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

the above will format the cells in A if it matches the values in B

so the above will format apple and pair

Is there a way to make it also format apple2, and apple3 if there is only apple in column B.

I have tried

=NOT(ISERROR(VLOOKUP($A1 & "*",$B:$B,1,FALSE)))

and it doesnt work, completely stuck.

Thanks for your time.

MKayZ

 

[anandvarma]

i have not totally understand you problem, but any way

Try this formula for increase the count of apples

=IF(A2=A1, A2&" "&COUNTA(A$1:A1))

 

[CharlesChuckieCharles]

Try change the VLOOKUP for exact match to closest match

=NOT(ISERROR(VLOOKUP($A1 & "*",$B:$B,1,FALSE)))

to

=NOT(ISERROR(VLOOKUP($A1 & "*",$B:$B,1,TRUE)))

 

[mkayz]

Thanks for your replies

I tried

=NOT(ISERROR(VLOOKUP($A1 & "*",$B:$B,1,TRUE)))

and it doesnt work, it will format all cells in A.

anyone got any other suggestions?

 

[CharlesChuckieCharles]

Try it like

VLOOKUP($A1,$B:$B,1,TRUE) (no * required something i left in by mistake)
and you will see that Apple and Apple3 return the same result

if not check for spaces on both sides

By use of NOT and ISERROR you are converting the result to
an boolean TRUE FALSE Result

 

[mkayz]

Hi,

yes it needs to be turned into a boolean for it to work in a conditional formatting formula.

I have tried your suggestion and it doesnt work either, the result is that it will format all cells in column A, even if there is nothing simular in column B. i suppose it means it turns the result into true regardless of the result of the vlookup.

Any other ideas?

Thanks

 

[CharlesChuckieCharles]

Using your example

i used =NOT(ISNA(VLOOKUP($A2,$B:$B,1,FALSE)))

which highlighted Apple and pear


and =NOT(ISNA(VLOOKUP($A2,$B:$B,1,TRUE)))

which highlighted Apple, Apple2,Apple3 and pear

My apply's to was $A$2:$A$100

 

[mkayz]

Hi,

Thanks for your reply, I tried exactly what you did and it will highlight all values, I have no idea why it does not work for me.

When its false, it will format only the items in column B, if its true, everything in column A is formatted, even values not in B like mango, plumb, etc

I am using excel 2010, not sure if that is the reason, any other suggestions? or a setting that I am missing?

 

[T. Valko]

Hi,

Thanks for your reply, I tried exactly what you did and it will highlight all values, I have no idea why it does not work for me.

When its false, it will format only the items in column B, if its true, everything in column A is formatted, even values not in B like mango, plumb, etc

I am using excel 2010, not sure if that is the reason, any other suggestions? or a setting that I am missing?

I think you should reverse the logic. Instead of looking in column B for matches of column A, you should look in column A for matches of column B.

Like this:

=OR(ISNUMBER(SEARCH(B$2:B$4,A2)))

Based on your posted sample data all the entries in column A will be highligted.

 

[mkayz]

Hi thanks again for your help.

maybe my example data was not very clear...

A
apple
mango
pear
apple2
apples
peach
peachs
anything

B
apple
pear
orange


what is wanted is apple, pear, apple2, apples, to be formatted.

so fair i tried both, suggestions and they give strange results, not sure what i am doing wrong. please help.