I have written a post about merging two single columns or ranges before: Merge two columns with possible blank cells. It demonstrates how to merge two different cell ranges dynamically and that is it. The following examples merge data tables with a criterion or criteria.
Copy cell D2 and paste to E2. Then copy D2:E2 and paste to D3:E11.
Step 1 - VLOOKUP function
VLOOKUP(lookup_value, table_array, col_index_num ,range_lookup)
The Vlookup function looks for a value in the leftmost column of a table and then returns a value in the same row from a column you specify.
Step 2 - lookup_value argument
The formula is entered in cell D2. It uses the lookup value in cell $A2. See the $ sign? It makes column A absolute, meaning it does not change when we copy the formula to column E.
Step 3 - table_array argument
The Vlookup function looks for the value in cell $A2 in the leftmost column of cell range 'Ex 1 - Sheet2'!$A$2:$C$11. It also returns a value from this cell range if there is a match and depending on the chosen column number in argument col_index_num. See next step.
Step 4 - col_index_num argument
col_index_num is a column number determining which column in cell 'Ex 1 - Sheet2'!$A$2:$C$11 we want to get values from. In my example I use COLUMN(B1), it returns 2 for formulas in column D and 3 for formulas in column E.
Step 5 - range_lookup argument
TRUE - Approximate match
FALSE - Exact match
Example 2 - INDEX and MATCH
Here are two lists on two different sheets. They share the same items. The second list has "Items" in column B (blue circle). You can't use the VLOOKUP function now unless you move the Items column to the leftmost column in the cell range. But wait, you don´t have to do that.
This picture shows the merged list using INDEX and MATCH function.
This is an array formula. Type the formula in cell D2. Press and hold CTRL + SHFT simultaneously, then press ENTER once. Release all keys. If you did it right, there are now curly brackets before and after the formula.
Copy cell D2 and paste it to D3:D11.
Explaining array formula in cell D2
Step 1 - Check if value in cell B2 matches 'Ex 4 - Sheet2'!$B$2:$B$11 AND if value in cell C2 matches 'Ex 4 - Sheet2'!$C$2:$C$11