Forum Discussion

Anonymous

Oct 17, 2018

VBA or Excel Formula?

Good morning! I have a spreadsheet where I am trying to combine information from 3 cells into one working formula. I have a formula that puts the pieces together, but I need help figuring out how t...

CrossSub v1.7.2.xlsm2.1 MB

developer

excel

Formulas and Functions

Macros and VBA

JKPieterse

Silver Contributor

Oct 18, 2018

This array formula seems to return an array of the numbers you are looking for, wrapping it in a TextJoin should work:
=IFERROR(INDEX(CandTable[jobOrderID],SMALL(IF(CandTable[candidateID]=$B$3,ROW(CandTable[candidateID]),""),ROW(CandTable[candidateID])-ROW(CandTable[[#Headers],[candidateID]]))),"")

Anonymous

Oct 18, 2018

Jan, thank you for your response. Can you please explain / show how wrapping in a TEXTJOIN would make the formula work?

Thanks!

Ronna

JKPieterse
Silver Contributor
Oct 18, 2018
From the top of my head:
=TEXTJOIN(", ",TRUE,IFERROR(INDEX(CandTable[jobOrderID],SMALL(IF(CandTable[candidateID]=$B$3,ROW(CandTable[candidateID]),""),ROW(CandTable[candidateID])-ROW(CandTable[[#Headers],[candidateID]]))),""))