+ Reply to Thread
Results 1 to 2 of 2

Writing a formula for returning a text value based on multiple criteria

  1. #1
    Registered User
    Join Date
    01-27-2016
    Location
    Oakland, CA
    MS-Off Ver
    Excel
    Posts
    1

    Writing a formula for returning a text value based on multiple criteria

    Hi all,

    I'm working on a formula in Excel for Mac for a report at work that will show when specific letters and reports are due for a specific grant funder and fiscal year. It is pulling this data from a table where the specific funders and fiscal years are on the right side and the data for what type of letter and when it is due are in a big table on the left.

    I need the output to do all of the following:

    1) Match for a specific funder as indicated in a cell in the main report
    2) Match for a specific fiscal period as indicated in the cell below the funder cell
    3) Finally it needs to find a specific text value within the table that includes a wildcard as the text value is a code for the type of report and includes a numerical value indicating the date when it is due

    The formula I currently have written up looks like this:

    =INDEX('Grants Calendar'!H3:AE51,SUMPRODUCT(('Grants Calendar'!B3:B60=C2)*('Grants Calendar'!F3:F60=C3)*('Grants Calendar'!H3:AE51="FINAL")*ROW('Grants Calendar'!H3:AE51)),1)

    I am currently receiving the #N/A output even though I know the inputs in the funder and fiscal cells match up with an existing result. What can I do to write a formula that will produce the outcome I need? How can I tweak the existing formula so it will work?

  2. #2
    Forum Moderator
    Join Date
    01-21-2014
    Location
    St. Joseph, Illinois U.S.A.
    MS-Off Ver
    Office 365 v 2403
    Posts
    13,406

    Re: Writing a formula for returning a text value based on multiple criteria

    Hi Data,

    Welcome to the forum.

    SUMPRODUCT seems an unlikely function for generating an index number. I have been wrong about that before. That said, in SUMPRODUCT the number of rows must be equal. Try changing all the AE51s to AE60.

    If that doesn't do what you intend try uploading an Excel workbook example (data desensitized) that demonstrates the challenge. Show a before section and an after section (hand typed if necessary).

    To attach a file:

    If you are not familiar with how to do this:
    • click FAQ at the top of this page,
    • under Board FAQ click Reading and posting messages
    • then click Attachments and images
    • You will find instructions on how to do this.
    Dave

+ 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. Returning text against text based criteria
    By dejathorus in forum Excel Programming / VBA / Macros
    Replies: 1
    Last Post: 07-19-2014, 04:50 PM
  2. Is there a formula that will populate a cell w/ TEXT based on multiple criteria?
    By violenttuesday in forum Excel Programming / VBA / Macros
    Replies: 7
    Last Post: 06-11-2014, 03:46 PM
  3. [SOLVED] Requesting help returning text values from criteria based on multiple columns
    By dzarrabi in forum Excel Programming / VBA / Macros
    Replies: 5
    Last Post: 10-15-2013, 08:16 AM
  4. Returning multiple row based on criteria
    By MartyB99 in forum Excel Formulas & Functions
    Replies: 3
    Last Post: 10-02-2013, 04:56 PM
  5. Formula to count Unique text based on multiple criteria
    By OAMPS in forum Excel Formulas & Functions
    Replies: 4
    Last Post: 06-14-2012, 10:48 AM
  6. Returning a lookup value based on multiple criteria.
    By ahunter488 in forum Excel General
    Replies: 3
    Last Post: 06-09-2011, 01:55 AM
  7. returning entire row based on single or multiple criteria
    By cdevericks in forum Excel Formulas & Functions
    Replies: 3
    Last Post: 10-23-2008, 09:43 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