Dynamic searching using MATCH and INDEX

Match

The MATCH function searches for a specified item in a range of cells, and then returns the relative position of that item in the range. For example, if the range A1:A3 contains the values 5, 25, and 38, then the formula

=MATCH(25,A1:A3,0)

returns the number 2, because 25 is the second item in the range.

Therefore, you can use Match to return the column number when you know information about the data but not the order of the columns.

MATCH(lookup_value, lookup_array, [match_type])

The MATCH function syntax has the following arguments:

lookup_value Required. The value that you want to match in lookup_array. For example, when you look up someone's number in a telephone book, you are using the person's name as the lookup value, but the telephone number is the value you want.

The lookup_value argument can be a value (number, text, or logical value) or a cell reference to a number, text, or logical value.

lookup_array Required. The range of cells being searched.

match_type Optional. The number -1, 0, or 1. The match_type argument specifies how Excel matches lookup_value with values in lookup_array. The default value for this argument is 1.

Index

http://production-scheduling.com/excel-index-function-most-useful/

The format of the index function is: INDEX(array, row_num, [column_num]) where array is the range that contains the table you wish to search. The column argument is only needed if the range contains more than one column.

The INDEX function returns a value from a table when given the row and column. So if you want the value of the second column in the fifth row of the table in the range A1:B12 then use:

=INDEX(A1:B12,5,2). This will return 395000.

Putting Index and Match together allow you to search any column and return a value in any other column.

<ACTIVITY>

Create INDEX/MATCH worksheet

Create Nesting INDEX/MATCH worksheet

</ACTIVITY>