When it comes to business, Microsoft Excel can be one of your biggest assets--if you know how to use it. Most people know how to navigate through the basic accounting functions, but Excel has so much more to offer that a manager or business owner could be taking advantage of.

Below are nine of the most useful Microsoft Excel formulas that you won’t want to live without once you discover their hidden potential.

Formula # 1: =VLOOKUP(lookup_value, lookup_table_range, column, exact)

=VLOOKUP( ) provides a way to look up vertical values in the first column of the data table selected and return the value for reference. One way to use this function would be to lookup a salesperson’s name in a monthly sales report to see how much that person sold. You could also calculate a sales commission for each salesperson using =VLOOKUP.

Explanation of arguments:

Lookup_value = The value you are looking up in the first column of the table.

Column = The number of the column of the information you are looking for. The value of this column is what will be returned.

How the formula works:

Let’s say you have the following data in a spreadsheet:

=VLOOKUP(A6, A6:C10, 3, 0)

The value you are looking up in the first column of the table is 2345, which is in cell A6. The range of the data in the table is from cell A6 to cell C10, and the column for price is the third column over, so a 3 is entered for “column.” Because you are looking for an exact match, a zero is entered for “exact.”

Formula #2: =INDEX(MATCH(lookup_value, lookup_array, match_type))

The =INDEX(MATCH( )) formula is a combination of the =INDEX( ) and =MATCH( ) formulas and is similar to the =VLOOKUP formula in that it is also used for a vertical lookup. The difference is that =INDEX(MATCH( )) can also do horizontal lookups, as well as look up values from right to left, whereas =VLOOKUP is only capable of looking up values from left to right. This being said, =VLOOKUP is the easier of the formulas to learn because there is only one formula to learn versus two combined formulas.

Explanation of Arguments:

Before using the =INDEX(MATCH( )) formula, we need to break it down into each individual formula to better understand it.

=INDEX(lookup_array, row_number)

The =INDEX function returns a value based on an array and a row number. In the table below, the “lookup_array” is C2 through C7, and because you want the cell from the third row in that column the “row_number” is 3.

The =MATCH( ) function returns a number based on the lookup value and array or column. You can see in the figure below that the “lookup_value” is A16 because that cell contains the ID number you are matching with a row, and the “lookup_array” is A2 through A7 because that is the column that contains the ID numbers. Similar to =VLOOKUP, if you are looking for an exact match for “match_type” you should use a zero or leave it blank.

Now to combine the two formulas. When using the =INDEX(MATCH( )) formula, the array is the same as when just using =INDEX( ), but the row number should be replaced with =MATCH( ) instead of a particular row number. By doing this, the formula will give you the same result as using =VLOOKUP, but with a little more flexibility.

Formula #3: =RANDBETWEEN(bottom, top )

The =RANDBETWEEN( ) formula provides an easier and faster way to randomly pick something from a list. This is especially good for raffle drawings and assigning random numbers or dates.
Explanation of Arguments:

Bottom = The smallest value the function can return

Top = The largest value the function can return

How the Formula Works:
To use the function insert the start number and end number of the sequence of numbers you want to draw from. For example, if you have a list of dates filling cells A1 to A200, your formula would be =RANDBETWEEN(1,200) to choose a random number for that sequence. Using the INDEX function explained earlier you can match the random number to the corresponding date.

Formula #4: =LEFT(text, number_characters)

This formula is useful for making usernames or pulling parts of words or numbers out of one cell and into another. It works by taking a certain number of letters from the left side of the text entered in a cell.

For example, if you have the text “Left” in cell A2, and you want to pull the first two letters from it (ie, “Le”), your formula would be =LEFT(A2, 2).

Formula #5: =RIGHT(text, number_characters)

This one is just like the =LEFT function, but it pulls the characters from the right. So if we want the last three letters in cell A1, which contains the text “Right,” our formula would be =RIGHT(A1, 3) to give us “ght.”

Formula #6: =MID(text, start_number, number_characters)

The =MID formula is just like the =LEFT and =RIGHT formulas, but you have to specify where in the text you want the formula to start. So if you want the second and third letters from the left in the cell A3 (which contains the word “Middle”), your formula would be =MID(A3, 2, 2) to give you “id.”

Formula #7: =CONCATENATE(text1, text2,...)

The =CONCATENATE function is a simple tool to link multiple cells together. All you have to do is select each cell you want combined. So if cell A1 contains “John” and cell B1 contains “Doe,” your formula will be =CONCATENATE(A1, B1), and your end result will be “John Doe.” This formula can be especially useful with the =LEFT, =RIGHT, and =MID functions to create usernames and strings of letters and numbers.

Formula #8: =IF(logical_statement, TRUE, FALSE)

The =IF statement is good to use if you need to display different results for something depending on a certain variable. Let’s say you have a list of salespeople with data on how much they have sold. You can use the =IF statement to display whether or not they have met their quota.

Logical_statement = The statement you are testing each cell for.

FALSE = What to display if the statement is false

Let’s say you have a data chart regarding monthly sales. In column A you have the salesperson’s name, and in column B you have their sales for the month in dollars. If the quota for the month is $5,000, your formula would be =IF(B1>5000, “Met quota”, “Did not meet quota”).

If you are interested in learning more about Microsoft Excel, click the link below!