Microsoft Excel INDEX() and MATCH() Function Combined

 Combining the INDEX() and MATCH() functions in Microsoft Excel allows you to dynamically retrieve values from a range based on specific criteria. Here's how to use them together:

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

Let's break down each part of the combined formula:

INDEX(array, ...): This part of the formula specifies the range of cells or array from which you want to retrieve the value.

MATCH(lookup_value, lookup_array, [match_type]): This part of the formula searches for the lookup_value within the lookup_array and returns the relative position of that value.

Here's an example:

Suppose you have a list of product names in cells A1:A5 and their corresponding prices in cells B1:B5. You want to find the price of a specific product, let's say "Product A".

=INDEX(B1:B5, MATCH("Product A", A1:A5, 0))

This formula searches for "Product A" within the range A1:A5 (the list of product names) using the MATCH() function. Then, it uses the relative position returned by MATCH() to retrieve the corresponding price from the range B1:B5 using the INDEX() function. If "Product A" is found in the third position in the list, the formula will return the price from the third position in the range B1:B5. If "Product A" is not found, the formula will return an error value.