VLOOKUP a cell that does not contain specific text

Question

Currently using this formula;

=ARRAYFORMULA(VLOOKUP("*"&$W$2&"*",H1:T1000,{2,3,5,8,11},false))

which works fine when I put something into cell W2.

However, what if I want to return all rows that do not contain that value from W2, is there a way?

Answer 1

Use QUERY instead:

=QUERY(H1:T1000,"Select I, J, L Where not H contains '"&W2&"'")

Or

…not H matches '.*"&W2&".*'… 

Answer 2

Edited to include wildcard match in filter formula.

In Google Sheets this can best be accomplished using the FILTER formula. FILTER takes a range as an argument and then allows filtering operations on columns adjacent to or within the range.

In this case, to get all rows within the range H1:T1000 in which the value in the first column does not contain the value of W2 you could use the following formula:

FILTER(H1:T1000,NOT(REGEXMATCH($W$2,H1:H1000)))

Note that the formula only needs to be in one cell and the result will expand to occupy the range needed to display the result.

For Google's documentation on the FILTER formula see here.