How to count if there's a cell with certain restrictions using Vlookup?

Question

I'm finding if there at least one cell with the first character equal to "!"

Formula I have used without success:

=Vlookup("!";Left(N2:N49;1);1)

Why doesn't it work? And what do I have to change in the formula to make it work as intended?

Answer 1

VLOOKUP function - Microsoft

Use VLOOKUP, one of the lookup and reference functions, when you need to find things in a table or a range by row. For example, look up an employee's last name by her employee number, or find her phone number by looking up her last name (just like a telephone book).

You just need a count, not a lookup.

Use COUNTIF with a wildcard character.

=COUNTIF(N2:N49,"!*")

Or to make it a condition,

=COUNTIF(N2:N49,"!*")>0

Answer 2

1. I would add formula =Left(N2,1) to a new column, in this example M;
2. the formula can then be changed to =Vlookup("!",M2:M49,1,False).