1. Computer problem? Tech Support Guy is completely free -- paid for by advertisers and donations. Click here to join today! If you're new to Tech Support Guy, we highly recommend that you visit our Guide for New Members.

Solved: how can i get nested search formula to work

Discussion in 'Business Applications' started by Trevors726, Jan 7, 2013.

Thread Status:
Not open for further replies.
  1. Trevors726

    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
     
  2. Garf13LD

    Garf13LD

    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))
     
  3. Trevors726

    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 -
     
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 733,556 other people just like you!

Thread Status:
Not open for further replies.

Short URL to this thread: https://techguy.org/1084135

  1. This site uses cookies to help personalise content, tailor your experience and to keep you logged in if you register.
    By continuing to use this site, you are consenting to our use of cookies.
    Dismiss Notice