From VLOOKUP to SUMIF: 10 Microsoft Excel formulas everybody should know

by Amber Rolfe

Looking to excel in Excel? You should read this…

Microsoft Excel is an essential tool in many job roles, from finance and marketing, to data analysis and administration. While Excel offers a vast array of functionalities, mastering just a few of its key formulas can significantly boost your efficiency and productivity.

Whether you’re a beginner or looking to enhance your spreadsheet skills, here are 10 Microsoft Excel formulas everyone should know:

1. VLOOKUP

VLOOKUP (Vertical Lookup) is one of the most widely used Excel functions. It searches for a value in the first column of a range and returns a value in the same row from another column.

Function: VLOOKUP(lookup_value, table_array, col_index_num, [range_lookup])

How to use VLOOKUP:

Select the cell you want the result to appear in
Type =VLOOKUP(
Enter the value you want to search for (e.g., A2)
Enter the range of cells to search within (e.g., B2:D10)
Enter the column number from which to return the value (e.g., 3 for the third column)
Specify TRUE for an approximate match or FALSE for an exact match (e.g., FALSE)
Press Enter

2. SUMIF

SUMIF is used to sum values in a range that meet a specific condition or criteria.

Function: SUMIF(range, criteria, [sum_range])

How to use SUMIF:

Imagine you have a sales list where column A has product names and column B has sales figures
To sum the sales of a particular product, use: =SUMIF(A2:A10, “ProductName”, B2:B10)
This adds up only the sales figures for “ProductName”

3. IF

The IF function performs a logical test and returns one value for a TRUE result and another for a FALSE result.

Function: IF(logical_test, value_if_true, value_if_false)

How to Use IF:

To assign a “Pass” or “Fail” based on scores in column A, use: =IF(A2>=50, “Pass”, “Fail”)
This checks if the score in A2 is 50 or above and returns “Pass”; otherwise, it returns “Fail”

4. INDEX & MATCH

INDEX and MATCH are powerful alternatives to VLOOKUP, offering more flexibility. INDEX returns a value from a specific row and column within a range, while MATCH finds the position of a value within a range.

Function: INDEX(array, row_num, [column_num]) + MATCH(lookup_value, lookup_array, [match_type])

How to use INDEX & MATCH:

First, find the position of a value with MATCH: =MATCH(“ProductName”, A2:A10, 0)
Then, use INDEX to find the value in the corresponding row and a different column: =INDEX(B2:B10, MATCH(“ProductName”, A2:A10, 0))
This combination returns the price of “ProductName” from column B

5. CONCATENATE (or CONCAT)

CONCATENATE (or the newer CONCAT) joins two or more text strings into one.

Function: CONCATENATE(text1, [text2], …) or CONCAT(text1, [text2], …)

How to use CONCATENATE (or CONCAT):

To combine first names in column A and last names in column B into a full name, use: =CONCATENATE(A2, ” “, B2)
This joins the first name and last name with a space in between

6. TEXT

The TEXT function converts a number to text in a specified format.

Function: TEXT(value, format_text)

How to use TEXT:

To format a date in “DD/MM/YYYY” format, use: =TEXT(A2, “DD/MM/YYYY”)
This changes the date in A2 to the desired format

7. SUMPRODUCT

SUMPRODUCT multiplies corresponding elements in arrays and then sums those products.

Function: SUMPRODUCT(array1, [array2], …)

How to use SUMPRODUCT:

If column A contains units sold and column B contains the price per unit, calculate total revenue with: =SUMPRODUCT(A2:A10, B2:B10)
This multiplies each unit sold by its price and then sums the total

8. COUNTIF

COUNTIF counts the number of cells in a range that meet a specific condition.

Function: COUNTIF(range, criteria)

How to use COUNTIF:

To count how many times a specific product appears in column A, use: =COUNTIF(A2:A10, “ProductName”).
This returns the number of times “ProductName” is listed.

9. LEFT, RIGHT, MID

These functions extract a specified number of characters from a text string, starting from the left, right, or a specific position (MID).

Functions: LEFT(text, [num_chars]), RIGHT(text, [num_chars]), MID(text, start_num, num_chars)

How to use LEFT, RIGHT, MID:

To extract the first three characters of a product code in A2, use: =LEFT(A2, 3)
To get the last four digits of a phone number in B2, use: =RIGHT(B2, 4)
To extract characters from the middle of a string, starting at the 2nd character and taking the next 4 characters, use: =MID(A2, 2, 4)