Write a benefit caption with full scope

Keep network, individual or family, and term with each benefit label.

What you'll make

In Excel: Labs + In + Family + Annual becomes Labs (In; Family; Annual).

The formula

The formula, with a numbered bracket under each part.
NumberPartWhat it doesResult so far
1OR(A8="",B8="",C8="",D8="")Checks if any of the four labels is empty.No
2A8&" ("&B8&"; "&C8&"; "&D8&")"Puts the three labels in brackets after the benefit."Labs (In; …)"
3IF( … )Flags a row with a missing label, else uses part 2."Labs (In; …)"

Review missing benefit scope shows when part 1 says yes.

=IF(OR(A8="",B8="",C8="",D8=""),"Review missing benefit scope",A8&" ("&B8&"; "&C8&"; "&D8&")")

How to use it

Yellow bar at the top? Click Enable Editing.

  1. See it work. The Example tab shows 20 made-up examples with the formula already in.
  2. Use your own list. Paste each benefit and its network, individual or family, and term labels into the green columns of the Template tab. The captions fill in by themselves.
  3. Move them into your letter. Copy the results. Then use Paste Special and choose Values.

Before you send

Use labels that belong to the same benefit. Don't fill a gap with a guess, since the caption would look certain.