# Return cell with longest character limit based on additional criteria

1. ## Return cell with longest character limit based on additional criteria

Hi all -

I am looking for some help creating a formula.

Column A1:A20 has a satisfaction score between 1 and 10
Column B1:B20 has text (customer comments)

What I need:

Column G1:G10 contains the descending numbers 1 to 10

I would like column H1:H20 to return the longest text from column B based on the satisfaction score (column A to match column G).

Apologies for the long-winded description but my business firewall doesn't allow image or file uploads.

Thanks for any help
SA

2. ## Re: Return cell with longest character limit based on additional criteria

In cell H1 entered as an array formula copied down
=MAX(LEN(B:B)*(A:A=G1))

an array formula is confirmed with {CTRL}{SHIFT}{ENTER}

The formula in the cell will then look like this {=MAX(LEN(B:B)*(A:A=G1))}
- typing { around a formula } does not work

see attached workbook

3. ## Re: Return cell with longest character limit based on additional criteria

However, I need the formula to return the actual text rather than the character length value, e.g. if the longest text for the customer score 1 was 'test test test' the formula result should be 'test test test'.

Thanks
SA

4. ## Re: Return cell with longest character limit based on additional criteria

Try this in H1:

=INDEX(\$B\$1:\$B\$20,MATCH(MAX(IF(\$A\$1:\$A\$20=G1,LEN(\$B\$1:\$B\$20))),IF(\$A\$1:\$A\$20=G1,LEN(\$B\$1:\$B\$20)),0))

Array formula, enter with Ctrl+Shift+Enter.

5. ## Re: Return cell with longest character limit based on additional criteria

However, I need the formula to return the actual text
Go with Phuocam's formula

6. ## Re: Return cell with longest character limit based on additional criteria

That's the badger! Thanks guys - I was tearing my hair out with combinations of MAX, INDEX, MATCH and LEN.

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

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