How to highlight the last cell based on other column value

[Khalid0090]

Hello,

I want to highlight the last value in column B based on the name of the company and if the last value for the company is 0 or empty, no highlight will be done. I tried to use a formula in conditional formatting but with no success. (See below the table and the formula I used)

Conditional Formatting formula: 

=IF($B$2:$B$11<>"",IF(LOOKUP(2,1/SEARCH($A2,$A$2:$A$11),$B$2:$B$11)>0,LOOKUP(2,1/SEARCH($A2,$A$2:$A$11),ROW($B$2:$B$11))))

Example:

In A2 the value is Apple. I want the conditional formatting to check for the value "Apple" (And this will be done for all the values in column A) and get the last value (in A5) and highlight the value in column B (144). When the last value is equal 0, all the highlights will disappear. But if the last value is empty in column B, it will look for another cell with a value and if there is no cell with a value, no highlight will be done.

I hope my request is clear, and thank you for your effort.

[#2 · Accepted solution]

@Khalid0090 

Does this conditional formatting formula do what you want?

=AND(ISERROR(MATCH($A2,$A3:$A10000,0)),$B2>0)

[#3]

Yes, it worked! Thanks a lot.