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

1. ## 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. ## Re: Is there a formula that will populate a cell w/ TEXT based on multiple criteria?

Maybe:

Formula:
`Please Login or Register  to view this content.`

Regards, TMS

3. ## 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

Originally Posted by TMS
Maybe:

Formula:
`Please Login or Register  to view this content.`

Regards, TMS

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").
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. ## 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. ## 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

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

Originally Posted by TMS
Maybe:

Formula:
`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

8. ## 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

There are currently 1 users browsing this thread. (0 members and 1 guests)