Pick a party reference by requested role

Pick the beneficiary, subscriber or policyholder reference a letter asks for.

What you'll make

In Excel: PT-004512, SB-117340, PH-208815 + Subscriber becomes SB-117340.

The formula

The formula, with a numbered bracket under each part.
NumberPartWhat it doesResult so far
1SWITCH(D8,"Beneficiary",A8&"",…)Takes the reference for the role in column D."SB-117340"
2IF(v="","Review missing requested party",v)Flags the row if that reference is empty."SB-117340"
3LET( … )Names part 1's answer v so part 2 can check it."SB-117340"

Part 1 gives Review requested coverage role for any other role word.

=LET(v,SWITCH(D8,"Beneficiary",A8&"","Subscriber",B8&"","Policyholder",C8&"","Review requested coverage role"),IF(v="","Review missing requested party",v))

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 row's three references and the role your letter is for into the green columns of the Template tab. The reference fills in by itself.
  3. Move them into your letter. Copy the results. Then use Paste Special and choose Values.

Before you send

Choose the role from your own workflow, not from the name or age. A reference here doesn't mean you may send.

Microsoft's help: SWITCH function