Solved: how can i get nested search formula to work

Status
This thread has been Locked and is not open to further replies. Please start a New Thread if you're having a similar issue. View our Welcome Guide to learn how to use this site.

Trevors726

Thread Starter
Joined
Jan 7, 2013
Messages
7
I have a spreadsheet with listed part numbers that i want to be able to categorise based on the part number containing specific text. I can make this work individually with

=IF(SEARCH("BG",H7,1),"Building",0)

so part no abc-BG-123 returns Building

=IF(SEARCH("CE",H7,1),"Central",0)

part number abc-CE-123 returns central

But when i try and nest the next criteria I still get Building when part no abc-BG-123 but i get a #VALUE! error when the first criteria doesn't match even if the second one does

=IF(SEARCH("BG",H7,1),"Building",(IF(SEARCH("CE",H7,1),"Central",0)

Can anyone help with an alternative?

I am in Excel 2010
 
Joined
Apr 17, 2012
Messages
455
=IF(ISERROR(SEARCH("BG",H7,1)),IF(ISERROR(SEARCH("CE",H7,1)),0,"Central"),"Building")
=IF(IFERROR(SEARCH("BG",H7,1),0),"Building",IF(IFERROR(SEARCH("CE",H7,1),0),"Central",0))
 

Trevors726

Thread Starter
Joined
Jan 7, 2013
Messages
7
Thanks Garf, the second one has work perfectly with more than the 2 variations on the Partr number -
 
Status
This thread has been Locked and is not open to further replies. Please start a New Thread if you're having a similar issue. View our Welcome Guide to learn how to use this site.

Users Who Are Viewing This Thread (Users: 0, Guests: 1)

As Seen On
As Seen On...

Welcome to Tech Support Guy!

Are you looking for the solution to your computer problem? Join our site today to ask your question. This site is completely free -- paid for by advertisers and donations.

If you're not already familiar with forums, watch our Welcome Guide to get started.

Join over 807,865 other people just like you!

Latest posts

Top