 Question related to Excel formula

Regular Contributor

Question related to Excel formula

Hello Everyone,

If letter "e" is entered in row C10:H16 or below, then cell C5:H5 appears listed separated by a comma, depending where "e" is. Also the formula needs to extend if new column or row are inserted.

I want in column J10 to get what is written in cell C5 if in cell C10 is "e" and so on. So for example if "e" is entered in cells C10 and D10, then in cell J10 it will appear #00001(C5) and #00002(D5)

I am using Microsoft Office 2019 Home and student version

Here is a attached file

6 Replies

Re: Question related to Excel formula

The following will work in Excel in Office 2019, Office 2021 and Microsoft 365.

In J10:

=TEXTJOIN(", ",TRUE,IF(\$C10:\$H10="e",\$C\$5:\$H\$5,""))

If you have Office 2019, confirm the formula with Ctrl+Shift+Enter.

Then fill down.

Re: Question related to Excel formula

Another approach using FILTER()

=TEXTJOIN(", ",TRUE,
FILTER(TRANSPOSE(\$C\$5:\$H\$5),TRANSPOSE(C10:H10)="e",""))

Or

=TEXTJOIN(", ",TRUE,
FILTER(\$C\$5:\$H\$5,(C10:H10="e")*(COLUMN(\$C\$5:\$H\$5))>0,""))

Re: Question related to Excel formula

FILTER FUNCTION not there in my office version. Because i am using Microsoft Office 2019 Home and Student version.

Sir, is there another way to solve?

Re: Question related to Excel formula

Take a look in the previous answer given by MR. @Hans Vogelaar

Re: Question related to Excel formula

Thank you so much sir

Re: Question related to Excel formula

Thank you so much sir...