Mastering Excel: Unleashing the Power of the INDEX MATCH Formula

Excel is a powerful tool for data analysis, and mastering its advanced functions can significantly enhance your efficiency and productivity. One of the most versatile and powerful formula combinations in Excel is the INDEX MATCH. This dynamic duo can replace VLOOKUP for more complex lookups, offering greater flexibility and performance. In this blog, we will delve into the INDEX MATCH formula, exploring its structure, benefits, and practical applications.

Understanding the Basics: INDEX and MATCH Functions

The INDEX Function

The INDEX function returns the value of a cell at the intersection of a specific row and column in a given range. The syntax for the INDEX function is:

excel

INDEX(array, row_num, [column_num])

• array: The range of cells from which you want to retrieve a value.
• row_num: The row number in the array from which to return a value.
• [column_num]: The column number in the array from which to return a value (optional).

The MATCH Function

The MATCH function searches for a specified value in a range and returns the relative position of that value within the range. The syntax for the MATCH function is:

excel

MATCH(lookup_value, lookup_array, [match_type])

• lookup_value: The value you want to search for.
• lookup_array: The range of cells to search.
• [match_type]: The type of match (0 for an exact match, 1 for less than, -1 for greater than).

Combining INDEX and MATCH

When combined, INDEX and MATCH provide a powerful lookup capability. Instead of relying on a single-column lookup like VLOOKUP, INDEX MATCH allows you to perform more complex lookups across rows and columns.

Example: Using INDEX MATCH for a Dynamic Lookup

Suppose you have the following dataset in a table called Sales_Data:

Product ID	Product Name	Sales Region	Sales Amount
101	Widget A	North	$1,000
102	Widget B	South	$2,500
103	Widget C	East	$1,200
104	Widget D	West	$3,000

You want to find the Sales Amount for Widget C. Here’s how you can achieve this using INDEX MATCH:

1. Step 1: Use MATCH to Find the Row Number

   excel
   
   =MATCH("Widget C", Sales_Data[Product Name], 0)
   

   This formula returns 3 because Widget C is in the third row of the Product Name column.

2. Step 2: Use INDEX to Retrieve the Sales Amount

   excel
   
   =INDEX(Sales_Data[Sales Amount], MATCH("Widget C", Sales_Data[Product Name], 0))
   

   This combined formula returns $1,200, the Sales Amount for Widget C.

Benefits of Using INDEX MATCH Over VLOOKUP

• Flexibility: INDEX MATCH can look up values in any direction—left, right, above, or below—unlike VLOOKUP, which can only look up to the right.
• Performance: INDEX MATCH is generally faster, especially with large datasets, because it processes the lookup in two steps rather than one complex operation.
• Stability: VLOOKUP requires the lookup column to be the first column in the range, which can be limiting and prone to errors if the data structure changes. INDEX MATCH does not have this limitation.

Practical Applications

1. Multi-Criteria Lookups: Use INDEX MATCH with multiple criteria to perform complex lookups.

   excel
   
   =INDEX(Sales_Data[Sales Amount], MATCH(1, (Sales_Data[Product Name]="Widget C") * (Sales_Data[Sales Region]="East"), 0))
   

   This formula returns the Sales Amount for Widget C in the East region.

2. Dynamic Ranges: Create dynamic ranges using INDEX MATCH with the INDIRECT function to reference ranges that change based on user input.

3. Data Validation: Improve data validation by using INDEX MATCH to create more sophisticated validation rules.

Conclusion

Mastering the INDEX MATCH formula in Excel opens up a world of possibilities for advanced data analysis and reporting. Its flexibility, performance, and stability make it a superior choice for complex lookups. By incorporating INDEX MATCH into your Excel toolkit, you can handle more sophisticated data challenges with ease and precision.

Stay tuned for more advanced Excel tips and tricks in our upcoming blog posts. Happy Excel-ing!

Count rows that contains partial specific text

Count rows that contains partial specific text :

To count the number of rows that contains partial specific text.

Use formula =countif(range,"*"&text&"*"). 

Please note : 1) It is for to count non-case sensitive text. 2) It count the number of rows that consist specific text , not the occurrence of text. Let say, if text contains two times in a row then it count one instead of two.

Thank you!!

Change the marker symbol in a chart to your own favorite shape

Change the marker symbol in a chart to your own favorite shape:

All you need is to draw any shape in a worksheet from Insert>>Shape . Copy the shape (Ctrl+C), Select the bars and paste into it (Ctrl+P). And here, you get your favorite shape chart, show it in a presentation and make your boss or teacher happy.

Draw any shape in a worksheet from Insert>>Shape

Copy the shape (Ctrl+C), Select the bars 

and paste into it (Ctrl+P)

Lookup an Item value from a table.

Two way lookup :-
Index function returns a value from within a table or range and Match function finds position for an item.

If you need to find an item value from a table where you have multiple rows and columns, then you need first to find the position of a row and a column for a particular item.

To find the position of row - use match function..
To find the position column - again use match function.
To returns value from relative position of row and column - use index functiin.

As in our example - Finding position of Year 2003. Match function is used. Match 2003 in Range E4:E9 (a single column range) where type is 0 means finding exact match. Result would be 4 which means position of row is 4.

Next, Finding position of Quarter Q2. Match function is used. Match Q2 in Range E4:I4 (a single row range) where type is 0 means finding exact match. Result would be 3 which means position of column is 3.

Next, finding an item value of interestion of row and column . Use Index function where array is E4:I9 which is whole table including headings. Index(table_array, row position, column position). Result will be $350

Thank you!

Tip : Use Wildcard to Sum Cells

Use Wildcard to Sum Cells : 

Here is quick tip:

If you have multiple sheets of different stores sales data and the challenge is to make summary report by summing up all the Delhi stores sales or the Mumbai store sales etc.. Then here is a tip:

Use wildcard "*" in sum formula.

=sum('de*'!G4)

It will sum all state that is started with "De" having sales value in G4 cell of each sheet.

CONVERT function.

CONVERT function:

CONVERT function converts a number from one measurement system to another such as inches to centimeters , days into hours etc.

Syntax:
=CONVERT(number, from_unit, to_unit)

Example:
=CONVERT(A2,"day","hr") : Days into Hours
=CONVERT(A2,"day","mn") : Days into Minutes

Below are the units abbreviations:

Thanks

Convert Days to Hour or Days to Minute :

Convert Days to Hour or Days to Minute :

Excel consist a function called CONVERT function. By using CONVERT function you could convert the days into hours or days into minute or hours into minutes or minutes into seconds or hours into seconds or many more.

=CONVERT(A2,"day","hr") : Days into Hours
=CONVERT(A2,"day","mn") : Days into Minutes

Thanks