Using Vlookup in a string search of a cell

[Ralph Heidecke]

I want to search a cell in a report where supervisors report staffing
actions. When overtime is worked they may note it as O/T, OT or ot. I was
wondering if it is possible to put these values in a table then use that
table to search the cell in the form something like

= IF(ISERROR(SEARCH(Vlookup(cell, table, reference),$C3,1)),FALSE,TRUE)

where C3 may contain something like "J Blow 800 - 400/ 400- 600 O/T"

to return true if one of the strings in the table is found or false if it is
not found.

Or is there a better way to do this?

thanks

 

[Harlan Grove]

Ralph Heidecke wrote...

I want to search a cell in a report where supervisors report staffing
actions. When overtime is worked they may note it as O/T, OT or ot. I was
wondering if it is possible to put these values in a table then use that
table to search the cell in the form something like

=IF(ISERROR(SEARCH(Vlookup(cell, table, reference),$C3,1)),FALSE,TRUE)

where C3 may contain something like "J Blow 800 - 400/ 400- 600 O/T"

to return true if one of the strings in the table is found or false if it is
not found.

Or is there a better way to do this?

First, since you're using SEARCH, there's no difference between OT and
ot, so only 2 things to seek. The way you do that is to use an array
constant as the 1st argument to SEARCH,

SEARCH({"O/T","OT"},$C3)

That'll return a 2-entry array with each entry either #VALUE! if there
were no instances of the particular search string in the cell value or
a number if there were. Wrap the SEARCH call in a COUNT call, and COUNT
will return 0 if there were no instances of either search string or >0
if there were. So you can replace your formula above with

=COUNT(SEARCH({"O/T","OT"},$C3))>0