I'm using a Google Drive spreadsheet to organize guests I want to invite to my wedding. On one sheet I want all my guests and information relating to them (are they friend/family? Are they an usher/groomsman? etc). On another sheet I want stats so I can prevent going crazy. I want to indicate how many family members, friends, groomsmen, etc are showing up. I also want a field to show the names of people who have certain responsibilities such as Best Man and Priest.

Here's how I think I can best set up the table to do this.

| First Name | Last Name | Status | ... |
| John       | Doe       | F      | ... |
| Joe        | Smith     | ABG    | ... |
| Cousin     | Doe       | FU     | ... |

Status contains letters such as F for family, A for amigo (F is for family, can't use Friend), B for Best Man, G for Groomsman, U for Usher, P for Priest and more as needed. For example, Joe Smith is a friend, the best man and inherently also a groomsman. Cousin Doe is a family member who is also an usher.

Is there a formula where I can look for a Status column entry that contains a B and if so, pieces together the first and last name?

I am just starting to make this so I can restructure if there's an easier approach. I'm just trying to avoid having a dozen columns of checkboxes that say "Is Groomsman" or "Is Bartender" if some responsibilities only apply to one person (i.e. it's unlikely that my priest will also be the bartender, though that'd be bad ass).


I discovered VLOOKUP and could use it for what I need. It did however require that I move my Status column to be A instead of C.

=CONCATENATE(VLOOKUP("*B*", GuestList!A2:C1000, 2, FALSE), " ", VLOOKUP("*B*", GuestList!A2:C1000, 3, FALSE))

VLOOKUP will search for anything with a B (as defined by "*B*" as the first parameter) in the first column of the specified range (GuestList!A2:C1000, it looks in column A) and when it finds it, it returns the value in column 2. The list is not sorted so I used the optional parameter to say so. Throw all that into a CONCATENATE twice like this along with a space and I get the name!


You could use a formula like this:

=IF(SEARCH("B",C2), CONCATENATE(A2, " ", B2), "")

This will return the first name a space and last name if C2 contains "B" otherwise it will return an empty string.

  • I want to search all of C and when it finds it, use the corresponding A and B. This only searches one row. – Corey Ogburn Jan 2 '14 at 18:24
  • this returns #VALUE! error if value is not found in range – vladkras Feb 7 '19 at 13:42

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.