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

1. ## 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. ## 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. ## 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

4. ## 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.

5. ## 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. ## 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

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. ## 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. ## Re: 7 nested IF statements with a SEARCH added IF(ISNUMBER(SEARCH({"INSUR","AP REFUND"}

Originally Posted by XOR LX
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

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. ## 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. ## 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

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
****************************************

11. ## 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

12. ## 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

13. ## 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. ## 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. ## 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. ## 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.

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

#### 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