How to match a cell with text with a range of text and return own text

Dear experts,

I had searched online and could not find any results on my problem.

I need to match a fleet of vehicle and monitor the installation of cameras (for this case).

In Sheet1, is the list of the vehicles that the Operations staff will record whenever the driver reports that the camera is not working. Then, the fleet manager will look into Sheet2 to see how many more vehicles are without cameras so that he can plan or deploy the vehicle back to workshop for the installation.

The formula will be in Sheet2, column E, the logical test will column B with Sheet1 column B. So long as the vehicle number on Sheet2 B2 = Sheet1 any vehicle listed in Column B, the cell E in Sheet2 will returns the desired text.

As the vehicle number plate are not = to a specific number, therefore, could not find a solution to do the matching.

Thank you.

Originally Posted by Tinbendr

The formula in Column E is a array formula, so you must use Ctrl-Shift-Enter. {Curly brackets}

Thank you Tinbendr, even if I enter as array, it can only works on Sheet1 B2, for data that was after B2, meaning B3, B4 and B5, all not working.

Array Note working.jpg

Thank you, Ace_XL and Tinbendr. Both method works!!