Tag Archives: Match Function

Example of in Index/Match Function

Here I have a table with employees work hours spread over a 7 day week. The employees are on the rows and the days of the week in the columns.  I have created two criteria cells, one for the employee and one for the days.  I then created another cell which is where I will enter my index and match fuctions to automatically find out the hours worked when the criteria cells are populated.  To make this more attractive I have added a combo box using the data valadation feature to enter the criteria as below;

 

 

 

 

Here is the example of the function I used to achieve this result.

=INDEX(C3:J9,MATCH(D12,B3:B9,0),MATCH(D13,C2:J2,0))

For your reference Monday is in cell B2 and John is in cell C3

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

Excel Courses – Retrieve Data using the Index and Match Functions

 

If you have an Excel Worksheet that contains Data relating to the hours of training for each Employee for Microsoft Excel Training London on Monday, Tuesday and Wednesday, you may want to return the hours trained for a particular employee on a particular day in another Excel Workbook or Excel Worksheet.

The spreadsheet shown below is the data set we will use:

Data Table Excel Workbook

 

 

 

 

 

 

 

 

 

The Excel INDEX function can return an item from a specific position in a table of Excel data using Row and Column headings. The MATCH Function can return the position of a value in a list. The INDEX and MATCH functions used together provide a flexible and powerful method for extracting data from a table.

We start with the INDEX function. The Index function has three arguments(Array,Row_Num,[Col_Num])

The Array argument is the range of cells we are trying to retrieve the data from. The Row_Num argument is the Row you are retrieving from and Col_Num the column you are retrieving from. The value at the intersection point of the Row and Column arguments will be returned from the cell range from the Array argument. We use the MATCH function to assign the Row and Column to the second and third arguments of the INDEX function.

The Match function has three arguments:

(Lookup_Value,Lookup_Array,[Match_Type])

Here is the spreadsheet we will use to create the formula which will return the number of hours. The formula is entered into cell B5 of the Index and Match formula sheet:

Index and Match formula sheet

 

 

 

 

 

 

 

 

 

The screenshot below shows the result and notes:

=INDEX(‘[Data Table Excel Workbook.xlsx]Sheet1′!$B$2:$D$5,MATCH(B2,’[Data Table Excel Workbook.xlsx]Sheet1′!$A$2:$A$5,FALSE),MATCH(B3,’[Data Table Excel Workbook.xlsx]Sheet1′!$B$1:$D$1,FALSE))

Enter a value into B2 and a value into B3 of the Index and Match formula sheet and in cell B5 of the Index and Match formula sheet it will return the hours of Excel Training completed for that Employee.

Formula Explanation Notes:

1. The first argument of the Index function relates to just the hourly figures in the Data Table Excel workbook(B2:D5).

2. The Row Argument of the Index function uses the Match function to match the value of cell B2 in the Index and Match Formula sheet to the corresponding value in the Data Table Excel Workbook(A2:A5).

3. The Column Argument of the Index function uses the Match function to match the value of cell B3 in the Index and Match Formula sheet to the corresponding value in the Data Table Excel Workbook(B1:D1).

4.The formula returns the value form the Array argument that represents the row and column values specified.

5. The FALSE statement returns the exact value found.