IF SEARCH Function

**snboarder356** · 12-13-2011, 11:17 AM

I am trying to build a formula into a macro that yields a particular value if one of a different set of values can be found within the adjacent column. I have tried using the below nested IF(SEARCH function but it does not work and it does not allow this many nested functions in Excel 2007.

=if(search("laptop",m2)=1,"LAPTOP",if(search("book",m2)=1,"LAPTOP",if(search("imac",m2)=1,"DESKTOP",if(search("mac mini",m2)=1,"DESKTOP",if(search("mac pro",m2)=1,"DESKTOP",if(search("macpro",m2)=1,"DESKTOP",if(search("elite 4",m2)=1,"DESKTOP",if(search("elite 5",m2)=1,"DESKTOP",if(search("elite 6",m2)=1,"DESKTOP",if(search("elite 7",m2)=1,"DESKTOP",if(search("elite 8",m2)=1,"DESKTOP",if(search("elite 9",m2)=1,"DESKTOP",if(search("elite 1",m2)=1,"DESKTOP",if(search("19in",m2)=1,"MONITOR",if(search("20in",m2)=1,"MONITOR",if(search("21in",m2)=1,"MONITOR",if(search("22in",m2)=1,"MONITOR",if(search("23in",m2)=1,"MONITOR",if(search("24in",m2)=1,"MONITOR",if(search("25in",m2)=1,"MONITOR",if(search("26in",m2)=1,"MONITOR",if(search("27in",m2)=1,"MONITOR",if(search("28in",m2)=1,"MONITOR",if(search("29in",m2)=1,"MONITOR",if(search("ethernet",m2)=1,"NETWORK",if(search("switch",m2)=1,"NETWORK",if(search("gigabit",m2)=1,"NETWORK",if(search("router",m2)=1,"NETWORK",if(search("printer",m2)=1,"PRINTER",if(search("proliant",m2)=1,"SERVER",if(search("xeon",m2)=1,"SERVER",if(search("storage",m2)=1,"STORAGE",if(search("5400RPM",m2)=1,"STORAGE",if(search("7200RPM",m2)=1,"STORAGE",if(search("15,000RPM",m2)=1,"STORAGE",if(search("10000rpm",m2)=1,"STORAGE",if(search("10,000rpm",m2)=1,"STORAGE",if(search("5,400rpm",m2)=1,"STORAGE",if(search("7,200rpm",m2)=1,"STORAGE",if(search("phone",m2)=1,"TELEPHONES",0)

In the attached file, the function should yield the values in column A if the respective values can be found in column B. Any help would be greatly appreciated.

**NBVC** · 12-13-2011, 11:26 AM

Try:

=LOOKUP(9.999999E+307,SEARCH($B$10:$B$49,B3),$A$10:$A$49)

copied down

**snboarder356** · 12-13-2011, 04:04 PM

Thanks very much, would it be too much to ask how that forumula performs the task? More specifically, I'm unfamiliar with the 9.999999E+307.

**NBVC** · 12-13-2011, 04:16 PM

that is the largest number Excel can use....

Lookup syntax: =LOOKUP(Lookup_value,lookup_vector,[result_vector])

in this case we are using LOOKUP to find that number in the defined lookup_vector and return the correspond item in the result_vector.

Lookup won't find that number in the lookup_vector defined by SEARCH($B$10:$B$49,B3), which returns a vector of #VALUE errors where matches are not found and some numbers where matches are found, so it looks for the last number in the vector, ignoring the errors, and returns the corresponding item in the adjacent column.

If you go to Formulas tab|Evaluate formula, you can step through to see how it works.