+ Reply to Thread
Results 1 to 16 of 16

7 nested IF statements with a SEARCH added IF(ISNUMBER(SEARCH({"INSUR","AP REFUND"}

  1. #1
    Registered User
    Join Date
    06-12-2013
    Location
    london
    MS-Off Ver
    Excel 2003
    Posts
    18

    7 nested IF statements with a SEARCH added IF(ISNUMBER(SEARCH({"INSUR","AP REFUND"}

    Afternoon all

    i have a formula (below) which as part of a larger spreadsheet looks up the value in a cell to the left (ROW G) and gives me a different value depending on whats found

    in essence its an upload spreadsheet we use to move money around different accounts and if the money is for a taxi it comes out of one account / good will gesture another etc etc

    i actually have 10 different accounts but can only nest 7 =IF's together so have dropped the 3 that are rarely used


    heres an example of some of the accounts just so you can see how my formula is working

    TAXI 21506
    Good CONDITION bonus 23150
    FAMILIARISATION LESSONS 54045
    RETURNERS AWARD 23160

    if the money to be moved is for "insurance" or "AP REFUND" then the account number will be in the cell above (ROW I)

    Please Login or Register  to view this content.

    the part where i am stumbling is ------ ({"INSUR","AP REFUND"},

    i can get a result if it finds "INSUR" but not "AP REFUND"

    if i swap them around i get a result for "AP REFUND" but not "INSURE"

    many thanks

    mike

    PS if you are going to suggest a different / better way of doing this i am open to that but i would also like to know where im going wrong on this version for future reference - thanks

  2. #2
    Registered User
    Join Date
    06-12-2013
    Location
    london
    MS-Off Ver
    Excel 2003
    Posts
    18

    Re: 7 nested IF statements with a SEARCH added IF(ISNUMBER(SEARCH({"INSUR","AP REFUND"}

    just realised how awkward that is to veiw the formula so try this view

    =IF(ISNUMBER(SEARCH("*TAXI*",G36,1)),21506,IF(ISNUMBER(SEARCH("*CONDITION*",G36,1)),23150,IF(ISNUMBER(SEARCH("*FAMIL*",G36,1)),54045,IF(ISNUMBER(SEARCH("*RETURN*",G36,1)),23160,IF(ISNUMBER(SEARCH("*71120*",G36,1)),71120,IF(ISNUMBER(SEARCH({"INSUR","AP REFUND"},G36,1)),I35,IF(G36="","",52130)))))))

    mike

  3. #3
    Forum Guru XOR LX's Avatar
    Join Date
    04-18-2013
    Location
    Turin, Italy
    MS-Off Ver
    Office 365
    Posts
    7,745

    Re: 7 nested IF statements with a SEARCH added IF(ISNUMBER(SEARCH({"INSUR","AP REFUND"}

    Hi,

    You can work around the nested limit with e.g.:

    =IF(G36="",52130,IF(ISNUMBER(LOOKUP(7^7,SEARCH({"INSUR","AP REFUND"},G36))),I35,LOOKUP(8^8,SEARCH({"*TAXI*","*CONDITION*","*FAMIL*","*RETURN*","*71120*"},G36),{"21506","23150","54045","23160","71120"})))

    to which you can add several more criteria if you wish (providing the returns are static, and not cell references (hence why INSUR and AP REFUND have been dealt with in a separate clause)).

    Regards
    Last edited by XOR LX; 12-09-2013 at 11:06 AM.
    Click * below if this answer helped

    Advanced Excel Techniques: http://excelxor.com/

  4. #4
    Forum Guru MarvinP's Avatar
    Join Date
    07-23-2010
    Location
    Woodinville, WA
    MS-Off Ver
    Office 365
    Posts
    16,044

    Re: 7 nested IF statements with a SEARCH added IF(ISNUMBER(SEARCH({"INSUR","AP REFUND"}

    Instead of a multi IF formula, you should use a Lookup Table. You would list all those types of expenses and in the second row of the Lookup Table have the account it is coming from. Then use a simple VLookup formula.

    Watch http://www.youtube.com/watch?v=981MeKFA4W8 for some examples
    One test is worth a thousand opinions.
    Click the * Add Reputation below to say thanks.

  5. #5
    Registered User
    Join Date
    06-12-2013
    Location
    london
    MS-Off Ver
    Excel 2003
    Posts
    18

    Re: 7 nested IF statements with a SEARCH added IF(ISNUMBER(SEARCH({"INSUR","AP REFUND"}

    many thanks both

    as always simple is the best

    i will give both options a try and post my findings

    regards
    mike

  6. #6
    Registered User
    Join Date
    06-12-2013
    Location
    london
    MS-Off Ver
    Excel 2003
    Posts
    18

    Re: 7 nested IF statements with a SEARCH added IF(ISNUMBER(SEARCH({"INSUR","AP REFUND"}

    Afternoon XOR LX

    i have been using your formula to better some of my other spreadsheets so thanks very much but it is not quite right for my problem this post was created for but very close

    the issue is that if cell G36 is blank i need no value returned

    if G36 is not blank AND none of the other searches are met then i need 52130


    just to give backround 52130 is the DEFAULT account where many many different "expenses" come from but there are 9 or 10 exceptions ie "TAXI" " INSURANCE" "FAMIL" ,"CONDITION" etc that have there own accounts

    so i need to show the cell above (I35) for AP REFUND or INSURANCE and the below accounts for the others
    TAXI 21506
    Good CONDITION bonus 23150
    FAMILIARISATION LESSONS 54045
    RETURNERS AWARD 23160

    if none of these accounts are to be used then the DEFAULT 52130 account will be used....

    i have tried removing "=IF(G36="",52130,IF...." from the begining and manipulating the formula but i seem to be making a right mess of something that seemed quite simple

    can you help please

    also i didnt quite understand the 7^7 and 8^8 part of the formula and google for once couldnt help
    what are these called or what do they do??

    Bests

    Mike

  7. #7
    Forum Guru XOR LX's Avatar
    Join Date
    04-18-2013
    Location
    Turin, Italy
    MS-Off Ver
    Office 365
    Posts
    7,745

    Re: 7 nested IF statements with a SEARCH added IF(ISNUMBER(SEARCH({"INSUR","AP REFUND"}

    Apologies. It's not easy dissecting a formula like that without the actual spreadsheet. Perhaps:

    =IF(G36="","",IF(ISNUMBER(LOOKUP(7^7,SEARCH({"INSUR","AP REFUND"},G36))),I35,IF(ISNUMBER(LOOKUP(8^8,SEARCH({"*TAXI*","*CONDITION*","*FAMIL*","*RETURN*","*71120*"},G36),{"21506","23150","54045","23160","71120"})),LOOKUP(8^8,SEARCH({"*TAXI*","*CONDITION*","*FAMIL*","*RETURN*","*71120*"},G36),{"21506","23150","54045","23160","71120"}),52130)))

    Regards

  8. #8
    Registered User
    Join Date
    06-12-2013
    Location
    london
    MS-Off Ver
    Excel 2003
    Posts
    18

    Re: 7 nested IF statements with a SEARCH added IF(ISNUMBER(SEARCH({"INSUR","AP REFUND"}

    Quote Originally Posted by XOR LX View Post
    Apologies. It's not easy dissecting a formula like that without the actual spreadsheet. Perhaps:

    =IF(G36="","",IF(ISNUMBER(LOOKUP(7^7,SEARCH({"INSUR","AP REFUND"},G36))),I35,IF(ISNUMBER(LOOKUP(8^8,SEARCH({"*TAXI*","*CONDITION*","*FAMIL*","*RETURN*","*71120*"},G36),{"21506","23150","54045","23160","71120"})),LOOKUP(8^8,SEARCH({"*TAXI*","*CONDITION*","*FAMIL*","*RETURN*","*71120*"},G36),{"21506","23150","54045","23160","71120"}),52130)))

    Regards

    thanks for the reply

    im on the same wavelength as i had tried exactly the same formula but it was not working for me

    i also just tried

    =IF(G36="","",IF(ISNUMBER(LOOKUP(7^7,SEARCH({"INSUR","AP REFUND"},G36))),I35,IF(ISNUMBER(SEARCH({"*TAXI*","*COND*","*FAMIL*","*RETURN*","*71120*"},G36)),LOOKUP(8^8,SEARCH({"*TAXI*","*COND*","*FAMIL*","*RETURN*","*71120*"},G36),{21506,23150,54045,23160,71120}),52130)))

    this works for "INSUR","AP REFUND, "*TAXI*" but anything after that reverts back to 52130 so
    "*COND*","*FAMIL*","*RETURN*","*71120*" ---- and any others i may add in future are not being picked up

    this is a problem i had originally where only the first SEARCH { , , , } term could be found and the others are ignored

    all other variations i try give me #value and i lose some more hair off my head

    i feel like im a very simple explanation away like a missing comma or argument back to front from it being right.

    any help appreciated

    thanks

    mike

  9. #9
    Forum Guru XOR LX's Avatar
    Join Date
    04-18-2013
    Location
    Turin, Italy
    MS-Off Ver
    Office 365
    Posts
    7,745

    Re: 7 nested IF statements with a SEARCH added IF(ISNUMBER(SEARCH({"INSUR","AP REFUND"}

    I think it's now a very appropriate time for you to post a workbook - this theoretical guesswork could go on for some time without one!

    Obviously remove/replace any sensitive/confidential information in there first, if necessary.

    Regards

  10. #10
    Registered User
    Join Date
    06-12-2013
    Location
    london
    MS-Off Ver
    Excel 2003
    Posts
    18

    Re: 7 nested IF statements with a SEARCH added IF(ISNUMBER(SEARCH({"INSUR","AP REFUND"}

    ok sorry for delay i had to remove alot of sensitive information and backround data

    please find attached

    as before G36 downward is where im testing from and i would copy the formulae into every other cell above and below

    this would be used tens of thousands of times throughout the year moving a couple of £billion around the business so it will improve speed / accuracy no end

    many thanks

    mike




    ******************************************
    PLEASE NOTE I HAVE REMOVED THE ORIGINAL FILE AS THE DATA CONTAINED WAS DEEMED AS SENSITIVE
    ****************************************
    Last edited by michaelproctor001; 12-10-2013 at 02:12 PM.

  11. #11
    Forum Guru XOR LX's Avatar
    Join Date
    04-18-2013
    Location
    Turin, Italy
    MS-Off Ver
    Office 365
    Posts
    7,745

    Re: 7 nested IF statements with a SEARCH added IF(ISNUMBER(SEARCH({"INSUR","AP REFUND"}

    Ok, correction to formula in I36:

    IF(G36="","",IF(ISNUMBER(LOOKUP(7^7,SEARCH({"INSUR","AP REFUND"},G36))),I35,IF(ISNUMBER(LOOKUP(8^8,SEARCH({"*TAXI*","*COND*","*FAMIL*","*RETURN*","*71120*"},G36))),LOOKUP(8^8,SEARCH({"*TAXI*","*COND*","*FAMIL*","*RETURN*","*71120*"},G36),{21506,23150,54045,23160,71120}),52130)))

    Can you test and get back to me?

    Regards
    Last edited by XOR LX; 12-10-2013 at 12:29 PM.

  12. #12
    Forum Guru XOR LX's Avatar
    Join Date
    04-18-2013
    Location
    Turin, Italy
    MS-Off Ver
    Office 365
    Posts
    7,745

    Re: 7 nested IF statements with a SEARCH added IF(ISNUMBER(SEARCH({"INSUR","AP REFUND"}

    Hang on - thought something was wrong.

    My 'correction' is not a correction at all - but precisely the same formula I gave you in post #7!

    Why is the version you have in the attached not the same as the solution I posted (you've omitted the LOOKUP between ISNUMBER and SEARCH)?

    Regards
    Last edited by XOR LX; 12-10-2013 at 12:34 PM.

  13. #13
    Registered User
    Join Date
    06-12-2013
    Location
    london
    MS-Off Ver
    Excel 2003
    Posts
    18

    Re: 7 nested IF statements with a SEARCH added IF(ISNUMBER(SEARCH({"INSUR","AP REFUND"}

    thanks the last one worked a treat

    the version in post seven is very slightly different and does not work i just double checked

    heres how it worked out

    POST #7
    =IF(G36="","",IF(ISNUMBER(LOOKUP(7^7,SEARCH({"INSUR","AP REFUND"},G36))),I35,IF(ISNUMBER(LOOKUP(8^8,SEARCH({"*TAXI*","*CONDITION*","*FAMIL*","*RETURN*","*71120*"},G36),{"21506","23150","54045","23160","71120"})),LOOKUP(8^8,SEARCH({"*TAXI*","*CONDITION*","*FAMIL*","*RETURN*","*71120*"},G36),{"21506","23150","54045","23160","71120"}),52130)))

    ******************

    My Attempt to make post seven formula work for me
    =IF(G36="","",IF(ISNUMBER(LOOKUP(7^7,SEARCH({"INSUR","AP REFUND"},G36))),I35,IF(ISNUMBER(SEARCH({"*TAXI*","*COND*","*FAMIL*","*RETURN*","*71120*"},G36)),LOOKUP(8^8,SEARCH({"*TAXI*","*COND*","*FAMIL*","*RETURN*","*71120*"},G36),{21506,23150,54045,23160,71120}),52130)))
    as you can see i removed the first result vector as i was only asking IF a value was to be found and not to actually display it hence SEARCH instead of LOOKUP. Then IF value was found to do a LOOKUP but if not found display 52130
    i had also removed "" from the results vector so it displayed a number rather than TEXT

    ******************

    and finally you adjusted this version to make it work by adding the original LOOKUP back in but kept the bit i adjusted ([result vector] removed)
    IF(G36="","",IF(ISNUMBER(LOOKUP(7^7,SEARCH({"INSUR","AP REFUND"},G36))),I35,IF(ISNUMBER(LOOKUP(8^8,SEARCH({"*TAXI*","*COND*","*FAMIL*","*RETURN*","*71120*"},G36))),LOOKUP(8^8,SEARCH({"*TAXI*","*COND*","*FAMIL*","*RETURN*","*71120*"},G36),{21506,23150,54045,23160,71120}),52130)))


    anyway we have a working spreadsheet now and ive learnt a few new functions that will help no end with enhancing past and future projects

    many many thanks again

    mike

  14. #14
    Forum Guru XOR LX's Avatar
    Join Date
    04-18-2013
    Location
    Turin, Italy
    MS-Off Ver
    Office 365
    Posts
    7,745

    Re: 7 nested IF statements with a SEARCH added IF(ISNUMBER(SEARCH({"INSUR","AP REFUND"}

    Ah, of course! Apologies. What on earth was I thinking back in post #7?

    Oh well, glad we got there in the end, and best of luck with the project (and I'll happily take a cut of that £2 billion you'll now save, of course )

    Regards

  15. #15
    Registered User
    Join Date
    06-12-2013
    Location
    london
    MS-Off Ver
    Excel 2003
    Posts
    18

    Re: 7 nested IF statements with a SEARCH added IF(ISNUMBER(SEARCH({"INSUR","AP REFUND"}

    wooaaahhh there..... not save money but change the way we move it around the company

    but any efficiencies that save us money will help, and as a not for profit company any money saved /profit made goes back into our disabled scheme or to a disabled charity

    this forum has helped me / us on many occasions to do just that
    so theres our warm fuzzy feeling for the day and a good helping of karma

  16. #16
    Forum Guru XOR LX's Avatar
    Join Date
    04-18-2013
    Location
    Turin, Italy
    MS-Off Ver
    Office 365
    Posts
    7,745

    Re: 7 nested IF statements with a SEARCH added IF(ISNUMBER(SEARCH({"INSUR","AP REFUND"}

    Ok, then I'll settle for a share of that karma instead...

    All the best.

+ 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. Replies: 6
    Last Post: 02-25-2013, 03:16 AM
  2. Required "IF(ISNUMBER(SEARCH" related formula
    By nur2544 in forum Excel Formulas & Functions
    Replies: 2
    Last Post: 01-21-2013, 11:39 AM
  3. Alternative to nesting formula? "IF(ISNUMBER(SEARCH"
    By ad9051 in forum Excel Programming / VBA / Macros
    Replies: 15
    Last Post: 03-19-2012, 06:12 AM
  4. using "isnumber(search" for a range of numbers
    By redhead522 in forum Excel General
    Replies: 5
    Last Post: 07-23-2009, 12:14 PM
  5. =IF(ISERROR(SEARCH("insurance",A125,1)),"","*")
    By cynichromantique in forum Excel Formulas & Functions
    Replies: 6
    Last Post: 03-31-2006, 02:45 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