10 Excel Formulas Every Consultant Should Know

Excel is one of those things you can spend a lifetime trying to master everything it can possibly do. Luckily you don’t need to know everything Excel can do to get a lot out of it.

I work with a partner now who is known to make the claim that Excel is the world’s largest Enterprise Resource Planning (ERP) system.

Also, I can tell you when I started in business school at Kellogg, we used two fancy macro workbooks to run sophisticated statistical analysis until they shifted to having everyone use STATA.

STATA was/is infinitely better than Excel, but you’d be amazed at what you can do in Excel, with a little bit of coding know-how.

This post is about the Excel formulas, though – the little statements you type into a cell to make magical things happen.

Read on to dive into the ten Excel formulas you need to know and love to…excel as a consultant.

1. Vlookup

Vlookup is a formula that allows you to link data from two difference sources based on a unique key. They syntax is =vlookup(value, lookup table, column number in the lookup table, match type). 

A couple of notes, the column numbers should be counted started with the column containing the lookup key. In other words, your lookup table should be formatted such that the column with values that might match are the first column of that table.

If you want the lookup to pull the nearest match – aka fuzzy match – put true in the last term and don’t forget to make sure your lookup table is sorted in ascending order. I don’t think I’ve ever used a fuzzy match in this way, but the functionality is there.

Generally speaking, the lookup value needs to be a unique or you may distort the results.

2. Match

Match is a formula that returns either the row number or column number of a value you’re looking for in a table. The syntax is =match(value, lookup row OR column, match type).

The thing to note here is that match type has a few variations. If you put “1” as the match type, your lookup array row/column needs to be ascending order. This will tell Excel to return the position of the largest value that is less than or equal to your lookup value.

Using “-1” tells Excel to return the position of the smallest value that is greater than or equal to your lookup value. This means your lookup row/column has to be sorted in descending order.

If you want an exact match, then you just put “0” in for the match type.

I rarely use the match formula independently to figure out a value’s position in a column or row because you can generally get that answer by highlighting the row/column and doing a search for the lookup value.

You might be asking why match is on this list then if I never use it, but the operative word in my statement above was “independently”. When you get to number 4, you’ll understand what I mean.

3. Index

Index is vlookup on steroids. Perhaps the best part of the index formula is the lookup value doesn’t need to be in the first column of the lookup table. If you’ve ever found yourself wishing you could do a negative vlookup, this is for you.

There are two syntaxes for using the index but the one I use is =index(lookup table, row number, column number). In most instances, you can probably just type in the column number like the vlookup formula, but if your table has more than one row, index needs you to provide the row of the lookup value so it can return the result from the intersection. You can either type in the lookup value row manually for each occurrence of the formula or you can use formula #4 instead.

4. Index + match

Ok, so technically this is two formulas combined,  but it is a slick way of doing lookups without needing to re-sort any of your data. The syntax is =index(lookup table, match(lookup value, lookup value column in lookup table, match type), column number).

You can also you match for the column number argument if you’re feeling too lazy to count how many columns it is to get from column A to column AC.

5. Text

When it comes to lookups, formatting matters and one thing that’s happened too many times for me to count is I get a data set where the lookup key doesn’t match on the lookup table because one of the files has dropped the leading zero. For the lookups to work the lookup value needs to be formatted the same in each table.

Let’s say we have two files. File #1 has my ID listed as 01234 and File #2 has my ID listed as 1234 in cell A2. I would fix file 2 by typing into a new cell =text(A2,”00000″). The first argument is the value that needs to be transformed, and the second value defines the format. In this case, the format is 5 digits.

6. Sumifs

The sumifs formula allows you to return the sum of a specified column based on certain conditions. The syntax of the formula is = sumifs(sum_range, criteria range1, criteria1, criteria range2, criteria2,…). Using sumifs gives you some functionality you can’t have in a standard vlookup, because it allows for repeated lookup values in the lookup table.

Imagine having a table with a list of customer IDs and wanting to pull in their total sales for the year. The lookup table, however, has a record for each month. Using index or vlookup would only return the first record with the matching customer ID. Sumifs will give you the total, even if your data set isn’t sorted by customer ID.

Additionally, you can have multiple criteria, so this can be quite useful when summarizing data in a table so you don’t have to key in every cell manually.

7. Countifs

Similar to the sumifs Excel formula noted above, countifs allows you to quickly summarize data using basically the same syntax. The beauty of countifs is it also gives you a good way to deal with qualitative data. For example, if you have a massive data table and you want to know the number of female customers who live in San Jose, countifs has you covered.

8. Iferror

Once you have started using formulas, you inevitably come across a scenario where the formula is working perfectly but the result is hideous – #N/A. That means the value you’re looking for doesn’t show up in the lookup table.

To keep your table clean use the syntax =iferror(formula,””). The second argument tells Excel what to do should you formula produce and error. In this case, I put empty quotation marks so Excel will return a blank, but you could put something else there like “additional research required”.

9. &

If you find yourself wanting to combine the values int two cells, you can easily do that by using =Cell1&Cell2. If you want to put a space or other character between the two cell values, try =cell1&” “&cell2 or =cell1&”_”&cell2. This formula comes in handy when you want to make sure you have a unique value to perform a lookup. For example, first name might not be a unique key, but first and last is. If you’re combining more than two values (that are all adjacent to each other), I would suggest using the next formula instead.

10. Concatenate

Sometimes creating a unique value for lookups requires mashing together several fields. Concatenate does this quickly by doing =concatenate(highlighted array of cells). Again, you can add in a character or space to separate the fields, but this is mostly cosmetic. The upside of this over the & formula is that if you’re combining 8 cells to come up with your unique value, you don’t have to type in “&” so many times.

Want to learn more about Excel formulas?

If you’d like to see a video of any of these formulas in action or have another Excel challenge you’re trying to solve, let me know in the comments.