Home |
Search |
Today's Posts |
#1
|
|||
|
|||
Need Help With A Find Formula
Hope this is the right forum :)
I have a spreadsheet with a long list of names that I have to go through and pick the employees that are mine. What I'm trying to do is simplify this by creating a find formula(or multiple) that will automatically populate the column where I indicate they are mine. An example. I have Column A that is where I put my name to indicate the employee belongs to me. I need to populate this with toy4x4 if they are my employee. Column B contains the employee first and last names and there is no rhyme or reason to the format of their name in this column. I can do a =find("Smith",b1) and it returns a 7 which is the starting point in the string for that last name. Problem is I have 17 employees. So I need to find "Smith", "Jones", "Brown", "White", etc I have to do this monthly and in about 15 spreasheets so I'm looking for an easy cut and paste method. Thanks in advance!! |
#2
|
|||
|
|||
Ok, this works to start off:
=IF(FIND("Smith",B1),"Mine","Not Mine") Problem is when Smith is not in the name I get #VALUE instead of "Not Mine". Then how to do nester ORs for Smith, Jones, etc. Cause this doesn't work: =IF(OR(FIND("Smith",B1),FIND("Jones",B1)),"Mine"," Not Mine") I just get #VALUE |
#3
|
|||
|
|||
try changing the order and testing for an error. I threw in the Proper
function to make sure the case is the same, otherwise I believe you will get an error. =IF(AND(ISERROR(FIND("Smith",PROPER(B1))),ISERROR( FIND("Jones",PROPER(B1)))),"Not Mine","Mine") "toy4x4" wrote: Ok, this works to start off: =IF(FIND("Smith",B1),"Mine","Not Mine") Problem is when Smith is not in the name I get #VALUE instead of "Not Mine". Then how to do nester ORs for Smith, Jones, etc. Cause this doesn't work: =IF(OR(FIND("Smith",B1),FIND("Jones",B1)),"Mine"," Not Mine") I just get #VALUE -- toy4x4 |
#4
|
|||
|
|||
Try...
=IF(OR(ISNUMBER(FIND({"Smith","Jones"},B1))),"Mine ","Not Mine") OR =IF(OR(ISNUMBER(FIND(A1:A2,B1))),"Mine","Not Mine") OR =IF(B1<"",IF(OR(ISNUMBER(FIND(A1:A2,B1))),"Mine", "Not Mine"),"") ....where A1:A2 contains your list of names. The last two formulas need to be confirmed with CONTROL+SHIFT+ENTER, not just ENTER. Also, the last formula contains an additional IF statement to return a blank when B1 is empty. Hope this helps! In article , toy4x4 wrote: Ok, this works to start off: =IF(FIND("Smith",B1),"Mine","Not Mine") Problem is when Smith is not in the name I get #VALUE instead of "Not Mine". Then how to do nester ORs for Smith, Jones, etc. Cause this doesn't work: =IF(OR(FIND("Smith",B1),FIND("Jones",B1)),"Mine"," Not Mine") I just get #VALUE |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Need formula to find and sum values in a different spreadsheet | Excel Worksheet Functions | |||
Formula to find cell with data on a row | Excel Worksheet Functions | |||
Find and Replace Formula Links | Excel Worksheet Functions | |||
Can't find the right lookup formula for this | Excel Worksheet Functions | |||
IF & VLOOKUP FORMULA | Excel Worksheet Functions |