0

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

Kobe2424
  • 147
  • 7

1 Answers1

1

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

Harun24hr
  • 30,391
  • 4
  • 21
  • 36
  • 1
    The second one did the trick, without having to use an array formula (it's weird I have 365 but the first one didn't work) Thank you very much! – Kobe2424 Aug 23 '21 at 09:40