rebook | A collection of tools for cleaning up book scans | Development Tools library

First of all, I recomend you to always paste your Json in a viewer. This way you can easily visualize file structure.

Then, saving your provided sample as file.json, in just a few lines of code you get it:

For the record (in case someone would find it helpful), the combined version is:

You are not using the correct service endpoint. The documentation you linked to shows the endpoint URL: https://gateway-a.watsonplatform.net/visual-recognition/api

You are using https://gateway.watsonplatform.net/visual-recognition/api

Your formula is unpenetratable. For one thing, we don't know where your named range "Confirmed" is located, for another SEARCH("Event",AO126) will return a number whether AO126 holds "No Value Ref - Event" or just "Event", not finally, your formula returns a useful result only if it throws and error and, finally, it's evaluating results of results instead of drinking straight from the mother's breast.

With that said, I would create a named range, preferably in a separate sheet dedicated to lists and hidden from sight (and interference), with the values "Zero Ticket booking", "Refund Booking", "Automatic Cancellation" and "Nagios Booking" in it. I will refer to this range by the name of Statuses here. Use this range to feed validation dropdowns from which to select cell contents in AP:AS or refer to it in formulas, like =IF(B126=0, Index(Statuses,4),"". The immediate effect of introducing this range will be to enable you to replace all SEARCH functions in your formula with equality tests, like IF(AS126=INDEX(Statuses,1),True,False)

Next up, your formula evaluates two conditions only, that is if AS126 is blank or not. Please work this logic into your formula's base: =IF(AS126="","Do one thing","Do the other"). That will dispose of all your AND conditions.

If AS126 isn't blank, this formula should work. INDEX(Statuses,(MATCH(AS126,Statuses,0))). MATCH just finds the row number in Statuses where the matching string is.

What to do if AS126 is blank is a secret you haven't disclosed, as explained above. Try and tackle the problem by the same method, however. Assign the possible values to a named range, use that range to fill cells in your worksheet and then use the result of MATCH to return a value from another range (instead of the same, as I have done above).

Assume you have "Zero Ticket Booking" in AO126. Therefore MATCH(AO126,Statuses,0) will return 1. Assume you have a named range "Output" with "A,B,C,D" in it. INDEX(Output, 1) would therefore return "A". Therefore INDEX(Output, MATCH(AO126,Statuses,0)) would also return "A". You can use this to simplify your formula.