INDEX AND MATCH FORMULA IN EXCEL

=INDEX(array, row_num, [column_num])

array – a range of cells or an array constant.

row_num – Required, unless column_num is present. Selects the row in array from which to return a value. If row_num is omitted, column_num is required.

column_num – Optional. Selects the column in array from which to return a value. If column_num is omitted, row_num is required.

MATCH(lookup_value, lookup_array, [match_type])

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.

Create a drop down list through data validation