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("etherne t",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.
Last edited by NBVC; 12-14-2011 at 03:39 PM.
Try:
=LOOKUP(9.999999E+307,SEARCH($B$10:$B$49,B3),$A$10:$A$49)
copied down
Microsoft MVP - Excel
Where there is a will there are many ways. Pick One!
Please read the Forum Rules
If you are happy with the results, please add to the contributor's reputation by clicking the reputation icon (star icon) below
Please also mark the thread as Solved once it is solved. Check the FAQ's to see how.
Preferred Charities: Lupus Canada and Sick Kids Foundation.
Feel Free to Donate if you want to, for the assistance you received today.
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.
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.
Microsoft MVP - Excel
Where there is a will there are many ways. Pick One!
Please read the Forum Rules
If you are happy with the results, please add to the contributor's reputation by clicking the reputation icon (star icon) below
Please also mark the thread as Solved once it is solved. Check the FAQ's to see how.
Preferred Charities: Lupus Canada and Sick Kids Foundation.
Feel Free to Donate if you want to, for the assistance you received today.
Thank you very much!
There are currently 1 users browsing this thread. (0 members and 1 guests)
Posting Permissions
Reply With Quote
Bookmarks