Lookup on a Many to one sheet

SherryFox

Okay, this may be a bit tricky, so I would appreciate your help. I will be creating a new sheet, and accessing both of the sheets below. Let's call the screenshot at the top, SheetA, and the one at the bottom SheetB. Now, SheetA is a "one to one" with the TDLs listed in the column on the right, there is only one entry on each row. I will be pulling my TDLs from that column. Now some of the data I require is on SheetB, that is where it gets tricky. I circled 2 entries that contain Multiple TDLs. How can I create a formula that can see those, and let's say I want that email column, for TDL-111, TDL-252 and TDL-292…. all 3 despite being in the same row on SheetB would all provide the email address that is listed in that sheet. Can that be done, and how do I do it? This is necessary, as we have many people that manage multiple TDL, so there are some sheets that are based on people rather than TDL that we need to manage this way.

Thanks in advance for all your help!!!!

SheetA

SheetB

DKazatsky2

Hi @SherryFox,

Index/Connect was just a typo, it's actually Index/Collect - give this a try.

=INDEX(COLLECT({Sheet B Email}, {Sheet B TDL}, HAS(@cell, ProjectIteration@row)), 1)

Make sure to use the correct range names if you already have them created.

Hope this helps,

Dave

Darren Mullen

@SherryFox Are you saying that you want to pull the email from Sheet B into a column in Sheet A based on a matching TDL? If so.. just use index match with has() to find the matching TDL in the multi select column of sheet B.

SherryFox

@Darren Mullen

I was not aware that Index/Match would pull properly on a one to many. Syntax is the same as normal?

Darren Mullen

@SherryFox O! You may have to use index connect instead.

I'm thinking about that. I do matches all the time but need to verify the match with has since I'm shooting from the hip here haha

SherryFox

@Darren Mullen ,

I am not familiar with INDEX/CONNECT. What is the syntax to use for that one?

DKazatsky2

Hi @SherryFox,

Index/Connect was just a typo, it's actually Index/Collect - give this a try.

=INDEX(COLLECT({Sheet B Email}, {Sheet B TDL}, HAS(@cell, ProjectIteration@row)), 1)

Make sure to use the correct range names if you already have them created.

Hope this helps,

Dave

Darren Mullen

@DKazatsky2 Thanks Dave! That's what I get for responding from my phone!

DKazatsky2

https://community.smartsheet.com/discussion/comment/486169#Comment_486169

Ha - We've all been there.

SherryFox

@DKazatsky2 ,

Thanks, let me go try it. I know INDEX/COLLECT, I was just confused by the typo that @Darren Mullen made regarding INDEX/CONNECT. I will be back in a bit with the results.

Darren Mullen

@SherryFox yes, Sorry!!!! Dave's got you covered.

SherryFox

@DKazatsky2 ,

Thanks so much, that worked!!!!