Q:

How to use search function with if function

My excel sheet have bank name, IFSC code and account number.
I want to get bank name from IFSC Code. For this I've used the following formula:
=IF(SEARCH("DNB",P17),"DELHI NAGRIK SEHKARI BANK",IF(SEARCH("SBIN",P17),"STATE BANK OF INDIA",IF(SEARCH("PUNB",P17),"PUNJAB NATIONAL BANK",IF(SEARCH("CNRB",P17),"CANARA BANK","TYPE BANK NAME")))).

It works for first option only (here, formula shows 'Delhi Nagrik Bank') and for other option it shows #Value.
Please tell me my mistake.?

A:

I suggest you break your formula into its smaller component parts, and test those, to understand the root cause.
Understanding the problem
SEARCH("DNB",P17) returns a number if found, or an error if not found. 
When that error occurs, the IF statement also returns an error. This is because IF expects input of either true or false only.
Also, this is why your formula works in the "DNB" case, but produces an error and doesn't actually test any further cases (e.g. "SBIN").
Solution
You need to update the function used in the IF statement's argument, so that it returns either a logical true or false value.
Conveniently, the ISNUMBER() function returns true if input is a number, and false otherwise.
TLDR
Instead of IF(SEARCH()), use IF(ISNUMBER(SEARCH())). Your formula will then work as intended.

