+ Reply to Thread
Results 1 to 6 of 6

Return Value based on Range Criteria selected

  1. #1
    Forum Contributor
    Join Date
    05-29-2017
    Location
    UK
    MS-Off Ver
    Pro Plus 2016
    Posts
    301

    Return Value based on Range Criteria selected

    Hi guys, what would be a suggestion for a formula, as per screenshot? Guessing Lookup or Match? Would it be possible to write it up, please? Balances in yellow cells are correct, only criteria's needs to be added. Thank you!

    https://www.excelforum.com/attachmen...1&d=1530883351 [EXCEL]

    2018-07-06_1519.png
    Attached Files Attached Files

  2. #2
    Forum Expert BadlySpelledBuoy's Avatar
    Join Date
    06-14-2013
    Location
    East Sussex, UK
    MS-Off Ver
    365
    Posts
    7,468

    Re: Return Value based on Range Criteria selected

    I don't understand what you're actually asking for here.

    Are you saying the yellow cells are your expected results and need a formula for that?
    If so then the below in S6 and copied down will do that:
    Formula: copy to clipboard
    Please Login or Register  to view this content.


    But I suspect I'm missing the point so if that's the case perhaps a more detailed explanation would be useful.

    BSB

  3. #3
    Forum Contributor
    Join Date
    05-29-2017
    Location
    UK
    MS-Off Ver
    Pro Plus 2016
    Posts
    301

    Re: Return Value based on Range Criteria selected

    Hi BadlySpelledBuoy, thanks for looking into it.

    Your suggested formula is what was needed, and it does the job, thank you! Only thing that it does not do, is that if cells in YearlyPlanner!B28:B48 are blank, the formula in S6:S12 also returns blanks. What would be a way of saying 'return 0' if blanks?

  4. #4
    Forum Expert Sam Capricci's Avatar
    Join Date
    06-14-2012
    Location
    Palm Harbor, Florida
    MS-Off Ver
    16.83 for Mac MS 365
    Posts
    8,480

    Re: Return Value based on Range Criteria selected

    perhaps this adjustment will work for it...
    =IF(INDEX(YearPlanner!B:B,MATCH(G6,YearPlanner!A:A,0))=0,"return 0",INDEX(YearPlanner!B:B,MATCH(G6,YearPlanner!A:A,0)))
    Make contributors happy, click on the "* Add Reputation" as a way to say thank you.
    Sam Capricci

  5. #5
    Forum Contributor
    Join Date
    05-29-2017
    Location
    UK
    MS-Off Ver
    Pro Plus 2016
    Posts
    301

    Re: Return Value based on Range Criteria selected

    Awesomeness, thank you guys!! Much appreciated.

    Have a nice day!!

  6. #6
    Forum Expert Sam Capricci's Avatar
    Join Date
    06-14-2012
    Location
    Palm Harbor, Florida
    MS-Off Ver
    16.83 for Mac MS 365
    Posts
    8,480

    Re: Return Value based on Range Criteria selected

    glad that worked for you, AND thank you for the rep!

+ 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. [SOLVED] Return first number in Range based on multiple criteria
    By Perk1961 in forum Excel Formulas & Functions
    Replies: 2
    Last Post: 10-21-2015, 09:35 AM
  2. [SOLVED] Return a number from a range of cells based on criteria?
    By Polymorpher in forum Excel Formulas & Functions
    Replies: 11
    Last Post: 07-25-2014, 11:01 AM
  3. Macro to Copy Selected Range from one Worksheet to Another, based off of Criteria
    By KLiving in forum Excel Programming / VBA / Macros
    Replies: 14
    Last Post: 11-04-2013, 03:02 PM
  4. [SOLVED] Copy a selected range based on cell criteria
    By Ronny66 in forum Excel Programming / VBA / Macros
    Replies: 2
    Last Post: 09-11-2013, 07:48 AM
  5. [SOLVED] Function to return 2 columns based on 2 user selected criteria
    By lax2734 in forum Excel Formulas & Functions
    Replies: 5
    Last Post: 04-07-2013, 04:36 PM
  6. Trying to get single data return from a range based on 3 criteria
    By alycat75 in forum Excel Programming / VBA / Macros
    Replies: 1
    Last Post: 06-27-2012, 10:26 AM
  7. return all range based on criteria
    By Dumy in forum Excel - New Users/Basics
    Replies: 2
    Last Post: 07-28-2011, 04:21 AM

Tags for this Thread

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