+ Reply to Thread
Results 1 to 8 of 8

Extremely Complex IF/SEARCH Formula

  1. 07-17-2015, 01:47 PM #1
    cowboy1388
    cowboy1388 is offline
    Registered User
    Join Date
    07-17-2015
    Location
    Akron, OH
    MS-Off Ver
    2013
    Posts
    10

    Exclamation Extremely Complex IF/SEARCH Formula

    Listed below is my formula. I do not get any errors which is great but no matter the text found, the formula spits out iPhone 4. The format of "A2" is as follow: AP4 00001 ackr AP4S 00001 ackr AP5 00001 ackr you get the picture. I'm testing with AP5 00001 ackr and still getting "iPhone 4" instead of "iPhone 5" PLEASE HELP

    =IF(SEARCH(AP4,A2),"iPhone 4",IF(SEARCH(AP4S,A2),"iPhone 4S",IF(SEARCH(AP5,A2),"iPhone 5",IF(SEARCH(AP5C,A2),"iPhone 5C",IF(SEARCH(AP5S,A2),"iPhone 5S",IF(SEARCH(AP6,A2),"iPhone 6",IF(SEARCH(AP6P,A2),"iPhone 6P","ERROR")))))))
    Reply With QuoteRegister To Reply
  2. 07-17-2015, 01:55 PM #2
    Jacc
    Jacc is offline
    Forum Expert
    Join Date
    09-01-2012
    Location
    Norway
    MS-Off Ver
    Office 365
    Posts
    2,844

    Re: Extremely Complex IF/SEARCH Formula

    It sounds like you are trying to do something like this: http://www.excelforum.com/excel-form...tatements.html

    If you post a workbook with some sample data it will be much easier to help you.
    <----- If you were helped by my posts you can say "Thank you" by clicking the star symbol down to the left

    If the problem is solved, finish of the thread by clicking SOLVED under Thread Tools
    I don't wish to leave you with no answer, yet I sometimes miss posts. If you feel I forgot you, remind me with a PM or just bump the thread.
    Reply With QuoteRegister To Reply
  3. 07-17-2015, 01:56 PM #3
    GeneralDisarray
    GeneralDisarray is offline
    Forum Expert GeneralDisarray's Avatar
    Join Date
    09-15-2011
    Location
    Pittsburgh, PA, USA
    MS-Off Ver
    Windows Excel 2016
    Posts
    1,416

    Re: Extremely Complex IF/SEARCH Formula

    Try this?

    Please Login or Register  to view this content.



    P.S.
    instead of 'error', possibly "congratulations, you bought a Samsung!"


    Last edited by GeneralDisarray; 07-17-2015 at 02:09 PM.
    Remember, saying thanks only takes a second or two. Click the star icon(*) below the post you liked, to give some Rep if you think an answer deserves it.

    Please,mark your thread [SOLVED] if you received your answer.
    Reply With QuoteRegister To Reply
  4. 07-17-2015, 01:59 PM #4
    FDibbins
    FDibbins is offline
    Administrator FDibbins's Avatar
    Join Date
    12-29-2011
    Location
    Duncansville, PA USA
    MS-Off Ver
    Excel 7/10/13/16/365 (PC ver 2310)
    Posts
    52,933

    Re: Extremely Complex IF/SEARCH Formula

    Sounds to me you want to use a vlookup(). Make a small table with your references and their meaning, then vlookup into that
    If you still have a problem, upload a small (clean) sample workbook (not a pic) of what you are working with, and what your expected outcome would look like.
    1. Use code tags for VBA. [code] Your Code [/code] (or use the # button)
    2. If your question is resolved, mark it SOLVED using the thread tools
    3. Click on the star if you think someone helped you

    Regards
    Ford
    Reply With QuoteRegister To Reply
  5. 07-17-2015, 02:00 PM #5
    cowboy1388
    cowboy1388 is offline
    Registered User
    Join Date
    07-17-2015
    Location
    Akron, OH
    MS-Off Ver
    2013
    Posts
    10

    Re: Extremely Complex IF/SEARCH Formula

    Inventory Scanning Master DO NOT TAMPER.xlsx

    Attached is the file
    Reply With QuoteRegister To Reply
  6. 07-17-2015, 02:07 PM #6
    GeneralDisarray
    GeneralDisarray is offline
    Forum Expert GeneralDisarray's Avatar
    Join Date
    09-15-2011
    Location
    Pittsburgh, PA, USA
    MS-Off Ver
    Windows Excel 2016
    Posts
    1,416

    Re: Extremely Complex IF/SEARCH Formula

    See attached.

    FDibbins is right though, a vlookup table would make this a lot easier - but for educational purposes you would be better off using a CHOOSE() for that many nested IF's (if you are not going to use a lookup table that is)
    Attached Files Attached Files
    Reply With QuoteRegister To Reply
  7. 07-17-2015, 02:09 PM #7
    cowboy1388
    cowboy1388 is offline
    Registered User
    Join Date
    07-17-2015
    Location
    Akron, OH
    MS-Off Ver
    2013
    Posts
    10

    Re: Extremely Complex IF/SEARCH Formula

    Awesome thank you!
    Reply With QuoteRegister To Reply
  8. 07-17-2015, 02:10 PM #8
    Jacc
    Jacc is offline
    Forum Expert
    Join Date
    09-01-2012
    Location
    Norway
    MS-Off Ver
    Office 365
    Posts
    2,844

    Re: Extremely Complex IF/SEARCH Formula

    You are missing the quotation marks around AP4 etc. Now Excel thinks that you are referring to the cell AP4.
    This is how I would do it:
    Formula: copy to clipboard 
    Please Login or Register  to view this content.

    Edit: Sorry forgot to mention, it is an array formula, it has to be entered with Ctrl + Shift + Enter!
    Attached Files Attached Files
    Last edited by Jacc; 07-17-2015 at 02:17 PM.
    Reply With QuoteRegister To Reply
+ Reply to Thread
« Previous Thread | Next Thread »

Thread Information

Users Browsing this Thread

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

Similar Threads

  1. Complex Search Formula
    By Nevec90 in forum Excel Formulas & Functions
    Replies: 6
    Last Post: 12-04-2014, 04:03 PM
  2. [SOLVED] Extremely slow workbook and complex formulas
    By gaker10 in forum Excel General
    Replies: 7
    Last Post: 07-28-2014, 09:13 AM
  3. Extremely Complex Data Validation
    By johnph in forum Excel Programming / VBA / Macros
    Replies: 22
    Last Post: 07-05-2012, 04:51 PM
  4. Macro Tied to Complex Nested If Statement Extremely Inefficient/Slow - HELP
    By Umann51 in forum Excel Programming / VBA / Macros
    Replies: 3
    Last Post: 10-06-2011, 04:34 PM
  5. [SOLVED] Complex Formula Solution using Search and IF
    By XP in forum Excel Programming / VBA / Macros
    Replies: 5
    Last Post: 02-01-2006, 12:25 PM
  6. Extremely complex problem: showing a value as an 'axis' on a circle
    By [email protected] in forum Excel General
    Replies: 5
    Last Post: 11-15-2005, 07:00 PM
  7. [SOLVED] Extremely complex problem: showing a value as an 'axis' on a circle
    By [email protected] in forum Excel Programming / VBA / Macros
    Replies: 5
    Last Post: 11-15-2005, 07:00 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

Forum Rules


Search Engine Friendly URLs by vBSEO 3.6.0 RC 1