INDEX + MATCH Formula in MS Excel

 INDEX + MATCH Formula in MS Excel

The INDEX and MATCH formula in Microsoft Excel is a combination of two functions used to retrieve data from a table based on a specified value. The INDEX function returns the value of a cell in a table based on the row and column numbers you specify, while the MATCH function returns the relative position of a value within a range of cells.

The syntax for the INDEX and MATCH formula is:

=INDEX(array, MATCH(lookup_value, lookup_range, [match_type]))

where:

array = the range or table of cells you want to retrieve data from 

lookup_value = the value you want to look up 

lookup_range = the range of cells that contain the lookup value 

match_type = (optional) the type of match you want: 1 for exact match, 0 for closest match less than the lookup value, and -1 for closest match greater than the lookup value. The default is 1 (exact match).

Here's an example of using the INDEX and MATCH formula:

=INDEX(A1:C5, MATCH("apple", B1:B5, 0), 1)

This will return the value in the first column (column A) of the row that contains the value "apple" in column B of the range A1:C5. If "apple" is not found in the lookup_range, the MATCH function will return the #N/A error.