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))
 
 

Add Commands To The Quick Access Toolbar Excel 2010

There are two different ways to add commands to the quick access toolbar. The first way involves using the Customize Quick Access Toolbar button, to the right of the toolbar. When we click that, we get this menu:

Customize The Quick Access Toolbar In Excel

In this panel, we can see that the commands that already appear in the toolbar have a tick next to them. To add others, just select them one at a time by clicking on them. To remove one, click on it again to remove the tick. This menu displays only a small selection of the commands that are available for display on the quick access toolbar, though.

Any command you see on the ribbon can be added to the quick access toolbar, which brings us to the second method. Find the command on the ribbon that you want to add, right click on it and select Add to Quick Access Toolbar.

Add To Quick Access Toolbar
For example, many people find that they use the Merge & Center command all the time and it makes sense to have quick and easy access to it from any tab. Just add it to the quick access toolbar!

To remove it again, you can right click on the command in the toolbar and select Remove from Quick Access Toolbar.

Remove From Quick Access Toolbar

There are other controls you can use on the quick access toolbar. For example, if you click on the Customize Quick Access Toolbar button (see above), you can choose to display it below the ribbon. But why on earth would you want to do that?? Another example of Microsoft providing a “feature” that has no benefit whatsoever, but hey we digress.