+ Reply to Thread
Results 1 to 4 of 4

Formula to return value based on two criteria

  1. #1
    Registered User
    Join Date
    04-20-2015
    Location
    Utah
    MS-Off Ver
    Office 365
    Posts
    21

    Formula to return value based on two criteria

    I have a seasonal business and I am trying to create a spreadsheet that pulls a historical average capacity number based on two criteria, week of the year and day of the week. I can get that working both with vlookup and sumifs formulas. There is a third variable involved and that is what I am calling special days (christmas, new years, etc.). I have tried different variants of index and match statements, nesting if statements with sumifs but I am not having any luck.

    My desired outcome is this:
    - Reference column A and B on the Capacity Data tab
    - If column H on the Daily Budget Day over Day tab matches column B on the Capacity Data tab, return the value in column C from the Capacity Data tab in column I on the Daily Budget Day over Day tab
    - If column H on the Daily Budget Day over Day tab is empty, match column B on the Daily Budget Day over Day tab to column A on the Capacity Data tab and match column C on the Daily Budget Day over Day tab to column B on the Capacity Data tab, return the associated value from column C on the Capacity Data tab in column I on the Daily Budget Day over Day tab
    Snowsports Instructors - 2015-2016.xlsx
    Thanks for your help!

  2. #2
    Forum Expert skywriter's Avatar
    Join Date
    06-09-2014
    Location
    USA
    MS-Off Ver
    2016
    Posts
    2,760

    Re: Formula to return value based on two criteria

    Try this in I2 of Daily Budget Day Over Day and copy down:

    Formula: copy to clipboard
    Please Login or Register  to view this content.



    If your original request is fulfilled please click the Thread Tools drop down box above your first post and choose solved.


    If you are happy with my help, please consider clicking the add reputation button in the lower left hand corner of this post.
    Click the * Add Reputation button in the lower left hand corner of this post to say thanks.

    Don't forget to mark this thread SOLVED by going to the "Thread Tools" drop down list above your first post and choosing solved.

  3. #3
    Registered User
    Join Date
    04-20-2015
    Location
    Utah
    MS-Off Ver
    Office 365
    Posts
    21

    Re: Formula to return value based on two criteria

    Thanks skywriter, that did the trick!

  4. #4
    Forum Expert skywriter's Avatar
    Join Date
    06-09-2014
    Location
    USA
    MS-Off Ver
    2016
    Posts
    2,760

    Re: Formula to return value based on two criteria

    My pleasure. Thanks for the rep. points and for marking the thread as solved.

+ 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. Help with a formula to look up/return based on 2 criteria
    By Choppo in forum Excel Formulas & Functions
    Replies: 3
    Last Post: 11-11-2014, 09:48 PM
  2. [SOLVED] Formula to return specified value based on criteria
    By adriam25 in forum Excel Formulas & Functions
    Replies: 13
    Last Post: 09-23-2014, 12:39 AM
  3. Formula to return specified value based on criteria
    By adriam25 in forum Excel General
    Replies: 8
    Last Post: 09-07-2014, 06:21 AM
  4. [SOLVED] Search and return value based on 3 criteria (array formula?)
    By AL1976 in forum Excel Formulas & Functions
    Replies: 3
    Last Post: 02-03-2014, 10:49 AM
  5. [SOLVED] Formula to Return ID based on Multiple Criteria
    By boldcode in forum Excel Formulas & Functions
    Replies: 2
    Last Post: 05-09-2013, 02:54 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