excel INDEX - Free Excel Tutorial

Break ties with helper COUNTIF and column

Suppose you got a task to adjust the values that contain the ties; what would be your first attempt to break the ties of the given value? If you are wondering about doing this task manually, let me add that you will end up exhausted, and there is a probability that you will not complete… read more »

Find the Closest Data to the Data Provided in Excel

In our daily work, we may encounter such an issue that to find the closest value to a certain value. In fact, Excel internal functions can help us solve this problem. In today’s article, we will show you how to find the student whose score value is closet to the score provided with help of… read more »

Creating a Table with Automatic Row Numbering

When creating an Excel Table, you may use a calculation relying on the ROW function to insert row numbers automatically. The formula in A2, which has been copied down, is as follows: =ROW()-ROW(Table1[#Headers]) Note: the default table name is Table1. And #Headers is the header in the Table1. Let’s See How This Formula Works: When… read more »

Get Address of First Cell in Range

We will learn how to get address of first cell in range in this post. At Instance, we must determine the address of first cell in range to use as the data table’s value. For the DATA table, we have a range of value values in the form of an array that is sent to… read more »

Convert State Names To Abbreviations

Assume that you got a task to convert the full state’s name into the abbreviations in MS Excel, and for doing this task, you might choose to do it manually, which is an acceptable way only if you don’t have any other way to do it! But fortunately, there is a way to convert a… read more »

Filter or Extract with a Partial Match

Assume you have a table consisting of a few cells having few values, and you want to filter out the set of records with the partial match. You might take it easy and would prefer to manually filter out the desired partial matching values into another table without any need for the formula; then congratulations… read more »

Find and Replace Multiple Values

This post will guide you how to find and replace multiple values at once with VBA macro or using formula  in Excel. How do I make multiple find and replace in Excel. Suppose that you have a few cells containing few values and you want to find and replace two or three values; then you… read more »

Excel XLOOKUP Function

Excel XLOOKUP Function was added into Excel as a beta feature in August 2019 and is now accessible exclusively in Microsoft 365. (as of July 2021). However, if you fall into this category and often deal with big sets of data in Excel, understanding this method is worthwhile. In this lesson, we will demonstrate how… read more »

Extract or Filter Top n values

You might have been through this kind of situation where you need to filter out the top n values from the list having few values, and I am also pretty sure about it that you might have chosen to do it manually, which is also a great choice when you have only a few values… read more »

Extract matching values From Two Lists

Suppose that you are working with two lists containing few values, and you want to extract the matching values from those two lists into another separate list. You might prefer to manually extract the matching values from the two lists, which is ok for the few values, but it would be a big deal to… read more »

Extract all Partial Matches

Just assume that you have a range of data that contains a text string and you want to extract the few partial matches into another separate range of cells; then you might think that it’s not a big deal; because you would prefer to manually extract the few partial matches into another separate range of… read more »

Excel Filter Function (10 Examples)

This post will guide you how to extracts matched values using FILTER function in Microsoft Excel 365. And also will introduce that how to use FILTER function with same examples in Excel 365. Excel Filter Function The FILTER function “filters” a set of data according to the conditions specified. The outcome is an array of… read more »

Extract Unique Items From A List

This post will guide you how to extract unique itmes from a given list in Microsoft Excel. How to create a newly formula to get unique values from a range cells in Excel. The unique list of items is the first one found, which does not have a value equal to any other in the… read more »

Extract all the matches with helper Column

With Excel’s powerful functions IF, INDEX, and MATCH, we can find exactly what you’re looking for with a few clicks of the mouse. This step-by-step tutorial will show how easy it is to extract data using these tools and more! Generic Formula =IF($F5<=$F$2,INDEX($A$2:$D$12,MATCH($F5,$D$2:$D$12,0),1),””) Summary Excel’s INDEX and MATCH functions can be used to extract multiple… read more »

VLOOKUP From Another Sheet Not Working

In the previous post, you should know that how to fix or remove the #N/A error when using VLOOKUP formula to lookup value from another sheet. And this post will show you reasons why your VLOOKUP formula is not working in Excel 2003/2010/2013/2016 or Excel 365. You should be want to know some of the… read more »

Basic Usage of INDEX & MATCH – Case Sensitive Lookup

In Excel, INDEX function and MATCH function are often used together for retrieving data from a particular position. MATCH function is one of Excel lookup & reference functions that can perform approximate match or exact match by setting different match types. They are not case-sensitive functions. If we need to perform a case-sensitive lookup, we… read more »

Basic Rates Calculation by VLOOKUP Based on Weight Band

Microsoft Excel provides many functions that can execute logical test, search data, return current date and something else. They are very useful in daily work. And Excel VLOOKUP function is one of Excel most frequently used functions. It belongs to lookup & reference functions and it can perform approximate match or exact match by setting… read more »

Basic Grade Calculation by VLOOKUP Function – Approximate Match

In Excel, except combination INDEX+MATCH, we can also apply other functions to search data, for example VLOOKUP function. Like MATCH function, VLOOKUP function is one of Excel lookup & reference functions that can perform approximate match or exact match by setting lookup matching modes. Today, we will show you the way to calculate “Grade” by… read more »

Basic Discount Calculation with VLOOKUP Function

In Excel, except combination INDEX+MATCH, we can also apply other functions to search data, for example VLOOKUP function. Like MATCH function, VLOOKUP function is one of Excel lookup & reference functions that can perform approximate match or exact match by setting lookup matching modes. Today, we will show you the way to calculate discount by… read more »

Basic Usage of INDEX & MATCH – Exact Match

In Excel, INDEX function and MATCH function are often used together for returning value or cell reference or range reference from specified position. And MATCH function is one of Excel lookup & reference functions that can perform approximate match or exact match by setting different match type values. Today, we will show you how to… read more »

Sidebar