Excel Gurus - TEXTJOIN, CONCAT, VLOOKUP, can't find what I need...

Django x2 · 16 Jun 2021 at 12:00

Stuck. It looks like I'm trying to do a vlookup with the reverse functionality of text join.

My scenario is this.

In sheet 1, I have a column with cells that might contain multiple lines of data, such as:

Action Group
<testers>
<managers>
<supervisors>

*Action group is the Row header (a1), the other 3 lines are in one cell (a2) (ie, Sheet 1, A1, A2)

In sheet 2, I have rows that match each action group in column A (because it can appear multiple times in cells in sheet 1) and their relevant permission in column B, ie;

A1 <testers> B1 yes
A2 <managers> B2 maybe
A3 <supervisors> B3 no

What I need to do in sheet 1 is take the cell with the multiple values and lookup the permissions in sheet 2.

So, in Sheet 1, in the cell next to A2, I would see "yes maybe no" (ideally separated with a comma, or semi colon, etc)

Any help, beyond massively appreciated...

Martynt74 · 16 Jun 2021 at 14:48

Any chance you can upload an example file (or email it?)

I've a feeling i understand but not 100% confident so would be good to see the dataset. Could be that some cells need splitting up.

Mekrel · 16 Jun 2021 at 15:02

Not sure this is 100% perfect (you'll get a blank in each return), but quickly mocked up:

Sheet2: B2, copied down

Code:

=ARRAYTOTEXT(UNIQUE(IF(Sheet3!$A$1:$A$6=Sheet2!A2,Sheet3!$B$1:$B$6,"")),0)

Sheet2:

Sheet3:

gpuerrilla · 17 Jun 2021 at 12:56

Most of the problem is lost in translation here

What is in sheet1? Unless you have described sheet1 and image of sheet1 is posted as sheet2 above?

Mekrel · 17 Jun 2021 at 16:51

It's just different naming because I wrote the examples in a spreadsheet I already had open, where sheet1 was already in use.

So my Sheet2 = Op's Sheet1
my Sheet3 = Op's Sheet2

gpuerrilla · 17 Jun 2021 at 21:38

Mekrel said:
It's just different naming because I wrote the examples in a spreadsheet I already had open, where sheet1 was already in use.

So my Sheet2 = Op's Sheet1
my Sheet3 = Op's Sheet2

Lol, sorry Mekrel, I must have needed a coffee on lunch break as I thought you were the OP. Chuckling to myself now.

gpuerrilla · 17 Jun 2021 at 21:40

From memory you have index + match which seems to be better than using vlookups, or if you have the latest office version you can now use Xlookup.

Something like this maybe handy.

You should probably start using named ranges, and not everything needs to be on separate sheets. But I dont know the real life problem and would answer different if I seen the sheet.