CODEWITHSUNDEEP
excelexcel-formulamatchingtextjoinexcel-tables
Kobe2424
Kobe2424

Reputation: 169

TEXTJOIN of INDEX MATCH matching table headers if values aren't blank

On cell E2 I'm trying to get a TEXTJOIN with delimiter ", " (comma and a space) of INDEX MATCH to the prices you see on the right. For example the price of an apple is $$$ and also A2 is not blank so the formula will match the header of A2 (and not A2 itself) to column G.

Any help will be much appreciated.

enter image description here

Upvotes: 0

Views: 361

Answers (1)

Harun24hr
Harun24hr

Reputation: 36880

Give a try on below formula-

=TEXTJOIN(", ",TRUE,FILTER(H2:H5,ISNUMBER(SEARCH(TRANSPOSE(IF(A2:C2<>"",A1:C1,"")),G2:G5))))

If you do not have FILTER() function then try below formula.

=TEXTJOIN(", ",TRUE,IFERROR(INDEX(H2:H5,IFERROR(MATCH(TRANSPOSE(IF(A2:C2<>"",A1:C1,"")),G2:G5,0),"")),""))

You may need to array entry this formula for non-365 version of excel. Array entry means enter formula as CTRL+SHIFT+ENTER.

enter image description here

Upvotes: 1

Related Questions