+ Reply to Thread
Results 1 to 3 of 3

Evaluate multiple criteria with multiple results (IF / OR / AND formula)

  1. #1
    Forum Contributor
    Join Date
    05-08-2015
    Location
    Asheville NC
    MS-Off Ver
    Excel for Office 365 MSO
    Posts
    111

    Evaluate multiple criteria with multiple results (IF / OR / AND formula)

    I am working with 2 worksheets within the same workbook. On the first tab I am using a IF formula to evaluate if 2 criteria (on the 2nd tab) are met and if true enter a value from the 2nd tab onto the 1st tab. I can get the formula to work fine but it is very long and am curious if it can be shortened. In the example I am only using 2 machines with the 2 conditions to be met (machine A & B and years2016 - 2019), however I have to evaluate many machines.
    Attached Files Attached Files

  2. #2
    Forum Guru
    Join Date
    04-13-2005
    Location
    North America
    MS-Off Ver
    2002/XP and 2007
    Posts
    15,802

    Re: Evaluate multiple criteria with multiple results (IF / OR / AND formula)

    Lookup functions (see help files here: https://support.office.com/en-us/art...rs=en-US&ad=US ). Specifically, the INDEX() and MATCH() functions.

    One MATCH() function to return the row number MATCH("A",equipment!$A$4:$A$12,0)
    One MATCH() function to return the column number MATCH(2016,equipment!$C$3:$F$3,0)
    The INDEX() function to return the final result INDEX(equipment!$C$4:$F$12,result of row match, result of column match)
    Quote Originally Posted by shg
    Mathematics is the native language of the natural world. Just trying to become literate.

  3. #3
    Forum Guru Kaper's Avatar
    Join Date
    12-14-2013
    Location
    Warsaw, Poland
    MS-Off Ver
    most often: Office 365 in Windows environment
    Posts
    8,606

    Re: Evaluate multiple criteria with multiple results (IF / OR / AND formula)

    Use such formula instead:
    Formula: copy to clipboard
    Please Login or Register  to view this content.


    Of course, if you have more cells filled in Equipment sheet, ranges have to be extended.
    I used absolute addressing (dollar signs in front of column letter and row number), but if you do not plan to copy the formula down/righ you do not need it and relative (no$) addressing can be used.
    Best Regards,

    Kaper

+ 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: 1
    Last Post: 09-04-2014, 12:14 PM
  2. Multiple Search Criteria produces (list) Multiple results in a single cell
    By snake007uk in forum Excel Formulas & Functions
    Replies: 2
    Last Post: 07-02-2014, 12:13 PM
  3. Help getting multiple results from multiple criteria on multiple pages
    By ERaasio in forum Excel Formulas & Functions
    Replies: 6
    Last Post: 07-03-2013, 07:29 PM
  4. Replies: 11
    Last Post: 02-07-2013, 02:22 AM
  5. User Form to Search multiple Criteria to return multiple Results
    By Calieth in forum Excel Programming / VBA / Macros
    Replies: 8
    Last Post: 08-16-2011, 11:57 AM
  6. Evaluate Multiple Data Points Against Single Criteria
    By fmulvaney in forum Excel General
    Replies: 3
    Last Post: 08-31-2010, 03:26 PM
  7. Replies: 9
    Last Post: 08-10-2010, 01:49 PM

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