I am trying to combine two tables with common headers. The formula below only properly works, if I define the exact start and end row. How can I adjust the formula below to dynamically fetch non blank rows from both the tables.
=iferror({A1:C50;ArrayFormula(hlookup(A1:C1,'Settings'!A1:C50,row(A2:A50),0))})
Sample Input:
Table 1
| col 1 | col2 | col 3 |
|---|---|---|
| abc | 123 | 789 |
| def | 456 | 1212 |
Table 2
| col 1 | col2 | col 3 |
|---|---|---|
| abc | 123 | 78849 |
| jkl | 256 | 1298 |
Desired Output:
Table 3
| col 1 | col2 | col 3 |
|---|---|---|
| abc | 123 | 789 |
| def | 456 | 1212 |
| abc | 123 | 78849 |
| jkl | 256 | 1298 |
CodePudding user response：
Use OUERY:
=QUERY({A1:C1;A2:C;E2:G},"WHERE Col1 IS NOT NULL")
