Combine results of multiple IF statements into 1 cell

[Gavin Harrison]

Hi All.

Im looking to combine the results of 3 if statements into one cell as well as add static data from another cell at the end.

I have the below formula so far:

=IFERROR(IF(NOT(A1="HSK"),"", B1&" / "&IF(NOT(C1="HSK"),"",D1&" / "&IF(NOT(H1="HSK"),"", F1&" / ")))&G1,"")

The data in all the cells will be text.

In the example below with the working formula entered in H1, i would like the below result.

	A	B	C	D	E	F	G	H
1	FOH	test 1	HSK	test 2	HSK	test 3	test 4	test 2 / test 3 / test 4

The formula that i have posted above, does kind of work, but sometimes misses out some of the responses.

Any help greatly appreciated.

Thanks

 

[Peter_SSs]

I suggest that you update your Account details (or 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’)

 

[Peter_SSs]

It would also help if you included a few more sample rows and their expected results to show any variation that you might get with your data, preferably with XL2BB

 

[Legacy 143009]

If you are using Office 2019 or above this should work:

=TEXTJOIN(" / ",,IF((TRIM($A$1:$F$1)<>"FOH")*(TRIM($A$1:$F$1)<>"HSK"),$A$1:$F$1,""))

This is an array formula. You must press Ctrl+Shift+Enter together after pasting unless you are using Office 365.

Dear @Peter_SSs,
I was not able to make wildcards work around FOH or HSK. That's why I choose to trim ranges. Why haven't mywild cards worked?

 

[Fluff]

Another option if you have 2019 or newer

Fluff.xlsm
	A	B	C	D	E	F	G	H
1	FOH	test 1	HSK	test 2	HSK	test 3	test 4	test 2 / test 3 / test 4
2	HSK	test 2	HSK	test 3	FOH	test 3	test 4	test 2 / test 3 / test 4
3	FOH	test 2	FOH	test 3	FOH	test 3	test 4	test 4
4
View

Cell Formulas
Range	Formula
H1:H3	H1	=TEXTJOIN(" / ",,IF(A1:E1="HSK",B1:F1,""),G1)