Jtdylktuy

To make things easier, please see the below image first

I am trying to get the cell under Which Event? (H2 cell) to say which events they are invited to.

• Suppose the person is only invited to Sanji, Wedding and Reception, then I want the formula to just show Sanji, Wedding & Reception(on the H2 cell) only if the value is >=1


• If the value is 0 on Vesvaar and Sanji but is >=1 on Wedding and Reception, then I want it to only show Wedding & Reception
  


• If the value is 0 on Vesvaar, Sanji, Reception but it's >=1 on Wedding, then I want it to show Wedding only.

I hope I make sense?

I tried to use the following formula:

=IF((AND(D2>0,E2>0,F2>0,G2>0)),*All*,*Sanji, Wedding & Reception*)

Where if I put a 1 on Vesvaar, Sanji, Wedding, Reception, the cell H2 will change to All events. But if the value is 0 on Vesvaar, Sanji, Reception but it's >=1 on Wedding, then it doesn't work.

I really hope I made sense and that you're able to help me?

An alternative way would be convert the list to binary and use VLOOKUP to find the events

=VLOOKUP((B2 > 0)*8 + (C2 > 0)*4 +(D2 > 0)*2 + (E2 > 0)*1, $H$2:$I$17, 2, FALSE)

The cell > 0 part produces a boolean value that would be converted to 0 or 1 in an arithmetic expression. The columns from left to right represent the digits from most to least significant

The lookup table can be stored anywhere, in some columns/rows faraway or in another sheet, and then you can also hide that sheet/rows/columns

Here is the lookup table for the above

Number  Events

0       None

1       Reception

2       Wedding

3       Wedding, Reception

4       Sanji

5       Sanji, Reception

6       Sanji, Wedding

7       Sanji, Wedding, Reception

8       Vesvaar

9       Vesvaar, Reception

10      Vesvaar, Wedding

11      Vesvaar, Wedding, Reception

12      Vesvaar, Sanji

13      Vesvaar, Sanji, Reception

14      Vesvaar, Sanji, Wedding

15      All

IF You have Office 365 Excel you can use TEXTJOIN as an array formula:

=IF(COUNTIF(D2:G2,">0")=4,"All",TEXTJOIN(",",TRUE,IF(D2:G2>0,$D$1:$G$1,"")))

It must be confirmed with Ctrl-Shift-Enter instead of Enter when exiting edit mode.

If not then you will need to concat the string on your own:

=IF(COUNTIF(D2:G2,">0")=4,"All",MID(IF(D2>0,","&$D$1,"")&IF(E2>0,","&$E$1,"")&IF(F2>0,","&$F$1,"")&IF(G2>0,","&$G$1,""),2,200))