3 Useful Excel Functions Every Accounting Professional Should Know Before Getting Started

As you strive for a successful accounting career, you will come across a number of challenges.

But don’t worry. We are here to help you with some of them. Here are three useful Excel functions that’ll help you on your journey to becoming a successful accountant.

So, buckle up and read on.

 VLOOKUP

In an accounting role you may be asked to analyse data from one system to another.

VLOOKUP is the perfect tool.

For a better understanding, let us look at this example.

In an Excel sheet, you have the following employee details.

Now, your boss sends you the following list and asks you to add the details in the blank columns.

The salaries and designations of Matt and Sophie. Let us do that with the help of VLOOKUP.

Use the following formula in their respective cells.

• F3: =VLOOKUP(E3,A3:C6,2,0)
• F4: =VLOOKUP(E4,A3:C6,2,0)
• G3: =VLOOKUP(E3,A3:C6,3,0)
• G4: =VLOOKUP(E4,A3:C6,3,0)

Results:

Matt Manager with Salary AUD 1,700

Sophie Asst. Manager with Salary AUD 3,400

Most popular question asked in class ” How do I create a VLOOKUP Function”

Use the following formula for VLOOKUP function:

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

Checkout this awesome YouTube channel for more info on VLOOKUP and how you can make the most out of it.

INDEX And MATCH

Best scenario to use the MATCH function is with VLOOKUP.

Replace the Column index number with the MATCH function.

Extremely useful when the column index number is something like 114.

INDEX Function is great when the VLOOKUP won’t do because the Lookup Value in the table is located in the middle and you need to extract data from the left of it.

For an understanding of how it works, let us look at the following example.

So, we have a table of students as follows.

Now, for instance, let us say that we need to lookup for the name of a student at roll number 104.

So, what we will do is take the INDEX and MATCH Formula to place the right values and paste it in G2.

The formula that we’ll have to use is: =INDEX(A2:A10,MATCH(F2,B2:B10,0))

As a result of this action, the following results will come up.

Simple, right?

INDEX Function structure i

= INDEX(array, row number, column number).

column number is optional.

MATCH Function structure is:

=MATCH(LookupValue, Table, Match Type)

=INDEX(range from where you want to retrieve data,Match(lookup_value,lookup range,0))

The formula we will have to use is:.

=INDEX(range from where you want to retrieve data,Match(lookup_value,lookup range,0))

IF, AND, OR

IF with AND, and IF with OR are two essential functions that allow you to select entities based on two conditions.

For example, if you are going to buy apples, you would like them to be red AND juicy. And if the apples on display appear to be rotten (not red OR juicy), you may give up on your purchase.

That’s simply how these functions work in Excel.

For a better understanding, let us consider the table below.

In the table above, we have a list of apples based on their colour and texture.

To point out the apples that are OK (both red and juicy), we use the following equation:

=IF(AND(B3=”Red”, C3=”Juicy”),”OK”,”Not OK”)

To show the results as OK for apples that are both “red and juicy”, and Not OK for other apples.

The equation is applied to the entire column, and the results show up.

This works the same way for IF with OR as well. The syntax for both functions are as follows.

Syntax for IF with AND:

=IF(AND(condition1, condition2,…),value if true, value if false)

Syntax for IF with OR:

=IF(OR(condition1, condition2,…),value if true, value if false)

Conclusion

Here you read about 3 important Functions every accountant would find most useful in their day to day manipulation of data. VLOOKUP to compare and extract. INDEX AND MATCH when VLOOKUP won’t do and the most flexible Function IF accompanied with the OR and AND Functions.

Now if you are feeling the need for learning more, an Excel training course in Sydney can help you. Apart from that, this was it for now. Hopefully you liked it.

Have you read?

# Largest crude steel-producing countries in the world, 2020.
# Most expensive hotels in the world for high net worth individuals, 2020
# Most traffic-congested cities in the world, 2020
# Cities around the world with the most and least stressed-out employees, 2020
# Countries most and least prepared to deal with an epidemic or pandemic like the Coronavirus