compare two columns in excel different sheets

#Compare two columns in excel different sheets full

array: This is the area where the answer is.The syntax of the INDEX() function is: = INDEX(array, row_num,) (Here is a link to a guide to using INDEX() and MATCH() functions.) Go back to the Summary tab and build the formula using the INDEX-MATCH approach. Note that spaces are not allowed in Table Names so you might want to replace the spaces with underscores.ĭo the same for the other two Divisions such that you have these table names: Specify a Table Name under the Design tab. To change the formatting of the table, click on any table cell and go to Design > Table Styles to select a scheme you prefer or Clear to revert back to the original. This converts the data to an Excel data table. This will prompt you to specify the area of the data table. Press CTRL + T to display the Create Table window. This approach involves converting all the data in the Division tabs into Excel data tables.Ĭlick on any data cell in the Division tab. Pull the formula down to C8 and it should now show the correct Invoiced amount values. The & symbol is used to combine the single quotation marks and the content of cell B6. The table_array parameter now becomes: INDIRECT(“’”&B6&”’!$A$4:$B$24”) Replace “Game Div.” with a cell reference and make sure to keep the single quotation marks.

#Compare two columns in excel different sheets full

The full formula reads: Cell C6 =VLOOKUP($B$4, INDIRECT(“’Game Div’!$A$4:$B$24’”),2,FALSE)Īs the INDIRECT() function is now included in the formula, all that’s left to do is to make the tab names dynamic. The tab name can’t be replaced with a cell reference without putting it inside the INDIRECT() function since the INDIRECT() function will translate the tab name as the address for VLOOKUP().Īs a first step, wrap the table_array argument of VLOOKUP inside the INDIRECT function. Since the table_array changes depending on the Division, the INDIRECT() function will be used here.īefore applying the INDIRECT() function, it’s worthwhile noting that when the tab names contain spaces, such as “Game Div.”, then any cell reference to that tab will need to include single quotation marks, e.g. The first parameter, lookup_value, is fixed, as well as the col_index_num and range_lookup. Where does the INDIRECT function come in? This was our basic VLOOKUP formula: =VLOOKUP($B$4,’Game Div.’!$A$4:$B$24,2,FALSE) The formula uses cell E5 as the address, which points the formula to cell A1. In the same way, if you use another cell (E5) containing “A1” and use that cell as the ref_text in the formula Cell E6 = INDIRECT(E5) To understand how it works, the formula =INDIRECT(“A3”) tells the function to go to cell A3 and returns the value in cell A3, which is “Summary Report”. The syntax of the INDIRECT() function is: = INDIRECT(ref_text,) (Here is a link to a guide to using the INDIRECT() function.) This function gets the right address from a cell or text reference. The INDIRECT() function comes in handy here. In order to make the formula dynamic to account for the different Divisions, the table_array part of the formula should be revised. However, when this formula is pulled down to C8, we get the same value since our tab is fixed to Game Div.

In this example, we want an exact match.Ĭell C6 =VLOOKUP($B$4,’Game Div.’!$A$4:$B$24,2,FALSE)

range_lookup: Select TRUE for an approximate match or false for an exact match.

In this case, since we want the second column in the table array area, we use 2.

col_index_num: This tells which column to look at.

and highlight the entire table and add a few more rows to include future data (‘Game Div.’!$A$4:$B$24) Since you want to be able to pull this formula down, fix this cell reference to $B$4. Lookup value is the cell containing the month, cell B4. tab, this does not need to be an argument.

lookup_value: Since you know that you will be looking at the Game Div.

The syntax for VLOOKUP() is: = VLOOKUP(lookup_value,table_array,col_index_num,)

(Here is a link to a guide for VLOOKUP functions). Once the formula is set up, we can proceed to make the tab part dynamic as well. We are also going to assume that Game Div is fixed and the report has just this tab. To start simply, let’s write the basic VLOOKUP formula first.