Extract value where the row, column intersects (INDEX, MATCH function)

As data in Excel can be large, it is difficult and time consuming for us to find the cell or value wanted.
There are 2 functions that help us extract the information we need using the row, column, or heading of the table as reference.

=INDEX(array, row_num, [column_num])

[array] = Either input value manually using { } (curly braces) and , (comma), or highlight cell range to refer to.
[row_num] = Input the row number of the subject to extract within the [array] (※The very left row selected in the [array] is regarded as the 1st row).
[column_num] = Input the column number of the subject to extract within the [array] (※The very left column selected in the [array] is regarded as the 1st row).

Definition of function A function that extracts data from the [array] based on the selected [row, column number].
EXCEL version Excel2003 ・ Excel2007 ・ Excel2010 ・ Excel2013・ Excel2016

 

=MATCH(lookup_value, lookup_array, [match_type])

[lookup_value] = Select the value or cell to search for.
[lookup_array] = Select cell range to set the search.
[match_type] = Not obligatory. Select ‘1’, ‘0 (zero)’, or ‘-1’. If nothing is written here, it will automatically be ‘1’.

‘1’・・・It will search for the maximum value smaller than the [lookup_value]. The [lookup_array] has to be set in ascending order.
‘0’・・・It will search only for the value that matches the [lookup_value]. There is no need to sort the data in any order. When the [lookup_value] is a character (word), we can use wildcard characters (ambiguous search).
‘-1’・・・It will search for the minimum value bigger than the [lookup_value]. The [lookup_array] has to be set in descending order.

Definition of function A function to search the row number of the [lookup_value] within the [lookup_array].
EXCEL version Excel2003 ・ Excel2007 ・ Excel2010 ・ Excel2013・ Excel2016

 

Example of use) INDEX・MATCH function

The table below shows the results of final exams in a school.
It is time consuming to look for one value with all this data, so we will use functions to save our time.
We will use the INDEX and the MATCH function to extract ‘Nicole Carney’s Algebra test score’ and ‘Connor Durham’s seat number’.

① Extract ‘Nicole Carney’s Algebra test score’ from table.

[array] : Select cell range to refer to, [C4:I25].
[row_num] : ‘Nicole Carney’ is in the 8th row within the [array], so we input ‘8’.
[column_num] : ‘Algebra’ is in the 3rd column within the [array], so we input ‘3’.
=INDEX(C4:I25,8,3)

② Extract ‘Connor Durham’s seat number’ from the table.

[lookup_value] : Surround ‘Connor Durham’ with ” (double quotations). → “Connor Durham”
[lookup_array] : Select cell range to set search, [C4:C25].
[match_type] : We want information that completely matches, so we input ‘0’.
=MATCH(“Connor Durham”,C4:C25,0)

 

We have successfully extracted the necessary information using the 2 functions. These functions are not as complicated as it looks once we get used to them.

 

POINT!

When referring to not only 1 but 2 or more tables, we use the following INDEX function which is a little different from the one described above.

=INDEX(reference, row_num, column_num, [area_num])

[reference] = Select the cells to refer to, dividing them with a , (comma) and surrounding everything with ( ) (brackets).
[row_num] = The same as the above.
[column_num] = The same as the above.
[area_num] = Not obligatory. Identify the number of range (the ○○th range) to refer to within the [reference]. If nothing is identified here, it will refer to the 1st cell found.

 

Select multiple [references] using the INDEX function)

① Extract the ‘price for tomato’ from the table.

[reference] : Select the 2 cells to refer to using a , (comma) and surrounding it with ( ) (brackets).

(B4:C7 , E4:F7)

[row_num] : ‘Tomato’ is in the 4th row within the [reference], so we input ‘4’.
[column_num] : ‘Price’ is in the 2nd column within the [reference], so we input ‘2’.
[area_num] : We want to extract the information from the 2nd [reference] (i.e. cell range), so we input ‘2’.
=INDEX((B4:C7,E4:F7),4,2,2)

Now the price for tomato is successfully extracted.
This function is useful when referring to multiple tables and cell ranges.

 

Extract data that meets multiple conditions (INDEX,MATCH function)

To extract data or information that meets multiple conditions, like ‘○○ and also ××’, we combine the INDEX function and the MATCH function.

 

Example of use) Extract data that meets multiple conditions with INDEX・MATCH function

The table below shows the sales report for January in a computer class. We will extract the sales amount for when ① ‘the branch name is Ohio head office’ and ② ‘the class type is Beginners (course)’.

① Extract the ‘sales amount for class type beginners in the Ohio head office’.

[array] : Select cell range to refer, [B4:D13].
[row_num] : We would like to find the row for ‘Ohio head office’ and ‘Beginners’ within the [array], so we use the MATCH function and set up a formula as follows.

(1) We use the ‘&’ symbol to combine ‘Ohio head office’ (cell F4) and ‘Beginners’ (cell G4).

F4&G4 (This means ‘Ohio head office beginners (course)’)

(2) We use the ‘&’ symbol to combine ‘Branch name’ (cell range [B4:B13]) and ‘Class type’ (cell range [C4:C13]) .

B4:B13&C4:C13 (Texts for each selected row is used as reference here)

 (3) We want information that completely matches, so we input ‘0’ in the end. The MATCH function is complete.

MATCH(F4&G4,B4:B13&C4:C13,0)

 

[column_num] : ‘Sales’ is in the 3rd column within the [array] so we input ‘3’.

Now the formula with the INDEX and the MATCH function is complete below.

=INDEX(B4:D13,MATCH(F4&G4,B4:B13&C4:C13,0),3)

However, with just the above, the ‘#VALUE’ error will appear and the results will not be displayed properly.

Therefore, to finish off the formula, we use { } (curly braces) to surround the whole formula (including the ‘=’ at the beginning). By doing this, it refers to the multiple ranges from both the INDEX and the MATCH function.

{ =INDEX(B4:D13,MATCH(F4&G4,B4:B13&C4:C13,0),3) }

 

Display the last one found when multiple values are found through INDEX, MATCH function

If the INDEX and MATCH function are used together we can extract data in many ways, but there is something we must keep in mind.

That is, that the INDEX function displays the results ‘of the cell/value found first (searching from the top) within the range‘. In other words, if there are ‘2 or more subjects that meet the specified condition‘, only the first one found will be displayed.
To solve this, we can set up a formula to extract not the ‘first’ but the ‘last‘ one found.

 

Example of use) Display the last value found when there are ‘multiple subjects that meet the condition’

The table below shows the expense of a local legislator.
From here, we would like to extract the ‘most recent date of receipt for using a taxi (i.e. the most recent day he used a taxi)’.

On the left (column D) there are more than one ‘taxi fee’, and if we use the INDEX function normally the date displayed will be the one found first (searching from the top) so would be ‘4/2 (April 2nd)‘. By using ‘DMAX’ which helps us to extract the biggest number that meets the specified condition, we will set up a formula to display the correct results. → For more information over the DMAX function, please click here

① Display the ‘most recent receipt date for a taxi fee’.

[array] : Select cell range to refer to, [B4:F14].
[row_num] : We would like to find the ‘most recent date = the biggest date’ for ‘taxi fee’ so we combine the MATCH and the DMAX function as follows.

(1) Follow these steps with the DMAX function.

Find the maximum value of the ‘date of receipt (C3)’ within cell range [B3:F14] that meets the condition [H3:H4].
DMAX(B3:F14,C3,H3:H4)

Next, combine the cell of the last/recent day with the ‘taxi fee’ (cell H4) using the ‘&’ symbol.
DMAX(B3:F14,C3,H3:H4)&H4

With this we will get the ‘most recent day of receipt and taxi fee’,  ‘4/28 Taxi fee‘.

(2) We combine the cell range ‘Date of receipt’ (cell range [C4:C14]) and ‘Use’ (cell range [D4:D14]) using the ‘&’ symbol.

→ It will refer to each text combined within each row.

(3)We want information that completely matches, so we input ‘0’ in the end.

MATCH(DMAX(B3:F14,C3,H3:H4)&H4,C4:C14&D4:D14,0)

 

[column_num] : ‘Date of receipt’ is in the 2nd column within the [array] so we input ‘2’.

The formula of the combination of the INDEX, MATCH and DMAX function is now complete.

=INDEX(B4:F14,MATCH(DMAX(B3:F14,C3,H3:H4)&H4,C4:C14&D4:D14,0),2)

In the end, surround the whole formula with { } (curly braces).

{ =INDEX(B4:F14,MATCH(DMAX(B3:F14,C3,H3:H4)&H4,C4:C14&D4:D14,0),2) }

 

Other than the DMAX function, there are the OFFSET, MAX, COUNTIF functions we can combine. However, no matter what function, the key to building a formula would be ‘how to find/display the last found value‘, so a series of experiments might be good to get used to combining these functions.