Urgently needing a formula that searches 2 columns for exact matches ...

[tanyaslater]

I'm running a guest list for an event and I have the following columns:

A - First Name
B - Surname
C - Formula to combine A&B
D - Organization
E - # of VIP Tix
F - # of GA Tix
G - # of own Tix
H - Total Guests (where this formula would live)
I - Guest Of who

I have main guests who then have sub guests attached to their party. I need to get a total number of guests to display in the H column of the main guests row.

i.e I need a formula that searches all of column I and if it finds a name that matches a name anywhere in Column C it will take the number of guests in that row and then add it to the Total Guests total in Column H. Does that make sense?

Column I will match Column C if the person is a main guest with no sub guests. If they have sub guests then they will be manually entered as the main guest name in column I.

For example John Smith is a Main guest in row 3 and has 5 guests arriving with him. But he also has his friend Davey Jones who is arriving later with 4 other guests but who will all be sitting with John Smith's party. Column I will be manually edited to show that DAvey JOnes is a guest of John Smith. I need a way to display in John Smith's row the total combined number of his direct guests and his sub guests.

I can add more columns etc if that's what's required as I know this is a complicated ask.

 

[Sinon]

I believe it would be more helpful if you could provide an example of all the situation that might arise (no sub guests, a few sub guests, friends and their sub guests etc) and your expected results. For instance, where does it say that Davey Jones has an additional 4 guests? Will that be a separate line or will you add this info in one cell? Will you only have the number of guests or their names as well? Do you need the names returned as well or just a total number?

Maybe try Excel Jeanie to get sample data in the forum.