excel 2010 – Page 6 – STL

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

Start Excel.

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

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))
Press CTRL+SHIFT+ENTER to enter the formula as an array formula.The formula returns the value 8.00.
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

Start Excel.

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

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))
Press CTRL+SHIFT+ENTER to enter the formula as an array formula.The formula returns the value 8.00.
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))

If you want to make your Excel Worksheet appear a little less dull, or maybe you want to draw attention to a certain section of text, why not try rotating the text.

And here’s how you can do it..

– Select the cell with the text in you would like to rotate.

– Click the Home tab on the Ribbon

– Then click a button which has the letters ”ab” (otherwise known as the ”Orientation” button)

– A pop up menu will then appear giving you choices on different rotations.

– If you want more control over the rotation, click Format Cell Alignment. Here, you can enter the number of degrees you wish to rotate the text.

It’s a simple as that but can add a whole new style to your Excel Worksheet.

Blog Categories