2

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

2 Answers 2

3

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!

1

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.

2
  • 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
    Commented Jan 2, 2014 at 18:24
  • 1
    this returns #VALUE! error if value is not found in range
    – vladkras
    Commented Feb 7, 2019 at 13:42

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

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