how to combine data from two sheets using index/small in an array

gms4b 

Hi Greg,

You may also consider using Power Query and just refresh when new data is added to the source.

I attached a sample file where I created a similar situation.

• Convert each source to a Table (CTRL + T)
• Data Tab >> From Table >> Close and Load To >> Only Create a Connection
• Repeat for the second Table (or for any number of Tables)
• Data Tab >> Combine Query >> Append >> Select the Previous Tables
• Filter the Column to Your Criteria
• Remove the Filtered Column
• Close and Load To >> Select a Destination and we are done

In another Scenario

I can create a third table for All criteria (Data Validation)

Convert the above Query to a Function

Use Table 3 (Criteria) as a variable input to the M Query Function

Close and Load>> To destination

Using Query enables you to combine more than 2 data sources very smoothly

Using a Query Function makes it full dynamic (with Refresh)

If you want to take it to a whole next level:

Adding a little code to the Change event of the cell having Data Validation, Can automatically refresh the Query

Hope that Helps

Nabil Mourad

gms4b 

To extract data from two ranges you may do that sequentially - when error appear on first range start selecting data from the second one. Just repeat you formula in place of "" for the first IFERROR adding to counting ROW() number of records found in first range.

For such sample

formula in G2 will be

=IFERROR(
  INDEX($B$2:$B$12,
    SMALL(
      IF("QC"=$A$2:$A$12,
        ROW($B$2:$B$12)-1,
        ""
      ),
      ROW()-1)
    ),
  IFERROR(
    INDEX($E$2:$E$12,
      SMALL(
        IF("QC"=$D$2:$D$12,
          ROW($E$2:$E$12)-1,
          ""
        ),ROW()-COUNTIF($A$2:$A$12,"QC")-1)
   ),
  "")
)

and drag it down.

Sample file is attached.

Hi Greg

Would be great if you share your file .  Thanks