Vlookup formula

[Mux99]

I'm currently using the following formula in conditional formatting:

=VLOOKUP($A1,$1:$1048576,COLUMN(),0)="MATCH"

The formula is only working if the word "MATCH" is in the row where the name in Column A is listed first. All six cells with data in the example below would need to be highlighted, but with the current formula only C2 & C4 are highlighted.

	A	B	C	D
1	Name1	HIGHLIGHT		HIGHLIGHT
2	Name2		MATCH
3	Name1	MATCH		MATCH
4	Name2		HIGHLIGHT

Thanks

 

[rehanemis]

Select the whole cells and then add that formula to conditional formatting.

 

[Mux99]

Select the whole cells and then add that formula to conditional formatting.

Formula is applied to =$1:$1048576 but only works in rows where the name in column A is listed for the first time.

In the example above "MATCH" in row 3 is not recognized as the Name is already listed in row 1. B1 & B3 and D1 & D3 should also be highlighted.

 

[rehanemis]

it is working well on my side and don't see any issue.

 

[Mux99]

I'll try to explain myself a little better as the formula is not working as I need it to.

I need to search through Column A and highlight all cells from column B onwards in the same column with the same name if it contains the word "MATCH".
Ex. C2 = MATCH so both C2 & C4 should be highlighted since they have the same name in Column A. (This currently works with my formula)

With my example B1 & B3 and D1 & D3 should all be highlighted as they all have the same name in Column A. This isn't currently working as it's only recognizing the first Name listed.

 

[Fluff]

How about

+Fluff New.xlsm
	A	B	C	D
1	Name1	HIGHLIGHT		HIGHLIGHT
2	Name2		MATCH
3	Name1	MATCH		MATCH
4	Name2		HIGHLIGHT
Data

Cells with Conditional Formatting
Cell	Condition		Cell Format	Stop If True
B1:D4	Expression	=COUNTIFS($A:$A,$A1,B:B,"MATCH")>0	text	NO

 

[Mux99]

How about

+Fluff New.xlsm
	A	B	C	D
1	Name1	HIGHLIGHT		HIGHLIGHT
2	Name2		MATCH
3	Name1	MATCH		MATCH
4	Name2		HIGHLIGHT
Data

Cells with Conditional Formatting
Cell	Condition		Cell Format	Stop If True
B1:D4	Expression	=COUNTIFS($A:$A,$A1,B:B,"MATCH")>0	text	NO

Thanks, this works but it's very slow. Each time new data is entered it loads for around 3-4 seconds.

Is there any other way this can be done?

 

[Fluff]

What range have you applied the CF to?

 

[Mux99]

What range have you applied the CF to?

I tested it on B1:Z100 and it's still taking long to load.

I actually need it to work on B1:FF1900 so hopefully this can be done another way.

 

[Fluff]

Conditional formatting is volatile, so running it on that amount of cells is not a good idea.
You would probably be better off with a macro, although that would not update automatically.