Collect value where cell with multiple value options contains a specific value

Av1ator

Greetings, Community!

I am hoping someone can lend a hand here, as I have tried multiple options but keep coming up short on a solution. 

Essentially, I have two tables. Table A (Names) has a list of unique names. Table B (Events) has a list of events, where each event can contain multiple names in a cell (not contacts, just names as a drop down list). I am trying to create a formula in table A that looks to see if if that name exists in the multiple name column, and if it does, return the value (location) also from Table B. 

The column I need to evaluate (multiples) is column #19 of sheet B, while the value I need to return is column #10 of sheet B.

Hope this makes sense. Appreciate any input in advance!

Kelly Moore

Hey @Av1ator

Try this:

=INDEX(COLLECT({TableB Location column}, {TableB Event column}, CONTAINS(Names@row, @cell)), 1)

Remember you must physically insert the cross sheet references in the sheet, you cannot simply copy paste this formula.

will this work for you? If you need help with the cross sheet references, shout out

Kelly

Kelly Moore

@Av1ator

I'm a bit confused as to what you want, but the answer is yes to both of the above. At least I believe there are two different requests you made. Please clarify - and I'm happy to help

Here's what I understood: (1) All relevant locations joined in one cell. This one is straight forward. Use a JOIN/Collect instead of Index/Collect. In the formula below, I used a line break - CHAR(10) as the delimiter. If you text wrap your cells you will see each value in it's own line within the cell. You must text wrap to see it. If you'd rather have a comma or other delimiter, replace the entire CHAR(10) with double quotes enclosing your delimiter. Like this: ","

=JOIN(COLLECT({TableB Location column}, {TableB Event column}, CONTAINS(Names@row, @cell)), CHAR(10))

Location with the minimum date of multiple rows with same event - this one is trickier. You will need to add a helper column to your TableA sheet since the minimum date per Names must be found. It's not simply the min created date or that would not require the helper. This helper needs to be a date column - if you must keep the timestamp let me know and I will make some adjustments to your formulas.

TableA Helper

=MIN(COLLECT({TableB Created column}, {TableB Created column}, ISDATE(@cell), {TableB Event column}, CONTAINS(Names@row, @cell)))

Location that you wanted:

=INDEX(COLLECT({TableB Location column}, {TableB Event column}, CONTAINS(Names@row, @cell), {TableB Created column}, DATEONLY(@cell)=[TableA Helper]@row

If either of these are what you wanted, great! If these aren't quite right, shout out and I'll try again.

Kelly

Kelly Moore

Hey @Av1ator

Try this:

=INDEX(COLLECT({TableB Location column}, {TableB Event column}, CONTAINS(Names@row, @cell)), 1)

Remember you must physically insert the cross sheet references in the sheet, you cannot simply copy paste this formula.

will this work for you? If you need help with the cross sheet references, shout out

Kelly

Av1ator

@Kelly Moore

Thank you so much for getting back. I swear I tried this exact formula and was getting nowhere, but this worked perfectly! Maybe my row index was bad... I nested it in an IFERROR and it's exactly what I was after. You're awesome.

If I may I ask one more question of you...

I there happened to be multiple rows in table B where a name is used in more than one row at a given time, is there a way for this to look at the earliest event based on an auto generated created date/time column?

Thank you again for your time here.

Av1ator

@Kelly Moore

Or rather, if they are listed in multiple rows join them together in table A?

Kelly Moore

@Av1ator

I'm a bit confused as to what you want, but the answer is yes to both of the above. At least I believe there are two different requests you made. Please clarify - and I'm happy to help

Here's what I understood: (1) All relevant locations joined in one cell. This one is straight forward. Use a JOIN/Collect instead of Index/Collect. In the formula below, I used a line break - CHAR(10) as the delimiter. If you text wrap your cells you will see each value in it's own line within the cell. You must text wrap to see it. If you'd rather have a comma or other delimiter, replace the entire CHAR(10) with double quotes enclosing your delimiter. Like this: ","

=JOIN(COLLECT({TableB Location column}, {TableB Event column}, CONTAINS(Names@row, @cell)), CHAR(10))

Location with the minimum date of multiple rows with same event - this one is trickier. You will need to add a helper column to your TableA sheet since the minimum date per Names must be found. It's not simply the min created date or that would not require the helper. This helper needs to be a date column - if you must keep the timestamp let me know and I will make some adjustments to your formulas.

TableA Helper

=MIN(COLLECT({TableB Created column}, {TableB Created column}, ISDATE(@cell), {TableB Event column}, CONTAINS(Names@row, @cell)))

Location that you wanted:

=INDEX(COLLECT({TableB Location column}, {TableB Event column}, CONTAINS(Names@row, @cell), {TableB Created column}, DATEONLY(@cell)=[TableA Helper]@row

If either of these are what you wanted, great! If these aren't quite right, shout out and I'll try again.

Kelly

Av1ator

@Kelly Moore Once again, You Rock! Those worked perfectly. Thank you so much again for all your time, help, and guidance.