+ Reply to Thread
Results 1 to 8 of 8

Is there a formula that will populate a cell w/ TEXT based on multiple criteria?

  1. #1
    Registered User
    Join Date
    05-29-2014
    Posts
    4

    Question Is there a formula that will populate a cell w/ TEXT based on multiple criteria?

    Hello All-

    Hoping someone can help me as I am getting quite frustrated! I am trying to find a formula that will allow me to populate a cell(s) with text based on more than one criteria (likely 2).

    I have used SUMPRODUCT and SUMIFS however both of these work only if your "Sum_range" is numerical. I need a formula that is nearly identical to those however works for letters/words.

    Example:

    If the member name is Mary and member number is 4558, I need the letter "A" to populate in a particular cell of my spreadsheet (see attached chart).


    Excel.jpg

    Any help would be greatly appreciated!!

    J

  2. #2
    Forum Guru TMS's Avatar
    Join Date
    07-15-2010
    Location
    The Great City of Manchester, NW England ;-)
    MS-Off Ver
    MSO 2003,2007,2010
    Posts
    32,227

    Re: Is there a formula that will populate a cell w/ TEXT based on multiple criteria?

    Maybe:

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



    Regards, TMS
    Trevor Shuttleworth - Excel Aid

    I dream of a better world where chickens can cross the road without having their motives questioned

    'Being unapologetic means never having to say you're sorry' John Cooper Clarke


  3. #3
    Registered User
    Join Date
    05-29-2014
    Posts
    4

    Re: Is there a formula that will populate a cell w/ TEXT based on multiple criteria?

    Unfortunately that does not work. It creates a "true" or "false" statement (so the result I got in the cell was "False"). I need one cell of one workbook to populate the exact same info from another workbook (whatever word is used in a particular cell), assuming they share 2 common pieces of criteria.

    So if workbook A shares the same two pieces of criteria from workbook B, the cell in workbook A will populate with the same text of a particular cell of a named range in workbook B.

    Providing the end result value is numerical, SUMIFS works. It does not work if the cell I am trying to populate is not numerical. Here is the formula I am using for numerical: =SUMIFS(Master!$J$2:$J$80,Master!$H$2:$H$80,A3,Master!$I$2:$I$80,C3)
    *Master! is the name of the workbook I am referring to




    Quote Originally Posted by TMS View Post
    Maybe:

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



    Regards, TMS

  4. #4
    Forum Guru TMS's Avatar
    Join Date
    07-15-2010
    Location
    The Great City of Manchester, NW England ;-)
    MS-Off Ver
    MSO 2003,2007,2010
    Posts
    32,227

    Re: Is there a formula that will populate a cell w/ TEXT based on multiple criteria?

    Unfortunately that does not work. It creates a "true" or "false" statement (so the result I got in the cell was "False").
    Yes it does; no it doesn't; not sure how you got "False" as it can only produce "A" or blank/null.

    Perhaps you would like to share more details about the version of Excel you use, your location, your regional settings and, ideally, a sample workbook with some typical data indicating what results you would hope to get.

    Not much to be done with a picture of a workbook ...

  5. #5
    Forum Guru TMS's Avatar
    Join Date
    07-15-2010
    Location
    The Great City of Manchester, NW England ;-)
    MS-Off Ver
    MSO 2003,2007,2010
    Posts
    32,227

    Re: Is there a formula that will populate a cell w/ TEXT based on multiple criteria?

    So if workbook A shares the same two pieces of criteria from workbook B, the cell in workbook A will populate with the same text of a particular cell of a named range in workbook B.

    You didn't mention multiple workbooks in the original post ... where did that come from?

  6. #6
    Registered User
    Join Date
    05-29-2014
    Posts
    4

    Re: Is there a formula that will populate a cell w/ TEXT based on multiple criteria?

    Sorry, I was trying to simplify. Also, the real workbook is confidential so I cannot actually post it.

    I tried to make a fake one (fictional data) that reflects what I am trying to do. Please see attached...

    Thank you!

    J
    Attached Files Attached Files

  7. #7
    Registered User
    Join Date
    05-29-2014
    Posts
    4

    Re: Is there a formula that will populate a cell w/ TEXT based on multiple criteria?

    Quote Originally Posted by TMS View Post
    Maybe:

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



    Regards, TMS
    Your formula doesn't seem to work for some reason. I cannot figure out what I'm doing wrong!

    Posting a new "fake" template.

    On the "Overall Exposure" worksheet I am trying to do a nested IF/AND formula for the "Detailed Product Type".

    Example: For cell D3, I need a formula that says "If the obligation number is 481000000 and the borrower is Jane Doe, give me the Product Type that corresponds from the "Master" worksheet.

    I am looking for the exact answer as found in cell N2, which is "Mars". I cannot get the cell to populate Mars. I either get a blank, or "False".

    The "Master" is where all data comes from.

    Do not know what to do. I feel like I've tried everything
    Attached Files Attached Files

  8. #8
    Forum Guru TMS's Avatar
    Join Date
    07-15-2010
    Location
    The Great City of Manchester, NW England ;-)
    MS-Off Ver
    MSO 2003,2007,2010
    Posts
    32,227

    Re: Is there a formula that will populate a cell w/ TEXT based on multiple criteria?

    D3: =INDEX(DETAILED_PRODUCT_TYPE,MATCH(A3&C3,OBLIGATION_NUMBER&BORROWER,0)) committed with Ctrl-Shift-Enter

    This is an array formula and will return the values expected/required. However, it will be very slow because the Named Ranges refer to entire columns.

    You could improve performance by specifying a range rather than a full column. Better still, use a Dynamic Named Range. See the updated example file.


    Regards, TMS
    Attached Files Attached Files

+ 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. Formula to Populate Multiple Cells Based on Text strings in Another Cell
    By RASoff in forum Excel Formulas & Functions
    Replies: 0
    Last Post: 03-18-2014, 11:52 AM
  2. Replies: 5
    Last Post: 03-04-2014, 10:01 AM
  3. [SOLVED] How to auto populate multiple Text Boxes from list based on value of a cell
    By MooseAUH in forum Excel Programming / VBA / Macros
    Replies: 2
    Last Post: 01-04-2014, 03:40 AM
  4. 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
  5. Replies: 9
    Last Post: 12-13-2010, 01:42 PM

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