none
String Search return Value RRS feed

  • Question

  • Looking to search through column A for First Name & Last Name and return a value in column B what city that person works in.

    Example Spread Sheet

    Column A

    Sales Rep Bill Smith 

    Sales Rep Jim Jones

    Sales Rep Dave Spade

    Results placed in Column B

    Sales Rep Bill Smith - Dallas

    Sales Rep Jim Jones - Boston

    Sales Rep Dave Spade - Boston

    Wednesday, February 11, 2015 3:32 PM

Answers

  • This seemed to do the trick. 

    =IF(COUNT(SEARCH({"BILL SMITH"},A1)),"DALLAS",IF(COUNT(SEARCH({"JIM JONES","DAVE SPADE"},A1)),"BOSTON"))

    • Marked as answer by David Keane Wednesday, February 11, 2015 5:39 PM
    Wednesday, February 11, 2015 5:39 PM

All replies

  • Looking to search through column A for First Name & Last Name and return a value in column B what city that person works in.

    Example Spread Sheet

    Column A

    Sales Rep Bill Smith 

    Sales Rep Jim Jones

    Sales Rep Dave Spade

    Results placed in Column B

    Sales Rep Bill Smith - Dallas

    Sales Rep Jim Jones - Boston

    Sales Rep Dave Spade - Boston


    Where are you pulling the city information for each person. In other words, how do you know that Jim Jones is in Boston. You have multiple options once that information is available
    Wednesday, February 11, 2015 3:41 PM
  • Where are you pulling the city information for each person ??
    Wednesday, February 11, 2015 3:47 PM
  • I was hoping to have something like an if statement "where I know that Smith = Dallas and Jones,Spade = Boston. There is no other column or table to reference the city information.


    Wednesday, February 11, 2015 5:07 PM
  • An example I know that works is listed below however I would like to add another if included in the one statement if possible to somehow associate  first and last name to a city....hope this makes sense!

    In Cell B2 i have

    =IF(COUNT(SEARCH({"BILL SMITH","JIM JONES","DAVE SPADE"},A1)),"DALLAS","BOSTON")

    RESULTS FOR THIS WOULD LOOK LIKE

    BILL SMITH - DALLAS

    JIM JONES - DALLAS "NEEDS TO BE BOSTON"

    DAVE SPADE - DALLAS "NEEDS TO BE BOSTON"

    Wednesday, February 11, 2015 5:21 PM
  • I was hoping to have something like an if statement "where I know that Smith = Dallas and Jones,Spade = Boston. There is no other column or table to reference the city information.


    So how do you know that <nameX> is from <city>?
    Wednesday, February 11, 2015 5:21 PM
  • I know, and would need to define in my if statement an association of name to a city. 
    Wednesday, February 11, 2015 5:31 PM
  • This seemed to do the trick. 

    =IF(COUNT(SEARCH({"BILL SMITH"},A1)),"DALLAS",IF(COUNT(SEARCH({"JIM JONES","DAVE SPADE"},A1)),"BOSTON"))

    • Marked as answer by David Keane Wednesday, February 11, 2015 5:39 PM
    Wednesday, February 11, 2015 5:39 PM
  • I know, and would need to define in my if statement an association of name to a city. 

    I don't know what your sheet is supposed to be doing so take this for what it's worth (i.e. I've been bit in the rear by similar sounding systems - plan for growth).

    Your model works if the number of reps is low but blows right up if the list gets long. I'd recommend you think about putting stuff into some kind of data structure and then use lookups to pull data as required. This makes it easy to expand the number of reps without having to re-write your formulas. Be sure to make the raw data into a table (which your formulas would reference by name) so as you add rows, the table grows and your lookups keep working.

    Wednesday, February 11, 2015 5:54 PM
  • I agree, this in sql table would allow me to do many more things. The count of names is low on my end so it does provide me with a basic solution for now. Thanks!
    Wednesday, February 11, 2015 6:05 PM