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

 

[T. Valko]

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.

Here's the result I get using this formula:

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

Book1
	A	B
2	apple	apple
3	mango	pear
4	pear	orange
5	apple2	_
6	apples	_
7	peach	_
8	peachs	_
9	anything	_
Sheet1

Here's how to apply it...

Select the *entire* range A2:A9 starting from cell A2.
Cell A2 will be the active cell. The active cell is the
one cell in the selected range that is not shaded. The
formula will be relative to the active cell.

• Goto the Home tab>Styles>Conditional Formatting>Manage rules>New rule>Use a formula to determine which cells to format
• Enter this formula in the box below:
  =OR(ISNUMBER(SEARCH(B$2:B$4,A2)))
• Click the Format button
• Select the desired style(s)
• OK out