+ Reply to Thread
Results 1 to 13 of 13

Formula to look at a list and enter specified text depending on which list text is found

  1. #1
    Forum Contributor
    Join Date
    05-29-2012
    Location
    Walnut, Ca
    MS-Off Ver
    Excel 2010
    Posts
    182

    Formula to look at a list and enter specified text depending on which list text is found

    Is there a way to have 3 seperate lists and have a formula look at each list. If specified text is found, depending on the list it will enter specific text.

    Example
    List 1 contains A, B, C if true enter a
    List 2 contains D, E, F if true enter b
    List 3 contains G, H, I if true enter c

    Formula is looking at cell c3 and d3, if either cell contain any of those letters (A thru I), then the formula enters the text according to which list it found it in.

  2. #2
    Forum Moderator alansidman's Avatar
    Join Date
    02-02-2010
    Location
    Steamboat Springs, CO
    MS-Off Ver
    MS Office 365 Version 2403 Win 11 Home 64 Bit
    Posts
    23,810

    Re: Formula to look at a list and enter specified text depending on which list text is fou

    =if(or (c3=A,C3=B,C3=C,D3=A,D3=B,D3=C),a,if(or (c3=D,C3=E,C3=F,D3=D,D3=E,D3=F),b,c))

    Try the above
    Alan עַם יִשְׂרָאֵל חַי


    Change an Ugly Report with Power Query
    Database Normalization
    Complete Guide to Power Query
    Man's Mind Stretched to New Dimensions Never Returns to Its Original Form

  3. #3
    Forum Expert newdoverman's Avatar
    Join Date
    02-07-2013
    Location
    Port Dover, Ontario, Canada
    MS-Off Ver
    2010
    Posts
    10,330

    Re: Formula to look at a list and enter specified text depending on which list text is fou

    I think that this might suit what you are looking for:

    Formula: copy to clipboard
    Please Login or Register  to view this content.
    <---------If you like someone's answer, click the star to the left of one of their posts to give them a reputation point for that answer.
    Ron W

  4. #4
    Forum Contributor
    Join Date
    05-29-2012
    Location
    Walnut, Ca
    MS-Off Ver
    Excel 2010
    Posts
    182

    Re: Formula to look at a list and enter specified text depending on which list text is fou

    Neither solution worked, I would like to have the formula search a list for text within the list.

    If it's too complicated, please just tell me, but what I would like is for cell D3 to search all three lists "Classified", "Tenure", and "Probationary". If Cell D3 is blank, then cell C3 to do the same thing. If D3 finds text in one of those lists to then enter the name of the list in F3 and of course if D3 was blank then if C3 finds the text, to still enter the title of the list in F3.

    So an example:

    The lists are located on the second sheet

    Example.xlsx

  5. #5
    Forum Expert newdoverman's Avatar
    Join Date
    02-07-2013
    Location
    Port Dover, Ontario, Canada
    MS-Off Ver
    2010
    Posts
    10,330

    Re: Formula to look at a list and enter specified text depending on which list text is fou

    Your file is vastly different from the first description. I have re-jigged the formula and hope that it works the way that you want.
    Attached Files Attached Files

  6. #6
    Forum Contributor
    Join Date
    05-29-2012
    Location
    Walnut, Ca
    MS-Off Ver
    Excel 2010
    Posts
    182

    Re: Formula to look at a list and enter specified text depending on which list text is fou

    Thank you, I thought about my first post and decided last night I needed it to do what I posted this morning. I took a look at the spreadsheet and when I type in "Jane" in C3 it doesn't populate the list title if F3 but it does when I type it in D3. Also, F3 is not always making D3 the primary source for data. Example I typed Christina in C3 and Probationary generated but then I typed John in D3 and it changed to "Classified" which is what I want it to do but when I typed "John" in C3 it generated "Classified" but when I entered "Christina" in D3, F3 didn't change to Probationary.

    Any suggestions?

  7. #7
    Forum Expert newdoverman's Avatar
    Join Date
    02-07-2013
    Location
    Port Dover, Ontario, Canada
    MS-Off Ver
    2010
    Posts
    10,330

    Re: Formula to look at a list and enter specified text depending on which list text is fou

    I think that this has it.....going blind looking at it.
    Attached Files Attached Files

  8. #8
    Forum Contributor
    Join Date
    05-29-2012
    Location
    Walnut, Ca
    MS-Off Ver
    Excel 2010
    Posts
    182

    Re: Formula to look at a list and enter specified text depending on which list text is fou

    This one worked like a charm but is there a way to have a formula search within the list without having to identify each cell in that list. Example: the "Classified" list is actually named Classified if you highlight cell A2:A4. The reason I ask is because the example I gave you is much shorter than my actual list. In my Tenure list I have 87 names. So if there is a way to shorten the formula that would amazing. Also for the purpose that I will sometimes have to adjust the names in the list and if I could just extend actual list vs having to add or subtract cells from the formula would be helpful. Please let me know if that is even an option.

    I really do appreciate your help on this matter. I would have never have figured it out.

  9. #9
    Forum Expert martindwilson's Avatar
    Join Date
    06-23-2007
    Location
    London,England
    MS-Off Ver
    office 97 ,2007
    Posts
    19,320

    Re: Formula to look at a list and enter specified text depending on which list text is fou

    try in f3
    =IFERROR(INDEX(Lists!$A$1:$C$1,SUMPRODUCT((Lists!$A$2:$C$100=IF(C3="",D3,C3))*COLUMN($A$1:$C1))),"")
    "Unless otherwise stated all my comments are directed at OP"

    Mojito connoisseur and now happily retired
    where does code go ?
    look here
    how to insert code

    how to enter array formula

    why use -- in sumproduct
    recommended reading
    wiki Mojito

    how to say no convincingly

    most important thing you need
    Martin Wilson: SPV
    and RSMBC

  10. #10
    Banned User!
    Join Date
    10-14-2006
    Posts
    1,211

    Re: Formula to look at a list and enter specified text depending on which list text is fou

    Quote Originally Posted by Dena View Post
    This one worked like a charm but is there a way to have a formula search within the list without having to identify each cell in that list. Example: the "Classified" list is actually named Classified if you highlight cell A2:A4. The reason I ask is because the example I gave you is much shorter than my actual list. In my Tenure list I have 87 names. So if there is a way to shorten the formula that would amazing. Also for the purpose that I will sometimes have to adjust the names in the list and if I could just extend actual list vs having to add or subtract cells from the formula would be helpful. Please let me know if that is even an option.

    I really do appreciate your help on this matter. I would have never have figured it out.
    Shorter/more elegant/and flexible.

    See the attached file.
    Attached Files Attached Files

  11. #11
    Forum Contributor
    Join Date
    05-29-2012
    Location
    Walnut, Ca
    MS-Off Ver
    Excel 2010
    Posts
    182

    Re: Formula to look at a list and enter specified text depending on which list text is fou

    Good Morning Mama, Thank you for the formula. On your result spreadsheet it looks like it works perfectly. I by no means am an expert in excel so I am hoping you could look at an example of the actual spreadsheet I am using because I added the formula to column F but it isn't working. I don't know if it matters that my actual lists do not have the equal amount of names in them. My longest list has 87 names.

    Please let me know
    Thank you

    Copy of Document Log-2013-14.xlsm

  12. #12
    Forum Expert martindwilson's Avatar
    Join Date
    06-23-2007
    Location
    London,England
    MS-Off Ver
    office 97 ,2007
    Posts
    19,320

    Re: Formula to look at a list and enter specified text depending on which list text is fou

    well i dispute TM assertion of Shorter/more elegant/and flexible.
    =IFERROR(INDEX(Lists!$A$1:$C$1,SUMPRODUCT((Lists!$A$2:$C$100=IF(C3="",D3,C3))*COLUMN($A$1:$C1))),"")
    =IFERROR(INDEX(Lists!A$1:C$1,MAX(INDEX((Lists!A$2:C$100=LOOKUP("zzz",C3:D3))*COLUMN(Lists!A$2:C$4),))),"")
    it obviously is not shorter!
    but ill fix the one you have used

    =IFERROR(INDEX(Lists!J$1:L$1,MAX(INDEX((Lists!J$2:L$87=LOOKUP("zzz",C3:D3))*COLUMN($A$1:$C$1),))),"")
    the bit in red has to be $a$1:$c$1 as COLUMN($A$1:$C1) evaluates to {1,2,3} which is the index numbers of the columns in range Lists!J$2:L$87 ie j=1 k=2 l=3

    some other things
    Williams, Debbie
    is not in the lists
    some are showing blank because of trailing spaces in names in Lists!J$2:L$87
    got to lists sheet and
    select column j
    then data tab/text to columns/choose fixed width click finish
    repeat for columns k,l,m
    that will remove any trailing spaces in the names
    also do it to col c of summer! as once spaces removed from lists previously selected names may still have spaces after them so now wont match
    Last edited by martindwilson; 07-30-2013 at 06:42 AM.

  13. #13
    Forum Contributor
    Join Date
    05-29-2012
    Location
    Walnut, Ca
    MS-Off Ver
    Excel 2010
    Posts
    182

    Re: Formula to look at a list and enter specified text depending on which list text is fou

    Thank you, works perfectly.

+ Reply to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Similar Threads

  1. can enter a text even with a validation list formula in the column
    By Elainefish in forum Excel Programming / VBA / Macros
    Replies: 1
    Last Post: 05-14-2013, 02:54 PM
  2. Replies: 1
    Last Post: 01-06-2013, 04:50 AM
  3. enter text, return a validated list
    By shivs69 in forum Excel General
    Replies: 3
    Last Post: 07-27-2010, 10:53 AM
  4. If text found in list then complete action
    By CJ944 in forum Excel Programming / VBA / Macros
    Replies: 0
    Last Post: 07-26-2010, 08:38 AM
  5. display text depending on coice from drop down list
    By Calle in forum Excel General
    Replies: 11
    Last Post: 05-28-2006, 04:15 PM

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1