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

 

[Mux99]

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.

Thanks anyway but I need it to update automatically so a macro wouldn't really help. I thought there would be another workaround as I'm using the formula in the original post multiple times to collect data from another worksheet without delays.

 

[Fluff]

You can try limiting the formula to
=COUNTIFS($A$1:$A$1900,$A1,B$1:B$1900,"MATCH")>0

Not sure if it will make any difference.

 

[Mux99]

You can try limiting the formula to
=COUNTIFS($A$1:$A$1900,$A1,B$1:B$1900,"MATCH")>0

Not sure if it will make any difference.

Loading time went down to around half a second after new data is inputted so it's usable now and I have applied the formula three times for different matches. Thanks a lot.

 

[Fluff]

Glad to help & thanks for the feedback.