Lookup and Textjoin help

ganesang · #1 08-25-2021, 12:16 AM

Hi Guys,

I need to lookup and get multiple values in to one cell using excel function.

Sheet 1 have data and column C need to get in sheet 2 column B using functions.

Please check the attachment and help me out

Thanks
Ganesan. G

p45cal · #2 08-25-2021, 02:30 AM

In cell B2:

Code:

=TEXTJOIN(", ",TRUE,UNIQUE(FILTER(Sheet1!$C$2:$C$31,Sheet1!$B$2:$B$31=A2,"None")))

copied down.

ganesang · #3 08-25-2021, 03:22 AM

Thanks much!!

I got the answer my question...

p45cal · #4 08-25-2021, 03:26 AM

Code:

=TEXTJOIN(", ",TRUE,UNIQUE(FILTER(Sheet1!$C$2:$C$31,(Sheet1!$B$2:$B$31=A2)*(Sheet1!$A$2:$A$31="Q1"),"None")))

ganesang · #5 08-25-2021, 03:26 AM

Thank you so much!!!

ganesang · #6 08-25-2021, 03:54 AM

Sorry to ask you again

I have tried in real data but cannot pull exactly what i need or correct

Can you please check the attached file and correct me?

p45cal · #7 08-25-2021, 04:40 AM

Can you highlight some of what's not 'what i need' and what's not correct?

ganesang · #8 08-25-2021, 04:43 AM

In sheet 2 Column C2 result should be RB3043, RB3043, RB3043,

But getting only one may be the same value. But i wanted to get these three

DO NOT ignore duplicated values and need to get that too.

ganesang · #9 08-25-2021, 04:49 AM

I got the answer when i have removed the UNIQUE function.

thanks very much

p45cal · #10 08-25-2021, 04:51 AM

I had assumed you didn't want repeating values in a list.
So take out the unique bit:

Code:

=TEXTJOIN(",  ",TRUE,FILTER(Sheet1!$C$2:$C$380,(Sheet1!$A$2:$A$380=A2)*(Sheet1!$B$2:$B$380=B2),"None"))

in cell C2 copied down.

ganesang · #11 08-25-2021, 04:54 AM

Sorry i forgot to tell you

Anyway thanks much for your time!!

ganesang · #12 08-26-2021, 04:07 AM

Hi I need your help again

Is there any possible to sumproduct the FILTER results numeric value.

Please find the attached and let me know we can sumproduct column D in sheet2

p45cal · #13 08-26-2021, 04:40 AM

For a given member in a given quarter you want separate total Hrs for each Eng?
Not easy using this path.

Are you sure it's not a pivot table you want?

See attached at cell G1, and another, slightly different arrangement at cell Q1. These take seconds to set up.

ganesang · #14 08-26-2021, 05:27 AM

Hi thanks for the reply

Can we have sum using char(10) as below?

if multiple names with multiple values

column C column D
RB3043 7.5
Pearl15 1
BR715 1

or

if same name with multiple values

RB3043 290.75 (this is comes from three places)

Please advise

p45cal · #15 08-26-2021, 05:51 AM

As I said, not easily. The pivot table gives this information.
What version of Excel are you using?

Similar Threads
Thread	Thread Starter	Forum	Replies	Last Post
LOOKUP - Complex lookup with 2 lookups in 1 cell	sglandon	Excel	6	05-05-2016 09:44 AM
Lookup	Tony Singh	Excel	3	03-06-2015 11:03 AM
Lookup	angie.chang	Excel	1	07-27-2012 09:45 PM
Possible Lookup	Karen222	Excel	3	01-10-2012 05:41 AM
LookUp	aztiguen24	Excel	5	05-24-2011 03:57 AM