# Multiple If formulas with conditions

Copper Contributor

# Multiple If formulas with conditions

I am in need a formula that will look at multiple conditions for the data I have. My sample data is below.

If the value in A is the same, and the value in B is the same, then join the values in C and put them in D. The formula I have for that function works (=IF(A2=A3, IF(A3=A4, TEXTJOIN(",",TRUE,C2,C3,C4),TEXTJOIN(",",TRUE,C2,C3)),"").

What I need the complete formula to do is this:

If the value in A is the same, and the value in B is the same, then join the values in C and put them in D, but if the value in A does not match, then copy the value from C2 to D2.

I hope I explained that well enough. I'm thinking it needs to be a nested formula, but I cannot figure it out. Any help will be greatly appreciated.

11 Replies

# Re: Multiple If formulas with conditions

This task is best handled by GROUPBY but that function is not yet available in general release of 365.

For now I offer a garden variety Reduce -

``````=LET(
uniqueID, SORT(UNIQUE(ID)),
Organize, LAMBDA(acc, v,
LET(
details, HSTACK(Company, Plan),
record, FILTER(details, ID = v),
comp, TAKE(record, 1, 1),
joined, ARRAYTOTEXT(TAKE(record, , -1)),
VSTACK(acc, HSTACK(v, comp, joined))
)
),
)``````

# Re: Multiple If formulas with conditions

An alternative which works in legacy Excel 2013 could be Power Query. In the attached file you can add data to the blue dynamic table. Then you can click in any cell of the green table and right-click with the mouse and select refresh to update the green result table.

# Re: Multiple If formulas with conditions

*For the future*:

``````=LET(
row_fields, HSTACK(ID, Company),
GROUPBY(row_fields, Plan, ARRAYTOTEXT, , 0)
)``````

# Re: Multiple If formulas with conditions

I've never used VBA or macro before. Once I copy this into the developer, what do I do?

# Re: Multiple If formulas with conditions

I've never used power query before, so I'm not sure I understand exactly what you did here.

# Re: Multiple If formulas with conditions

Both replies by @Patrick2788 are cell formulas, not VBA. The first one will work in Excel in "regular" Microsoft 365, the second one is currently (early May 2024) only available to Microsoft 365 Insiders.

# Re: Multiple If formulas with conditions

Oh. It looks like VBA coding. I will try it.

# Re: Multiple If formulas with conditions

That formula really has star quality!  Both the HSTACK and ARRAYTOTEXT nail it.  .

# Re: Multiple If formulas with conditions

No vba is used. The only thing you may need to account for in the formulas are the named items:

If your data has these defined names, you can plug the formula in and calculate.

@Peter Bartholomew - hats off to the Excel team for PIVOTBY/GROUPBY! I really like how these formulas read with the ETA. The only change I'd make is having the [filter array] argument appear after the 'function' argument.

# Re: Multiple If formulas with conditions

I tried plugging in the formulas suggested, and they didn't work. Maybe it's just me.

# Re: Multiple If formulas with conditions

Which formula didn't work?  The second uses GROUBY which is only available on the insider's beta channel at the moment.  Otherwise REDUCE 5.3 from above should work with non-insider channels.