Merge tables based on a condition

This article demonstrates techniques on how to merge or combine two data sets using a condition. The top left data set has three headers, Item, Price, and Category. The top right data set has three columns,  header names are Item, Salesperson, and Phone.

Both data sets contain column Item, this allows you to combine them using the column item as a condition. I will in this article use different Excel functions to merge these data sets. The two last examples show how to combine data sets if a cell contains a given value and how to match multiple columns in order to combine the data sets.

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 columns dynamically and that is it. The following examples merge data tables with a condition or criteria.

1. How to join data sets using the VLOOKUP function

The image above shows two tables on two different worksheets. They only share the same items, however, they are not in order making it harder and tedious to combine manually.

Let us use the Item value in the first table and search for it in the second table. The VLOOKUP function allows us to retrieve corresponding values.

Here is the table in worksheet named sheet1 merged with the table located on worksheet named sheet2, see image above.

Formula in cell D2:

Copy cell D2 and paste to E2. Then copy cell range D2:E2 and paste to cell range D3:E11.

Back to top

Explaining formula in cell D2

I do recommend using the "Evaluate Formula" tool when debugging and examining formulas, it allows you to evaluate different parts of the formula individually. Going through the formula is as easy as press with left mouse button oning a button, Excel shows the result of each expression.

Here is how to start this tool. Select the cell you want to check out. Go to tab "Formulas" on the ribbon, press with left mouse button on the "Evaluate Formula" button. This opens a dialog box, see image above.

The "Evaluate Formula" dialog box shows the formula and the underlined expression is what is next to be evaluated, the most recent result from an evaluation is italicized.

Press with left mouse button on the "Evaluate" button to move to the next step in the formula calculation, keep press with left mouse button oning until you are satisfied or the final result appears. That result always match what the cell shows. Press with left mouse button on "Close" button to dismiss the dialog box.

Step 1 - VLOOKUP function

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.

The VLOOKUP function has four arguments: lookup_value, table_array, col_index_num ,range_lookup

VLOOKUP(lookup_value, table_array, col_index_num ,range_lookup)

The Item column is the leftmost column which is needed in order to use the VLOOKUP function, remember the Item column is shared by both data sets.

Step 2 - lookup_value argument

The formula is entered in cell D2 and it uses the corresponding lookup value in cell $A2 to find a match in the second data set located on worksheet Sheet2.

VLOOKUP($A2, 'Ex 1 - Sheet2'!$A$2:$C$11, COLUMN(B1), FALSE)

becomes

VLOOKUP("AS 83", 'Ex 1 - Sheet2'!$A$2:$C$11, COLUMN(B1), FALSE)

See the $ sign? It makes column A absolute, meaning the column part of cell reference does not change when we copy cell D2 and paste to cell E2.

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 based on the chosen column number in argument col_index_num. See the next step where I explain the col_index_num argument.

VLOOKUP("AS 82", 'Ex 1 - Sheet2'!$A$2:$C$11, COLUMN(B1), FALSE)

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.

I use COLUMN(B1) in this example, it returns 2 for formulas in column D and 3 for formulas in column E. Cell reference B1 is a relative cell reference meaning it changes when you copy the cell and pastes it to another cell.

Step 5 - range_lookup argument

The range_lookup argument lets you chose if you want the VLOOKUP function to perform an aproximate or exact match. We want an exact match so we will use FALSE.

TRUE - Approximate match
FALSE - Exact match

An approximate match requires the values in the leftmost column in the table_array to be sorted in ascending order. This is not required when you use an exact match.

Step 6 - Return value

The VLOOKUP function finds a match on row 3 and returns the value from the second column on worksheet Sheet2 which is "Celia" and returns that value to cell D2 on worksheet Sheet1.

Back to top

2. Merge data using the INDEX and MATCH functions

The image above shows two lists on two different sheets. They share the same items, however, they are not sorted. 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 move the Items column, you can use a formula that combines two Excel functions.

If you combine the INDEX and MATCH function you can build a formula that is more versatile and as easy to use as the VLOOKUP function. This picture shows the merged list using a formula containing the INDEX and MATCH functions.

Formula in cell D2:

Copy cell D2 to cell range D3:D11.

Formula in cell E2:

Copy cell E2 to cell range E3:E11.

The only difference between these two formulas is the column number that the INDEX function uses to determine which value to extract.

Back to top

Explaining formula in cell D2

The following link takes you to an explanation of how the "Evaluate Formula" tool works.

Step 1 - Find the relative position with the value in cell A2 in cell range 'Ex 2 - Sheet2'!$B$2:$B$11

The MATCH function returns a number representing the relative position of a given value (lookup_value argument) in a one-dimensional array or cell range (lookup_array argument).

MATCH(lookup_value, lookup_array, [match_type])

The match_type argument can be -1, 0, and 1. 0 (zero) is an exact match. Check out the other match_type arguments.

MATCH('Ex 2 - Sheet1'!A2,'Ex 2 - Sheet2'!$B$2:$B$11,0)

returns 2. This means that the lookup_value is found in the second position in cell range 'Ex 2 - Sheet2'!$B$2:$B$11 which you can verify if you check out the array above.

Step 2 - Return a value of the cell at the intersection of a particular row and column

The INDEX function returns a value from a cell range or array based on a row and column number (both optional).

INDEX(array, [row_num], [column_num])

INDEX('Ex 2 - Sheet2'!$A$2:$C$11,MATCH('Ex 2 - Sheet1'!A2,'Ex 2 - Sheet2'!$B$2:$B$11,0),1)

returns value "Celia" from row 2 and column 1 in cell range 'Ex 2 - Sheet2'!$A$2:$C$11.

Back to top

3. Merge data sets based on a partial match - wildcard character

Here we want to find the color for each category, the first data set contains category values in the third column. The second list contains multiple categories in column A and a corresponding color in column B.

This means that multiple categories may share the same color. This picture below shows the merged list.

Formula in cell D2:

Copy cell D2 and paste it to cell range D3:D11.

You can use the VLOOKUP function in this example also, as long as you search in the leftmost column. I do, however,  prefer the INDEX and MATCH functions.

You can use other characters, as well: Wildcard characters

Back to top

Explaining the array formula in cell D2

The following link takes you to an explanation of how the "Evaluate Formula" tool works.

Step 1 - Search for a text string in an array of cell values and return relative position of first match in array

The MATCH function allows you to append asterisk characters before and after the lookup_value argument allowing you to search a cell to see if it contains a given value.

The MATCH function will match the entire cell if you don't append asterisks to the argument.

MATCH(lookup_value, lookup_array, [match_type])

MATCH("*"&C2&"*",'Ex 3 - Sheet2'!$A$2:$A$5,0)

becomes

MATCH("*A*", {"A,B";"H,I,J";"F,G";"C,D,E"},0)

and returns 1. The first value in the array is a match, it contains value "A". The MATCH function returns only the first match.

Step 2 - Return a value of the cell at the intersection of a particular row and column

INDEX(array, [row_num], [column_num])

INDEX('Ex 3 - Sheet2'!$B$2:$B$5,MATCH("*"&C2&"*",'Ex 3 - Sheet2'!$A$2:$A$5,0))

becomes

INDEX({"Yellow"; "Red"; "White"; "Brown"}, 1)

and returns "Yellow" in cell D2.

Back to top

4. How to merge data using two or more criteria with the COUNTIFS function

This example demonstrates how to match multiple values from two different columns, both values must match.

Below is the merged list. The COUNTIFS function allows you to match multiple columns simultaneously.

Array formula in cell D2:

This is an array formula and you need to enter this formula differently than a regular formula.

1. Type the formula in cell D2.
2. Press and hold CTRL + SHFT simultaneously.
3. Then press ENTER once.
4. Release all keys.

If you did it right, there are now curly brackets before and after the formula shown in the formula bar.

Copy cell D2 and paste it to D3:D11.

Explaining array formula in cell D2

The following link takes you to an explanation of how the "Evaluate Formula" tool works.

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

The COUNTIFS function allows you to use multiple arguments, in fact, up to 254 additional arguments.

It works just like the COUNTIF function meaning it counts the number of cells based on a condition, however, it also lets you use multiple criteria requiring you to enter the formula as an array formula.

COUNTIFS(criteria_range1, criteria1, [criteria_range2, criteria2]…)

We are going to use two conditions an compare them to two different columns, 1 equals True and 0 (zero) equals False. If both match on the same row the returned array returns 1 on the same relative position in the array.

COUNTIFS(B2,'Ex 4 - Sheet2'!$B$2:$B$11,C2,'Ex 4 - Sheet2'!$C$2:$C$11)

returns {0; 0; 1; 0; 0; 0; 0; 0; 0; 0}

Step 2 - Find the relative position of 1

The MATCH function looks for 1 in the array and returns a number representing the relative position in the array. It returns an error value if not found at all.

MATCH(lookup_value, lookup_array, [match_type])

MATCH(1,COUNTIFS(B2,'Ex 4 - Sheet2'!$B$2:$B$11,C2,'Ex 4 - Sheet2'!$C$2:$C$11),0)

becomes

MATCH(1,{0; 0; 1; ... ; 0},0)

and returns 3 meaning number 1 is in position 3 in the array.

Step 3 - Return a value of the cell at the intersection of a particular row and column

The INDEX function then returns the value on the same row from cell range A2:A11 using the result from the MATCH function.

INDEX(array, [row_num], [column_num])

INDEX('Ex 4 - Sheet2'!$A$2:$A$11, MATCH(1, COUNTIFS(B2, 'Ex 4 - Sheet2'!$B$2:$B$11, C2, 'Ex 4 - Sheet2'!$C$2:$C$11), 0))

returns "Linaeve" in cell D2.

Back to top

Back to top

5. Update list with new values if possible

Overview

Updating a list using copy/paste is a boring task. This blog article describes how to update values in a price list with new values.

Sheet 1 is the old price list. It contains 5000 products and amounts.

Sheet2 is the new price list. It contains 2000 random products from the old price list with new prices. (There are no new products)

How to create a new list with the latest prices.

Copy all products from sheet1 into sheet 3

Now let us find out if a new price exists.

Sheet3, formula in B2:

Double press with left mouse button on lower right corner of cell B2.

The formula is copied down to last adjacent product.

Sheet3, formula in C2:

Double press with left mouse button on lower right corner of cell C2 to copy the formula as far down as needed.

I created column B to make sure the values are the same as in sheet2 and to make it easier to understand the formula in cell C2.

Explaining the formula in cell C2

Step 1 - Find out if a new price exists

Match returns the relative position of an item in (Sheet2!$A$2:$A$2001) that matches a specified value (A2).

MATCH(A2, Sheet2!$A$2:$A$2001, 0) returns #N/A. This means "Product AT" can´t be found in sheet2.

ISERROR(MATCH(A2, Sheet2!$A$2:$A$2001, 0)) returns TRUE.

Step 2 - Identify sheet and price

The formula returned TRUE in cell C2. This means there is no new price. Let´s find the old price in sheet1 instead.

INDEX(Sheet1!$B$2:$B$5000, MATCH(A2, Sheet1!$A$2:$A$5000, 0))

MATCH(A2, Sheet1!$A$2:$A$5000, 0) returns 1. "Product AT" is found on the first row on sheet1.

INDEX(Sheet1!$B$2:$B$5000, 1) returns $43,90.

If formula in step 1 had returned False

If the formula had returned False in cell C2, we would need to look for the new price in sheet2.

INDEX(Sheet2!$B$2:$B$2001, MATCH(A2, Sheet2!$A$2:$A$2001, 0))

Get excel example file
automate_excel_pricelist.zip (300 KB)

Combine merge category

More than 1300 Excel formulas

Excel categories

Comments (12)