Index and Concat / Concatenate from another sheet

Question

I have a data sheet which i want to index pull into another sheet and also concanate or concat 2 or more columns with array formula.

sheet link here https://docs.google.com/spreadsheets/d/1GUI-Gl7HDNGg5V2wM4ossUn46cp7VHRBZ_t4la2gDH0/edit?usp=sharing

data sheet https://docs.google.com/spreadsheets/d/1GUI-Gl7HDNGg5V2wM4ossUn46cp7VHRBZ_t4la2gDH0/edit#gid=0

result required sample sheet https://docs.google.com/spreadsheets/d/1GUI-Gl7HDNGg5V2wM4ossUn46cp7VHRBZ_t4la2gDH0/edit#gid=1226812843 you can see in result sheet column B and F which are merged from "data sheet column b c d and h i simultaneously"

Answer 1

Use an { array expression }. Insert > Sheet and put this formula in cell A1 of the new sheet:

=arrayformula( 
  { 
    DATA!A1:A, 
    trim( 
      DATA!B1:B & " " & 
      if( iserror(search(DATA!C1:C, DATA!B1:B)), DATA!C1:C, "" ) & " " & 
      if( iserror(search(DATA!D1:D, DATA!B1:B)), DATA!D1:D, "" ) 
    ), 
    DATA!E1:G, 
    trim(DATA!H1:H & if(len(DATA!I1:I), " / " & DATA!I1:I, "")), 
    DATA!J1:U 
  } 
)

The formula will only append the values in columns C and D to the value in column B when they do not not already appear in the value in column B.