+ Reply to Thread
Results 1 to 15 of 15

Using nested IFS to enter text based on partial text in another cell

  1. #1
    Registered User
    Join Date
    03-29-2009
    Location
    Oregon, USA
    MS-Off Ver
    Excel 2016
    Posts
    48

    Using nested IFS to enter text based on partial text in another cell

    I'm trying to enter a full name (& additional text) in cell based on a formula that tests for the names initials listed in another cell (G4) - number of names in use is somewhat limited so I'm not worried about multiple matches.

    Although I searched for something more compact, (e.g. =LOOKUP(A1, {"Cat","Dog","Mouse"}, {"Purrs","Barks","Squeaks"}) but nothing seems to work.

    I could use list of names to select from on another worksheet if that would work better then typing them all inside the formula.

    The formula below is the closest I came to working. It only works if G4 contains "bs", if it contains any of the other options (jb, mh) I get a #VALUE! error. When I show calculation steps it create the #VALUE! during the SEARCH for the 1st missing item (bs).

    =IFS(SEARCH("bs",G4),"Bob Seger",SEARCH("jb",G4),"Jackson Brown",SEARCH("mh",G4),"Music Hall")&" - Phone Consult"

    My understanding of the IFS statement is that if it's FALSE it would just move on to the next logical test until one is satisfied...what am I missing?

  2. #2
    Forum Moderator AliGW's Avatar
    Join Date
    08-10-2013
    Location
    Retired in Ipswich, Suffolk, but grew up in Sawley, Derbyshire (England)
    MS-Off Ver
    MS 365 Subscription Insider Beta Channel v. 2404 (Windows 11 22H2 64-bit)
    Posts
    79,401

    Re: Using nested IFS to enter text based on partial text in another cell

    The SEARCH function returns a number, but if it can’t find the value, it errors out.

    There are instructions at the top of the page explaining how to attach your sample workbook.
    Ali


    Enthusiastic self-taught user of MS Excel who's always learning!
    Don't forget to say "thank you" in your thread to anyone who has offered you help.
    You can reward them by clicking on * Add Reputation below their user name on the left, if you wish.

    Forum Rules (updated August 2023): please read them here.

  3. #3
    Registered User
    Join Date
    03-29-2009
    Location
    Oregon, USA
    MS-Off Ver
    Excel 2016
    Posts
    48

    Re: Using nested IFS to enter text based on partial text in another cell

    OK will try posting sample. There is a bit more going on than original question.

    I am using sheet name to auto-populate G4 with a ID# once date is entered.

    ID# is also related to elapsed time from start date to current date.

    Since sheet name has the initials of client it shows up in ID# in G4.

    I want to extract those initials and auto-fill the client name with additional text in C7.
    Attached Files Attached Files

  4. #4
    Forum Moderator AliGW's Avatar
    Join Date
    08-10-2013
    Location
    Retired in Ipswich, Suffolk, but grew up in Sawley, Derbyshire (England)
    MS-Off Ver
    MS 365 Subscription Insider Beta Channel v. 2404 (Windows 11 22H2 64-bit)
    Posts
    79,401

    Re: Using nested IFS to enter text based on partial text in another cell

    OK - on another sheet, that can be hidden, set up a lookup table - initials on the left and names on the right.

    Then this:

    =VLOOKUP(MID(G4,FIND("-",G4)+1,2),lookup_array,2,0)&" - Phone Consult"

    e.g.

    =VLOOKUP(MID(G4,FIND("-",G4)+1,2),$J$4:$K$6,2,0)&" - Phone Consult"

    Excel 2016 (Windows) 32 bit
    B
    C
    D
    E
    F
    G
    H
    I
    J
    K
    4
    Bill To: Invoice #: **6-BS 17 **
    BS
    Bob Seger
    5
    Address: Invoice Date: **03/07/2020
    MM
    Mickey Mouse
    6
    DD
    Donald Duck
    7
    Invoice For: Bob Seger - Phone Consult
    **Hourly Rate:
    $75.00
    ** ** **
    Sheet: 6-BS
    Attached Files Attached Files

  5. #5
    Forum Expert bebo021999's Avatar
    Join Date
    07-22-2011
    Location
    Vietnam
    MS-Off Ver
    Excel 2016
    Posts
    9,412

    Re: Using nested IFS to enter text based on partial text in another cell

    Or try:
    Please Login or Register  to view this content.
    Quang PT

  6. #6
    Forum Moderator AliGW's Avatar
    Join Date
    08-10-2013
    Location
    Retired in Ipswich, Suffolk, but grew up in Sawley, Derbyshire (England)
    MS-Off Ver
    MS 365 Subscription Insider Beta Channel v. 2404 (Windows 11 22H2 64-bit)
    Posts
    79,401

    Re: Using nested IFS to enter text based on partial text in another cell

    The reason I have gone for the lookup table approach is because (a) it's easier to maintain and (b) it doesn't need to be in any particular order. Sometimes taking an extra step when setting things up can save a lot of headaches and wasted time later.

  7. #7
    Registered User
    Join Date
    03-29-2009
    Location
    Oregon, USA
    MS-Off Ver
    Excel 2016
    Posts
    48

    Re: Using nested IFS to enter text based on partial text in another cell

    Tried spreadsheet: IFS_SEARCH AliGW.xlsx - it always shows "Bob Seger" no matter which initials I enter in worksheet tab.

    I see the array with initials J4:J6 has a formula for "BS" in J4 "=MID(G4,FIND("-",G4)+1,2)"
    The other two are just text "MM" & "DD" - not clear what's happening there, did it work for you?

  8. #8
    Registered User
    Join Date
    03-29-2009
    Location
    Oregon, USA
    MS-Off Ver
    Excel 2016
    Posts
    48

    Re: Using nested IFS to enter text based on partial text in another cell

    That worked bebo021999! I pasted it directly into my actual worksheet and it worked without any adjustments!

    I like the lookup table approach also, just haven't got it working yet.

    Thank you everyone so much - you rock!

  9. #9
    Forum Moderator AliGW's Avatar
    Join Date
    08-10-2013
    Location
    Retired in Ipswich, Suffolk, but grew up in Sawley, Derbyshire (England)
    MS-Off Ver
    MS 365 Subscription Insider Beta Channel v. 2404 (Windows 11 22H2 64-bit)
    Posts
    79,401

    Re: Using nested IFS to enter text based on partial text in another cell

    Have you created your lookup list? Change MM and DD to the real initials that you have in your tab names.

    Ignore the formula in J4 - that should just be BS (it was where I tested my formula).

    Yes, it works here.

    You would be well-served to learn my approach, as it will be beneficial to you in the future and far easier to maintain as your number of tabs grows.

  10. #10
    Forum Moderator AliGW's Avatar
    Join Date
    08-10-2013
    Location
    Retired in Ipswich, Suffolk, but grew up in Sawley, Derbyshire (England)
    MS-Off Ver
    MS 365 Subscription Insider Beta Channel v. 2404 (Windows 11 22H2 64-bit)
    Posts
    79,401

    Re: Using nested IFS to enter text based on partial text in another cell

    Attached is an amended version with the lookup data on a separate tab to show you what I mean - this tab can be hidden except when being edited.
    Attached Files Attached Files

  11. #11
    Forum Moderator AliGW's Avatar
    Join Date
    08-10-2013
    Location
    Retired in Ipswich, Suffolk, but grew up in Sawley, Derbyshire (England)
    MS-Off Ver
    MS 365 Subscription Insider Beta Channel v. 2404 (Windows 11 22H2 64-bit)
    Posts
    79,401

    Re: Using nested IFS to enter text based on partial text in another cell

    If that takes care of your original question, please select Thread Tools from the menu link above and mark this thread as SOLVED.

  12. #12
    Registered User
    Join Date
    03-29-2009
    Location
    Oregon, USA
    MS-Off Ver
    Excel 2016
    Posts
    48

    Re: Using nested IFS to enter text based on partial text in another cell

    OK, that worked AliGW! That is a great way to set it up for future growth, I like it!

    Again, thank you all for your Excel-ant help with 2 great SOLVES!

  13. #13
    Forum Moderator AliGW's Avatar
    Join Date
    08-10-2013
    Location
    Retired in Ipswich, Suffolk, but grew up in Sawley, Derbyshire (England)
    MS-Off Ver
    MS 365 Subscription Insider Beta Channel v. 2404 (Windows 11 22H2 64-bit)
    Posts
    79,401

    Re: Using nested IFS to enter text based on partial text in another cell

    No worries. Please mark the thread as solved.

  14. #14
    Registered User
    Join Date
    03-29-2009
    Location
    Oregon, USA
    MS-Off Ver
    Excel 2016
    Posts
    48

    Re: Using nested IFS to enter text based on partial text in another cell

    Done!
    You Excel experts amaze me.
    I can visualize what I WANT to happen, you guys MAKE it happen...it's almost like magic. This forum is THE BEST!
    ...and thanks for the extra hints along the way to make an infrequent flyer's trip easier.
    (Reputation added for both solutions)

  15. #15
    Forum Moderator AliGW's Avatar
    Join Date
    08-10-2013
    Location
    Retired in Ipswich, Suffolk, but grew up in Sawley, Derbyshire (England)
    MS-Off Ver
    MS 365 Subscription Insider Beta Channel v. 2404 (Windows 11 22H2 64-bit)
    Posts
    79,401

    Re: Using nested IFS to enter text based on partial text in another cell

    Thanks so much - have a Happy Independence Day!

+ 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. [SOLVED] LOOKUP Value Based on Partial Text available in a Cell
    By Rajeshkumar R in forum Excel Formulas & Functions
    Replies: 4
    Last Post: 01-21-2017, 07:41 AM
  2. Replies: 4
    Last Post: 11-11-2015, 12:05 PM
  3. [SOLVED] Help using VBA to highlight a partial column based on text in a cell out of the range
    By JTKEXCEL in forum Excel Programming / VBA / Macros
    Replies: 8
    Last Post: 04-20-2013, 04:46 AM
  4. [SOLVED] Conditional formating based on text in 2 columns (but only partial text!)
    By Icehockey44 in forum Excel Formulas & Functions
    Replies: 1
    Last Post: 12-19-2012, 06:26 AM
  5. Text Matching based on partial text
    By LKluger in forum Excel Programming / VBA / Macros
    Replies: 0
    Last Post: 07-20-2011, 04:04 PM
  6. matching partial text within one cell to partial text within another
    By Solstice in forum Excel - New Users/Basics
    Replies: 4
    Last Post: 03-03-2010, 09:13 PM
  7. Deleting rows based upon partial cell text
    By mjwillyone in forum Excel Programming / VBA / Macros
    Replies: 3
    Last Post: 06-17-2008, 10:29 PM

Tags for this Thread

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