Lookup Formula to identify YES items and bring back uncoded header results

MHamid · Feb 9, 2021

Hello,

Not sure if the subject lines helped, but it was hard for me to specify what I need.
I am looking for an Excel formula (I know it will be a complicated formula) that will let me know the items identified as YES.

I have a sheet with accounts and within 18 columns I have a YES stating what fruit(s) is/are within the account. For evert YES found within the row, look at the column header and look at another table that has the text for each code. For instance, the table below has two accounts with a YES for the fruit associated with the account. So for account# 123456789, ORG, MNG, LMN, and PPA have a YES. For each of these, YES items, go to another table in another sheet and lookup the header (ORG, MNG, LMN, and PPA) ad bring back the actual text i want to see concatenated (Oranges, Mangoes, Lemons, Papaya) [in the Result Column].

Would anyone know a formula that can give me the results I am looking for in the Results column? I am looking for a formula, not a vba code.

Data:

Account #

APPL

ORG

BNS

CHR

STR

BLB

BLR

MNG

GRF

PNP

AVC

PMG

CRB

LMN

WTM

GRP

GVA

PPA

Results

123456789

No

Yes

No

Yes

No

Yes

No

Yes

Oranges, Mangoes, Lemons, Papaya

987654321

No

Yes

No

Yes

No

Strawberries, Blueberries, Grapes

Lookup Table in Sheet 2:

Code
APPL	Apples
ORG	Oranges
BNS	Bananas
CHR	Cherries
STR	Strawberries
BLB	Blueberries
BLR	Blackberries
MNG	Mangoes
GRF	Grapefruits
PNP	Pineapples
AVC	Avocado
PMG	Pomegranate
CRB	Cranberries
LMN	Lemons
WTM	Watermelon
GRP	Grapes
GVA	Guava
PPA	Papaya

Thank you,

Fluff · Feb 9, 2021

What version of Excel are you using?

I suggest that you update your Account details (click your user name at the top right of the forum) so helpers always know what Excel version(s) & platform(s) you are using as the best solution often varies by version. (Don’t forget to scroll down & ‘Save’)

alansidman · Feb 9, 2021

With Power Query, I created three queries to get the result you are looking for.
1. Import your first table to the PQ Editor. Here is the Mcode for that

Power Query:

let
    Source = Excel.CurrentWorkbook(){[Name="Table1"]}[Content],
    #"Unpivoted Other Columns" = Table.UnpivotOtherColumns(Source, {"Account #"}, "Attribute", "Value"),
    #"Filtered Rows" = Table.SelectRows(#"Unpivoted Other Columns", each ([Value] = "Yes")),
    #"Removed Columns" = Table.RemoveColumns(#"Filtered Rows",{"Value"})
in
    #"Removed Columns"

2. Import your second table to the PQ Editor. Mcode

Power Query:

let
    Source = Excel.CurrentWorkbook(){[Name="Table3"]}[Content]
in
    Source

3. Join the two tables and then pivot the data
l

Power Query:

et
    Source = Table.NestedJoin(Table1, {"Attribute"}, Table3, {"Code"}, "Table3", JoinKind.LeftOuter),
    #"Expanded Table3" = Table.ExpandTableColumn(Source, "Table3", {"Column1"}, {"Table3.Column1"}),
    #"Removed Columns" = Table.RemoveColumns(#"Expanded Table3",{"Attribute"}),
    #"Grouped Rows" = Table.Group(#"Removed Columns", {"Account #"}, {{"DATA", each _, type table [#"Account #"=text, Table3.Column1=text]}}),
    #"Added Custom" = Table.AddColumn(#"Grouped Rows", "Custom", each Table.AddIndexColumn([DATA], "Index", 1, 1)),
    #"Expanded Custom" = Table.ExpandTableColumn(#"Added Custom", "Custom", {"Account #", "Table3.Column1", "Index"}, {"Custom.Account #", "Custom.Table3.Column1", "Custom.Index"}),
    #"Removed Columns1" = Table.RemoveColumns(#"Expanded Custom",{"DATA", "Custom.Account #"}),
    #"Pivoted Column" = Table.Pivot(Table.TransformColumnTypes(#"Removed Columns1", {{"Custom.Index", type text}}, "en-US"), List.Distinct(Table.TransformColumnTypes(#"Removed Columns1", {{"Custom.Index", type text}}, "en-US")[Custom.Index]), "Custom.Index", "Custom.Table3.Column1")
in
    #"Pivoted Column"

End Result

Book5

A

B

C

D

E

1

Account #

1

2

3

4

2

123456789

Oranges

Mangoes

Lemons

Papaya

3

987654321

Strawberries

Blueberries

Grapes

Merge1

MHamid · Feb 9, 2021

Fluff said:
What version of Excel are you using?

I suggest that you update your Account details (click your user name at the top right of the forum) so helpers always know what Excel version(s) & platform(s) you are using as the best solution often varies by version. (Don’t forget to scroll down

Thank you. i just updated my account detail as suggested.

MHamid · Feb 9, 2021

alansidman said:

With Power Query, I created three queries to get the result you are looking for.
1. Import your first table to the PQ Editor. Here is the Mcode for that

Power Query:

let
    Source = Excel.CurrentWorkbook(){[Name="Table1"]}[Content],
    #"Unpivoted Other Columns" = Table.UnpivotOtherColumns(Source, {"Account #"}, "Attribute", "Value"),
    #"Filtered Rows" = Table.SelectRows(#"Unpivoted Other Columns", each ([Value] = "Yes")),
    #"Removed Columns" = Table.RemoveColumns(#"Filtered Rows",{"Value"})
in
    #"Removed Columns"

2. Import your second table to the PQ Editor. Mcode

Power Query:

let
    Source = Excel.CurrentWorkbook(){[Name="Table3"]}[Content]
in
    Source

3. Join the two tables and then pivot the data
l

Power Query:

et
    Source = Table.NestedJoin(Table1, {"Attribute"}, Table3, {"Code"}, "Table3", JoinKind.LeftOuter),
    #"Expanded Table3" = Table.ExpandTableColumn(Source, "Table3", {"Column1"}, {"Table3.Column1"}),
    #"Removed Columns" = Table.RemoveColumns(#"Expanded Table3",{"Attribute"}),
    #"Grouped Rows" = Table.Group(#"Removed Columns", {"Account #"}, {{"DATA", each _, type table [#"Account #"=text, Table3.Column1=text]}}),
    #"Added Custom" = Table.AddColumn(#"Grouped Rows", "Custom", each Table.AddIndexColumn([DATA], "Index", 1, 1)),
    #"Expanded Custom" = Table.ExpandTableColumn(#"Added Custom", "Custom", {"Account #", "Table3.Column1", "Index"}, {"Custom.Account #", "Custom.Table3.Column1", "Custom.Index"}),
    #"Removed Columns1" = Table.RemoveColumns(#"Expanded Custom",{"DATA", "Custom.Account #"}),
    #"Pivoted Column" = Table.Pivot(Table.TransformColumnTypes(#"Removed Columns1", {{"Custom.Index", type text}}, "en-US"), List.Distinct(Table.TransformColumnTypes(#"Removed Columns1", {{"Custom.Index", type text}}, "en-US")[Custom.Index]), "Custom.Index", "Custom.Table3.Column1")
in
    #"Pivoted Column"

End Result

Book5

A

B

C

D

E

1

Account #

1

2

3

4

2

123456789

Oranges

Mangoes

Lemons

Papaya

3

987654321

Strawberries

Blueberries

Grapes

Merge1

I didn't think about using power query to do this. Thank you, I will try it out and get back to you

Fluff · Feb 9, 2021

Thanks for that, how about

+Fluff 1.xlsm

A

B

C

D

E

F

G

H

I

J

K

L

M

N

O

P

Q

R

S

T

1

Account #

APPL

ORG

BNS

CHR

STR

BLB

BLR

MNG

GRF

PNP

AVC

PMG

CRB

LMN

WTM

GRP

GVA

PPA

Results

2

123456789

No

Yes

No

Yes

No

Yes

No

Yes

Oranges, Mangoes, Lemons, Papaya

3

987654321

No

Yes

No

Yes

No

Strawberries, Blueberries, Grapes

Data

Cell Formulas
Range	Formula
T2:T3	T2	=TEXTJOIN(", ",,FILTER(Sheet2!$B$2:$B$19,ISNUMBER(MATCH(Sheet2!$A$2:$A$19,FILTER($B$1:$S$1,B2:S2="Yes"),0))))

+Fluff 1.xlsm

A

B

1

Code

2

APPL

Apples

3

ORG

Oranges

4

BNS

Bananas

5

CHR

Cherries

6

STR

Strawberries

7

BLB

Blueberries

8

BLR

Blackberries

9

MNG

Mangoes

10

GRF

Grapefruits

11

PNP

Pineapples

12

AVC

Avocado

13

PMG

Pomegranate

14

CRB

Cranberries

15

LMN

Lemons

16

WTM

Watermelon

17

GRP

Grapes

18

GVA

Guava

19

PPA

Papaya

Sheet2

MHamid · Feb 9, 2021

Fluff said:
Thanks for that, how about

+Fluff 1.xlsm
A B C D E F G H I J K L M N O P Q R S T
1 Account # APPL ORG BNS CHR STR BLB BLR MNG GRF PNP AVC PMG CRB LMN WTM GRP GVA PPA Results
2 123456789 No Yes No No No No No Yes No No No No No Yes No No No Yes Oranges, Mangoes, Lemons, Papaya
3 987654321 No No No No Yes Yes No No No No No No No No No Yes No No Strawberries, Blueberries, Grapes
Data
Cell Formulas
Range Formula
T2:T3 T2 =TEXTJOIN(", ",,FILTER(Sheet2!$B$2:$B$19,ISNUMBER(MATCH(Sheet2!$A$2:$A$19,FILTER($B$1:$S$1,B2:S2="Yes"),0))))

+Fluff 1.xlsm
A B
1 Code
2 APPL Apples
3 ORG Oranges
4 BNS Bananas
5 CHR Cherries
6 STR Strawberries
7 BLB Blueberries
8 BLR Blackberries
9 MNG Mangoes
10 GRF Grapefruits
11 PNP Pineapples
12 AVC Avocado
13 PMG Pomegranate
14 CRB Cranberries
15 LMN Lemons
16 WTM Watermelon
17 GRP Grapes
18 GVA Guava
19 PPA Papaya
Sheet2

Hi Fluff,

I tried this and I am not getting a #CALC! error for "Empty arrays". The columns that have the YES values are from a formula. So there are formulas in each column that result into a Yes or No. Would that make a difference in your formula? Also not all rows will have a YES.

Thank you

Fluff · Feb 9, 2021

The #CALC error means that the filter didn't return anything, you can handle that like

Excel Formula:

=TEXTJOIN(", ",,FILTER(Sheet2!$B$2:$B$19,ISNUMBER(MATCH(Sheet2!$A$2:$A$19,FILTER($B$1:$S$1,B2:S2="Yes"),0)),""))

MHamid · Feb 9, 2021

MHamid said:
Hi Fluff,

I tried this and I am not getting a #CALC! error for "Empty arrays". The columns that have the YES values are from a formula. So there are formulas in each column that result into a Yes or No. Would that make a difference in your formula? Also not all rows will have a YES.

Thank you

Never mind, I was referencing the wrong header. However, I still got the #CALC! error and that is for those that did not have a YES. So I have updated the formula as shown below and it works perfectly. Thank you!!

Excel Formula:

=TEXTJOIN(", ",,FILTER(Sheet2!$B$2:$B$19,ISNUMBER(MATCH(Sheet2!$A$2:$A$19,FILTER($B$1:$S$1,B2:S2="Yes"),0)),""))

Fluff · Feb 9, 2021

You're welcome & thanks for the feedback.

Lookup Formula to identify YES items and bring back uncoded header results

MHamid

Active Member

Excel Facts

Fluff

MrExcel MVP, Moderator

alansidman

Well-known Member

MHamid

Active Member

MHamid

Active Member

Fluff

MrExcel MVP, Moderator

MHamid

Active Member

Fluff

MrExcel MVP, Moderator

MHamid

Active Member

Fluff

MrExcel MVP, Moderator

Forum statistics

Share this page

Lookup Formula to identify YES items and bring back uncoded header results

Active Member

Excel Facts

MrExcel MVP, Moderator

Well-known Member

Active Member

Active Member

MrExcel MVP, Moderator

Active Member

MrExcel MVP, Moderator

Active Member

MrExcel MVP, Moderator

Forum statistics

Share this page

We've detected that you are using an adblocker.

Which adblocker are you using?

Disable AdBlock

Disable AdBlock Plus

Disable uBlock Origin

Disable uBlock