Excel: Lookup value in a given range and return below few cell data in a cell

Question

Team, I want to get the few below cell values with textjoin or any other methods based on my request. Below is the example data that i have.

From the above example i want to get all the machine names in a single cell with comma or space added.

Note: In this case Machine names are not in the same column. so i have to check the mahine heading and need to pull the below few cells data to output cells as shown in the image.

Sorry if my explanation is not so clear. Please check the output result which i neeed.

Thanks

Answer 1

Well, you may try this formula as shown in image below,

Formula used in cell G2

=IFERROR(TEXTJOIN(",",,INDEX($B$2:$E$13,ROWS($G$1:G1)+{1,2,3},MATCH("Machine",B2:E2,0))),"")

And Fill Down !