Take the 2-minute tour ×
Web Applications Stack Exchange is a question and answer site for power users of web applications. It's 100% free, no registration required.

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

share|improve this question

migrated from stackoverflow.com Jan 17 at 13:22

This question came from our site for professional and enthusiast programmers.

2 Answers 2

up vote 1 down vote accepted

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!

share|improve this answer

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.

share|improve this answer
    
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 at 18:24

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

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