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