Text queries within nested IF formulas to provide required output

[marky385]

Attempting a formula which is quite possibly beyond my excel knowledge!
I have a cell which showes either 3 options - Full Disposal, No Disposal, Part Disposal. I am attempting to check further cells depending on the initial value of the 'Disposals' cell.

Formula -

'=IF(AND(ISNUMBER(SEARCH("Full Disposal",X42)),V42<0),"Error",IF(AND(ISNUMBER(SEARCH("No Disposal",X42)),V42<0),"Error",IF(AND(ISNUMBER(SEARCH("Part Disposal",X42)),V42<0,U42<0),"Error","Okay")

If cell contains 'Full Disposal' & other query is true, show 'error', else check for other two types of disposal rinse and repeat.

I think where i am going wrong is with regards to whethther the ISNUMBER(SEARCH) is providing the correct response to work?

Any help much appreciated!
Thanks

[zbor]

Your post does not comply with Rule 1 of our Forum RULES. Your post title should accurately and concisely describe your problem, not your anticipated solution.

Use terms appropriate to a Google search. Poor thread titles, like Please Help, Urgent, Need Help, Formula Problem, Code Problem, and Need Advice will be addressed according to the OP's experience in the forum: If you have less than 10 posts, expect (and respond to) a request to change your thread title. If you have 10 or more posts, expect your post to be locked, so you can start a new thread with an appropriate title.

To change a Title go to your first post, click EDIT then Go Advanced and change your title, if 2 days have passed ask a moderator to do it for you.

(This thread should receive no further responses until this moderation request is fulfilled, as per Forum Rule 7)

[marky385]

Apologies, updated!

[shukla.ankur281190]

Hi Marky,

Search formula will find if there is anything will match according to that text means "disposal" world are always it will search and will give you the destination number. Isnumber function will convert it in to TRUE then all the conditions where you mentioned "disposal" will search and revert number.

If you could share you spreadsheet. It will very easy to give you valuable results.

[marky385]

Excel Test.xlsx

Find attached clip of excel.

Where i have it returning the text value in column U, dependent on that answer i would like to do further checks in column V.

Where column U shows 'No Disposal' I would like it to test if column T is <0 and if so return "Incorrect".
Where it shows 'Full disposal' I would like to test that column S is <0 and similarly if so return "incorrect".

Hope this makes sense.
Thanks for your previous response.

[shukla.ankur281190]

Try V11=IF(AND(U11="No Disposal",T11<=0),"Error",IF(AND(U11="Full Disposal",S11<=0),"Error",IF(AND(U11="Part Disposal",T11>=0,S11>=0),"Error","okay")))

Please Login or Register  to view this content.

Hope this will help.

[marky385]

Thanks Ankur that seems to work great.
As usual simple > attempting to have a complex forumla.

Many Thanks

[shukla.ankur281190]

Glad it help you. Please mark as it solved .