Tag: PR

Posted on

INDEX and MATCH based on multiple criteria

The following examples use the INDEX and MATCH worksheet functions in excel 2010 to find a value based on multiple criteria.

Example 1: Data in Columns

Method 1

  1. Start Excel.
  2. Type the following data into a new worksheet: 
       A1: Part   B1:  Code   C1:  Price   D1:  Find Part  E1:  Find Code
   A2: x      B2:  11     C2:  5.00    D2:  y          E2:  12
   A3: x      B3:  12     C3:  6.00    D3:  y          E3:  11
   A4: y      B4:  11     C4:  7.00    D4:  x          E4:  12
   A5: y      B5:  12     C5:  8.00    D5:  x          E5:  11
  3. To retrieve the price for part y with code 12 and return the value to cell F2, type the following formula in cell F2:
    =INDEX($C$2:$C$5,MATCH(D2,IF($B$2:$B$5=E2,$A$2:$A$5),0))
  4. Press CTRL+SHIFT+ENTER to enter the formula as an array formula.The formula returns the value 8.00.
  5. Select cell F2, grab the fill handle, and then fill down to cell F5 to retrieve the price for each part and code combination.

Method 2

A second method yields the same results but uses concatenation instead. The following sample formula may be better for matching data against more than two criteria because it does not require nested IF statements. This method is identical to Method 1 except that you replace the formula in step 3 with the following formula:

=INDEX($C$2:$C$5,MATCH(D2&E2,$A$2:$A$5&$B$2:$B$5,0))

Example 2: Data Arranged in Rows

Method 1

  1. Start Excel.
  2. Type the following data into a new worksheet: 
       A1: Part        B1: x      C1: x     D1: y       E1: y
   A2: Code        B2: 11     C2: 12    D2: 11      E2: 12
   A3: Price       B3: 5.00   C3: 6.00  D3: 7.00    E3: 8.00
   A4: Find Part   B4: y      C4: y     D4: x       E4: x
   A5: Find Code   B5: 12     C5: 11    D5: 12      E5: 11
  3. To retrieve the price for part y with code 12 and return the value to cell B6, type the following formula in cell B6:
    =INDEX($B$3:$E$3,MATCH(B4,IF($B$2:$E$2=B5,$B$1:$E$1),0))
  4. Press CTRL+SHIFT+ENTER to enter the formula as an array formula.The formula returns the value 8.00.
  5. Select cell B6, grab the fill handle, and then fill right to cell E6 to retrieve the price for each part and code combination.

Method 2

A second method yields the same results but uses concatenation instead. The following sample formula may be better for matching data against more than two criteria because it does not require nested IF statements. This method is identical to Method 1 (under Example 2) except that you replace the formula in step 3 with the following formula:

=INDEX($B$3:$E$3,MATCH(B4&B5,$B$1:$E$1&$B$2:$E$2,0))
 
 
Posted on

Adding a Calculated Item to a Pivot Table in Excel 2010

Above is an example of a standard pivot table in Microsoft Excel 2010.  It is set up with financial quaters as column headers and products as Row labels.  I’m interested in seeing the results for the combined sales for the first half and the second half of the year. As you can see I have colour coded these two halves and now I am going to add two “calculated items” showing a total for Q1+Q2 and Q3+Q4.

TAKE ACTION: 

  1. Ensure your cursor is placed onto the Q4 column header as in the image.
  2. Select the “PivotTable tools” tab and click on “options”
  3. In the “calculations” box” select “fields, items, & sets” and then “calculated items”

When this box appears follow these instructions:

  1. Click into the “name” field and enter the new name Qtr1+Qtr2.
  2. Click into “formula” field, remove the 0, double click on Qtr1 in the “Items” field, add + then double click on the Qtr2 from the “Items” field.  Here you are entering a formula which is Qtr1 + Qtr2.
  3. Click the “Add” button and then OK

You will now see that this new column has been added to your PivotTable in Microsoft Excel 2010.

Repeat this process for Qtr3 + Qtr4 and adjust the background colours to match those already on the pivot table.  All going well you should have a pivot table that resembles the one I have pasted below:

You now have a pivot chart showing you the totals for both halves of the year.  Take note that your grand total includes your two new columns so its best to remove that. To learn how to remove the total column in Microsoft Excel 2010, well that’s for the next blog.

Good luck!