+ Reply to Thread
Results 1 to 4 of 4

Return Values based upon multiple criteria

  1. #1
    Registered User
    Join Date
    10-04-2005
    Posts
    73

    Return Values based upon multiple criteria

    Hi

    Is it possible to enter specific text based upon a list of options. For example

    I have a table containing animals in column B as follows:

    B2 - Owl
    B3 - Elephant
    B4 - Sparrow
    B5 - Dolphin
    B6 - Lion
    B7 - Trout
    B8 - Cat
    B9 - Whale
    B10 -Pigeon
    etc

    In column C I would like to enter the animal type. I would like the formula to do the following:

    If B2 contains Owl, Sparrow or Pigeon then C2 is bird
    If B2 contains Elephant, Lion, Cat then C2 is mammal
    If B2 contains Whale, Dolphin, Trout then C2 is fish.

    And so on and so forth for each cell.

    I don't want to use lookups if at all possible.

    I hope this makes sense.

    Thanks for the help.

  2. #2
    Forum Moderator alansidman's Avatar
    Join Date
    02-02-2010
    Location
    Steamboat Springs, CO
    MS-Off Ver
    MS Office 365 Version 2405 Win 11 Home 64 Bit
    Posts
    23,872

    Re: Return Values based upon multiple criteria

    =if(Or(B2="Owl",B2="Sparrow",B2="Pigeon"),"bird",if(Or(B2="Elephant",B2="Lion",B2="Cat"),"mammal","fish")).

    Nested if statements can work, or if you are amenable to VBA, you could do this with Select Case.
    Alan עַם יִשְׂרָאֵל חַי


    Change an Ugly Report with Power Query
    Database Normalization
    Complete Guide to Power Query
    Man's Mind Stretched to New Dimensions Never Returns to Its Original Form

  3. #3
    Registered User
    Join Date
    10-04-2005
    Posts
    73

    Re: Return Values based upon multiple criteria

    Thanks alansidman

    This worked great!

  4. #4
    Forum Guru AlKey's Avatar
    Join Date
    07-20-2009
    Location
    Lakeland, FL USA
    MS-Off Ver
    Microsoft Office 2010/ Office 365
    Posts
    8,903

    Re: Return Values based upon multiple criteria

    Or
    Formula: copy to clipboard
    Please Login or Register  to view this content.
    If you like my answer please click on * Add Reputation
    Don't forget to mark threads as "Solved" if your problem has been resolved

    "Nothing is so firmly believed as what we least know."
    --Michel de Montaigne

+ Reply to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Similar Threads

  1. Replies: 3
    Last Post: 12-03-2014, 05:43 PM
  2. [SOLVED] How to return multiple values to a single cell based on multiple criteria
    By lwallace in forum Excel Formulas & Functions
    Replies: 5
    Last Post: 08-20-2014, 06:32 AM
  3. Replies: 2
    Last Post: 03-03-2014, 10:03 PM
  4. Array formula to return multiple values based on various (OR) criteria
    By dancing-shadow in forum Excel Formulas & Functions
    Replies: 4
    Last Post: 12-20-2013, 05:12 AM
  5. Lookup/ Return and concatenate values based upon multiple criteria
    By steveboise in forum Excel Formulas & Functions
    Replies: 1
    Last Post: 04-15-2013, 10:43 AM
  6. Replies: 10
    Last Post: 02-19-2013, 12:05 PM
  7. Select multiple criteria based on check box selection and return all unique values.
    By TommyToe in forum Excel Programming / VBA / Macros
    Replies: 0
    Last Post: 01-21-2013, 09:14 AM

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1