MrExcel Publishing
Your One Stop for Excel Tips & Solutions

Assign value based on another


Posted by Allision on December 07, 2001 2:37 PM

I want to assign a cells values based on whether or not another "contains" a certain string.

For example:
If cell in H:H contains "Dev" then 17 Else
If cell in H:H contains "Pro" then 11 Else
etc.

I'm thinking a select statement would work, but am not certain how to write it.

P.S. I say "contains" Dev because the cell in question could have a value or Dev or Developer, or Learning Developer, etc


Posted by IML on December 07, 2001 2:46 PM

You could use
=IF(ISNUMBER(FIND("dev",LOWER(A1))),17,"ELSE")

for finding dev in cell A1. Good luck.

Posted by IML on December 07, 2001 2:54 PM

save a few key strokes with ...

=IF(ISNUMBER(SEARCH("dev",A1)),17,"else")

Posted by Aladin Akyurek on December 07, 2001 2:54 PM

If VBA is not per se required, one can use:

=IF(SUMPRODUCT(ISNUMBER(SEARCH("Dev",H1:H100))+0),17,"")
=IF(SUMPRODUCT(ISNUMBER(SEARCH("Pro",H1:H100))+0),11,"")

where ELSE is set to "".

The range H1:H100 must be adjusted to the situation (H:H is not allowed in this formula).

Aladin