Top 5 Excel Formulas Explained

Photo by AbsolutVision on Unsplash

The Vlookup

Excel Syntax

=VLOOKUP(lookup_value, table_array, col_index_num, [range_lookup])

Explanation of Syntax

lookup_value = exact value in working table and lookup table

table_array = a table that includes the lookup_value and your desired output. The lookup value must be in the first column of the table

col_index_num = the column number of your desired output in the table that you defined in the table_array

[range_lookup] = the type of match you want to use — “exact”(FALSE) or “close” (TRUE)

Explanation

1. The lookup_value is the cell in the working table that EXACTLY matches cells in a column of the lookup table.
2. The table_array is simply the lookup table, with the first column of the table_array being the lookup value column (Ex: if the lookup_value was blue (in the eye color column), the table_array must have the eye color column as the first column).
3. The col_index_num is the number of the column that you want to return from the table_array. The column numbers go left to right and start with your lookup value column, in the working table, as 1. If the data that you wanted to return was one column to the right of your lookup value column in the working table, you would enter 2. If the data that you wanted to return was three columns to the right of the lookup_value column, you would enter 4.
4. The [range_lookup] will almost always be FALSE. Excel is asking if you would like an exact match - - or if you would like a "close" match - TRUE.

Example

Let’s look at an example of how to use the vlookup:

=VLOOKUP(A2,$A$2:$B$5,2,FALSE)

We have two tables — the lookup table and working table. The working table has Region ID, but does not have Region. Let’suse a vlookup to bring Region in from the lookup table. We must first determine the lookup value. With what we learned in the Explanation of Syntax and Explanation sections above, we know that the lookup_value must be a value that is EXACTLY the same in both the working table and lookup table. In this case, the Region ID fits that criteria so we will reference it by entering cell A2. Next, we must state the table_array. The table_array is the table that contains our desired output and has our lookup_value as the first column. In this case, the table_array starts at the beginning of column A, since column A has the common lookup value, and ends in the last cell of column B ($A$2:$B$5). Next, we will tell the function what column number our desired output resides in. In this example, since there are only 2 columns — the lookup_value column (#1) and the desired output column (#2) — we will use 2. If our table spanned 3 columns, we would still use 2 to return the Region as long as Region was the second column in our defined table_array. If a “total sales dollars” column was inserted between Region ID and Region, the “total sales dollars” column would then be in column 2 so in order to return Region, we would have to change our col_index_num to 3. Lastly, we use FALSE to tell the function that we want an exact result instead of a “close” match.

If Statement

Excel Syntax

=IF(logical_test, [value_if_true], [value_if_false])

Explanation of Syntax

logical_test = statement that you want to test and return different results from

[value_if_true] = anything you want the cell to return if your logical_test is true (can be a value (number), a string (letter, word), or a calculation (formula))

[value_if_false] = anything you want the cell to return if your logical_test is false (can be a value (number), a string (letter, word), or a calculation (formula))

Explanation

1. The if statement is used when you need different outcomes based on the results of a test or formula. The results of the test are either TRUE or FALSE and you can program the formula to output any values you’d like for either a TRUE result or a FALSE result. The logical_test is the test, or formula, that you are looking to get different ouputs from — one result of the test is TRUE and one result if the test is false
2. The [value_if_true] is simply the result that you want the formula to output if the logical_test that you entered is true.
3. The [value_if_false] is simply the result that you want the formula to output if the logical_test that you entered is false.

Example

Let’s look at an example of how to use the if statement:

=IF(A2<40,”Yes”,”No”)

We are working for an airline that has a specific rule that suitcases must be 40lbs or less or the customer will not be able to board the plane. We have a list of suitcase weights and we need to test if the suitcase weight is less than 40lbs — if the suitcase is less than 40lbs, the customer can board the plane; if the suitcase is equal to 40lbs or over 40lbs, the customer cannot board the plane. The formula starts with the lookup_value — A2 — and the condition “<40”. The formula now asks us to enter the desired cell output if the formula is true. In this case, we would like to return “Yes” if the test is true. The formula now asks us to enter the desired cell output if the formula is false. In this case, we would like to return “No” if the test is false.

Index Match

Excel Syntax

=INDEX(array, row_num, [column_num])

=MATCH(lookup_value, lookup_array, [match_type])

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

Explanation of Syntax

array = the group of cells (rows and/or columns) that contains your desired data

row_num = the row number where your desired data is located

[column_num] = if the array that you gave the formula contains rows and columns, this is the column number where the desired data is located.

lookup_value = exact value in working table and lookup table

lookup_array = column in lookup table that contains your lookup value. The starting and ending row numbers must match the starting and ending row numbers of the array that was defined in the INDEX formula

[match_type] = There are three options: 1 (Less than), 0 (Exact match), -1 (Greater than). 1 and -1 are only used when you want the match formula to return the next closest values (values that are greater than or less than the lookup_value) — 0 is used in all other cases (with values and text).

Explanation

1. Start with the index formula by defining the array, or the cells that contain your desired output
2. Next, embed the match formula within the index formula. We are embedding the match formula to allow the row_num, of the index formula, to dynamically update based on the row number of the lookup_value in the lookup table.
3. The first part of the match formula is the lookup_value. This is the value that you have in the working table and in the lookup table. The match formula is going to look for the lookup_value in the lookup_array, that we will define next, and return its row number. The returned row number will be used to tell the index formula what row of the array to return.
4. The next part of the match formula is the lookup_array. The lookup array is the group of cells, in the lookup table, that exactly matches the starting and ending row numbers of the index formula’sarray and contains the lookup_value from the working table.
5. The last part of the match formula is the [match_type]. Like we learned in the “Explanation of Syntax” section, the match type can be 1, 0, and -1. For a majority of the index match formulas, we are looking for an exact match, so we will use 0. There will be more examples of when to use -1 and 1 in a later section.

Example

Let’s look at an example of how to use the index match formula:

=INDEX($B$2:$B$5,MATCH(A2,$A$2:$A$5,0)

Like in the vlookup example above, our working table has the region ID, but does not have region. To complete the table, we would like to pull in the region using the region ID as the common lookup_value in both tables. We start the index match formula by defining the array for the index formula — $B$2:$B$5. This array represents the data that we want to return from the formula. The next part of the formula is embedding the match function. As we learned previously, embedding the match function allows us to dynamically reference the row that we want to return in the index array by returning the row number of the lookup_value in the lookup_array. The lookup_value will be A2. Next, we need to define the lookup_array, which is the set of cells in the lookup table that matches our lookup_value. Again, the lookup_value will match to a row number in the lookup_array and that same row number will be fed into the index array formula. The row number fed into the index array formula will return the data that is found in that row number of the index array. The lookup_array will be $A$2:$A$5. Lastly, we will define a [match_type] for the match formula. We will use 0 as we want an exact match. There will examples on the 1 (less than) and -1 (greater than) match in a later post.

Sumif

Excel Syntax

=SUMIF(range, criteria, [sum_range])

Explanation of Syntax

range = the group of cells in the lookup table that contain the criteria that you are trying to sum

criteria = the cell that you are trying to sum in the working table

[sum_range] = the values in the lookup table that you are trying to sum. Each lookup table value needs to have a corresponding sum criteria cell, in the same row, that you defined in the range above. The defined range has cells that exactly match the criteria that you defined above as well.

Explanation

1. Start the sumif formula by defining the range of cells that contain your sum criteria in the lookup table
2. Next, define the criteria cell in the working table. This criteria cell will match some/most/all of the cells in the range that you defined in the previous step
3. Lastly, the [sum_range] will be values (numbers), in the lookup table, that you want to sum based on the criteria in the working table and the criteria located in the range of the lookup table

Example

Let’s look at an example of how to use the sumif formula:

=SUMIF($A$2:$A$15,A2,$C$2:$C$15)

In this example, we have a list of sales reps and their quarterly sales. We’d like to understand what the annual sales is for each sales rep. The sumif formula is a perfect candidate for this job. We start the formula by defining the range of cells, in the lookup table, that contains the criteria we’d like to summarize, not the values we’d like to summarize. In this case, the range of criteria is the sales reps — $A$2:$A$15. Next, we give the formula the criteria, or cell, from the working table that matches the range, that we just defined, from the lookup table — A2. Lastly, we give the formula the [sum_range] which are the values (numbers) in the lookup table that we want to total, based on the criteria from the working table and corresponding range of criteria from the lookup table — $C$2:$C$15.

Countif

Excel Syntax

=COUNTIF(range, criteria)

Explanation of Syntax

range = the group of cells in the lookup table that contain the criteria that you are trying to count

criteria = the cell that you are trying to count in the working table

Explanation

1. Start the countif formula by defining the range of cells that contain your count criteria in the lookup table
2. Next, define the criteria cell in the working table. This criteria cell will match some/most/all of the cells in the range that you defined in the previous step

Example

Let’s look at an example of how to use the countif formula:

=COUNTIF($A$2:$A$15,A2)

In this example, we have a list of sales reps and the customers that they service. We would like to understand how many customers our sales reps — Carl and Larry — each have. We start the formula by defining the range of cells, in the lookup table, that contains the criteria we’d like to count — sales reps — $A$2:$A$15. Next, we define the criteria in the working table that we’d like a count from the lookup table — sales rep name — A2.

—

Thanks for reading! Check out my website for more Business/Excel/SQL/Power BI content — https://www.bigdogsexcelerator.com/

Originally published at https://www.bigdogsexcelerator.com.