Category Archives: Excel

My Accounting Information Systems students are preparing to take the Microsoft Excel Expert certification exam in a few weeks. To help them review, I have created several videos that cover topics on the exam.

I am sharing the playlist of Excel videos with you, in hopes you find something useful. Some topics that are covered include: Vlookup, Match/Index, Pivot Tables and other functions.

I will keep adding to the playlist, so please check back.

Here is a link to YouTube: https://www.youtube.com/playlist?list=PLF_zs3YRWdbwUfIL0uLdVRno1ZyENCGVq

Otherwise, check out my other helpful Excel tips on my blog. https://iteachais.com/category/excel/

The VLOOKUP function is widely used to retrieve information from a range of data. Although that function works great, most of the time, it does have some limitations.

This article gives a great summary of when to use INDEX/MATCH vs the VLOOKUP function. https://excelzoom.com/3109-2/

Of course, soon in Office 365, this will all go away with the XLOOKUP function

OK — there is a lot of competitions for the best Excel functions. However, Match and Index have to be near the top of any accountant’s list.

There are times that a VLOOKUP, just won’t cut it. Remember, a VLOOKUP requires the lookup value is in the first column of the table array. Sometimes, that just isn’t possible. There are some cases in which you are rearrange the data, but not always. That is where a combination of MATCH and INDEX saves the day!

In the following example, you cannot use a VLOOKUP to lookup the Item Number, using the Vendor Part Number. The Venter Part Number is not in the first column.

The MATCH function searches for a specified item in a range of cells, and then returns the relative position of that item in the range. The INDEX function returns a value, specified by its relative position in a range, from within a range of cells. In other words, MATCH is used to find an item and INDEX is used to return the value of the item.

The MATCH function has the following syntax:

MATCH(lookup_value, lookup_array, [match_type])

The INDEX function has the following syntax:

INDEX(array, row_number, column_number) /html

Using the table above, the following example shows a comparison of the VLOOKUP and the MATCH/INDEX functions.

To see a short video for further explanation, go to: https://youtu.be/U_g6grN7Ndg

There are times text needs to be converted to lower case, proper case, or upper case for consistency sake. Excel provides functions to easily convert text in these cases.

However, Excel does not convert text to sentence case — the first letter only is capitalized. Using a combination of functions will easily convert text to sentence case.

The formula to convert the text in A1 is:

=UPPER(LEFT(A1,1))&LOWER(RIGHT(A1,LEN(A1)-1))

For examples, see the following:

Next week will continue with more useful text functions.

Vlookup is one of the functions I use the most. It can locate data within a table and return a value. One of the most confusing things about the function is whether or not to include the True or False at the end.

When using the Excel Function wizard, it argument in the function is called Range_lookup.

Simply stated, use a TRUE or 1 when you want an approximate match. Use a FALSE or 0 when you need an exact match.

Use TRUE when the data in the lookup table does not contain all of the possible data points. For example, in the figure below, if you want to look up the tax rate you would use an approximate match since all incomes are not in the table. If the net income were 100,001, it would return a rate of 39%.

It finds the closest value that is not larger than the lookup value.

In order to use an approximate match, your data must be sorted as well. Otherwise, use a FALSE or exact match.

For more examples, see this YouTube video: