Joining the columns of the sheet using google sheets formula

1
votes

I have a 2 sheets

Sheet1 and Sheet2

I want to join the columns based on their #LP values. Which I am successful using the formula

=ArrayFormula(IFERROR(
   {
     Sheet1!A1:G,
     vlookup(Sheet1!A1:A, {Sheet2!A1:A, Sheet2!B1:H}, {2,3,4}, false)
   },"")
)

https://docs.google.com/spreadsheets/d/1I-7OYQx6eZ3a1AbxhBgOOpa632XNMh-gxKmGymnpxQ4/edit#gid=1245959930

I also want to skip the columns in result if the row names dynamically detrmining that wont begin with $ symbol.

The expected columns are shown in Green color as shown in the LP_QUERY1 sheet.

1
google-sheets-formula

1 Answers

0
votes

You can use MATCH and FILTER to get the columns you want.

=ArrayFormula(IFNA(
    {
        vlookup(
            Sheet1!A1:A, 
            Sheet1!A1:G,
            ArrayFormula(
                MATCH(
                    FILTER(
                        Sheet1!1:1,
                        (LEFT(Sheet1!1:1,1)="$")+(Sheet1!1:1="#LP")
                    ),
                    Sheet1!1:1,
                    0
                )
            )
            ,0
        ),
        vlookup(
            Sheet1!A1:A, 
            Sheet2!A1:H,
            ArrayFormula(
                MATCH(
                    FILTER(
                        Sheet2!1:1,
                        LEFT(Sheet2!1:1,1)="$"
                    ),
                    Sheet2!1:1,
                    0
                )
            ),
            0
        )
    })
)