INDEX and MATCH Function: Advanced Lookup Techniques in Google Sheets

The INDEX and MATCH functions in Google Sheets are used together to perform advanced lookups, providing a more flexible alternative to VLOOKUP or HLOOKUP.

We have some data with product details, including Stock Number, and we want to find the Sell Price of the product with a specific stock number (5621-45-132). To do this, we can combine the MATCH and INDEX functions in a single formula. Here’s how they work:

Below is a product table that lists various types of shoes. Using the MATCH and INDEX functions, we can efficiently retrieve the price and product name based on the stock number. This approach is highly beneficial for providing accurate and organized data to the owner or seller, ensuring easy access to crucial information for inventory management and decision-making.

B2:F10	B	C	D	E	F
2	Product Name	Stock Number	Category	Sell Price	Cost Price
3	PUMA RUNNING SHOES	8392-76-543	Running	$ 105.00	$ 85.40
4	REEBOK TRAINING SHOES	5621-45-132	Training	$ 89.00	$ 72.80
5	ASICS JOGGING SHOES	6743-32-890	Jogging	$ 125.00	$ 95.50
6	UNDER ARMOUR RUNNING SHOES	3910-56-278	Running	$ 115.00	$ 87.40
7	SKECHERS WALKING SHOES	4792-68-113	Walking	$ 95.00	$ 72.60
8	FILA TRAINING SHOES	9153-73-421	Training	$ 97.00	$ 88.40
9	SALOMON TRAIL RUNNING SHOES	3947-19-845	Trail	$ 145.00	$ 110.70
10	BROOKS RUNNING SHOES	2156-42-768	Running	$ 129.00	$ 100.90

MATCH Function

Syntax:
=MATCH(search_key, range, [search_type])

search_key : The value to search for.
range : The range to search within.
search_type : 1 (default): Finds the largest value less than or equal to the lookup value (requires data to be sorted in ascending order).
0: Finds an exact match.
-1: Finds the smallest value greater than or equal to the lookup value (requires data to be sorted in descending order).

The MATCH function finds the position of a value within a row or column. Here its finds the position of the stock number 5621-45-132 in the column containing stock numbers (C3:C10).

Example:
= MATCH (B15, $C$3:$C$10, 0)

This returns 2, as the stock number 5621-45-132 is in the 2nd position.

INDEX Function

This function retrieves a value from the Sell Price column ( E3:E10 ) based on the row number provided.

Syntax:
= INDEX (reference, [row], [column] )

reference: The range of cells to retrieve data from.
row: The row number within the range.
column (optional) : The column number within the range (used for multi-column ranges).

Example:
= INDEX ($E$3:$E$10, 2, 1)
This directly retrieves the value in the 2nd row of the E3:E10 range, which is $89.00.

How INDEX and MATCH Function Work Together:

Instead of hardcoding the row number (e.g., 2) in the INDEX function, we replace it with the result of the MATCH function. This makes the formula dynamic.

1st = MATCH (B15, $C$3:$C$10, 0)
2nd = INDEX ($E$3:$E$10, 2, 1)

You need to combined the both example given above in single formula as like below;

Replace the second function second argument by first function, as like below and you found the result as you need.= INDEX($E$3:$E$10, MATCH(B15, $C$3:$C$10, 0))

Explanation of the Combined Formula:

MATCH(B15, $C$3:$C$10, 0): Finds the row number of the stock number B15 (i.e., 5621-45-132) in column C .
INDEX($E$3:$E$10, …): Retrieves the corresponding Sell Price from column E , using the row number provided by the MATCH function.

This combined formula dynamically tracks the position of the stock number ( 5621-45-132 ) and retrieves the desired value without manual adjustments.

If you want to find the Product Name based on a specific Stock Number, here’s you need to adjust the INDEX-MATCH function:

Steps to Retrieve the Product Name:

The MATCH function identifies the row number of the desired Stock Number in column C.

Example:
=MATCH(B15, $C$3:$C$10, 0)This will return the position of the stock number 5621-45-132 in the range C3:C10. For this example, the result will be 2.

The INDEX function retrieves the value from the Product Name column ( B ) based on the row number returned by the MATCH function.
Example:

= INDEX ($B$3:$B$10, MATCH(B15, $C$3:$C$10, 0))

If the stock number 5621-45-132 is in the 2nd row of the range, the formula will return:
REEBOK TRAINING SHOES

This method works seamlessly for any stock number in the given range.

The INDEX and MATCH functions in Google Sheets and Excel are powerful tools for retrieving data based on specific criteria. The INDEX function is used to return a value from a specified cell or range, based on a given row and column number. The MATCH function complements this by identifying the position of a value within a range, which can then be used as an input for INDEX.

As provided above example, by combining these two functions, you can dynamically search for a value (e.g., a stock number) in one column and retrieve the corresponding value (e.g., product name or cost price) from another column. This combination is more flexible and robust than using a simple VLOOKUP, especially when dealing with large or complex datasets.

Summary and Conclusion

The INDEX and MATCH combination is a versatile alternative to VLOOKUP or HLOOKUP. It allows you to perform lookups in any direction (left, right, or even in reverse), making it highly adaptable. Moreover, it avoids the structural limitations of lookup functions that rely on hard-coded column numbers.

Pros and Cons of INDEX and MATCH

Pros:

Flexibility: Can retrieve data from columns to the left of the lookup range (unlike VLOOKUP).
Efficiency: Handles large datasets with less computational overhead than alternatives.
Dynamic Adjustments: Automatically adapts to changes in data structure, such as inserted or deleted columns.
Accuracy: Provides precise control over row and column references, reducing errors.

Cons:

Complexity: Requires a deeper understanding of how ranges and references work, making it harder for beginners.
Not Intuitive: Combining the two functions can feel cumbersome compared to simpler functions like VLOOKUP.
Error Sensitivity: Mismatched ranges or incorrect references can lead to errors that are not immediately obvious.

The INDEX and MATCH functions offer a highly adaptable and reliable solution for data retrieval tasks. While they may require more setup and understanding than basic lookup functions, their flexibility and efficiency make them indispensable tools for advanced spreadsheet users.

YOU MUST KNOW?

Did you like this article?

If you found this post helpful, feel free to share it with your network using the social media buttons on X or via email located on the left below.

For any questions or assistance regarding the MATCH and INDEX function in Google Sheets or Excel, feel free to reach out in the comments. The CountLen team is dedicated to providing prompt and effective solutions, ensuring that all your queries are addressed swiftly. If you come across any inaccuracies or misleading information, don’t hesitate to share your feedback in the comments. We’re here to help you make the most of your data!