This article will examine how to work with two of Excel’s most useful functions, VLOOKUP and SUMIF.

What IS VLOOKUP?

The Microsoft Excel VLOOKUP function is used to help locate data in a table that has a vertical organization so that information can be retrieved. Essentially, VLOOKUP tells Excel to look for a given piece of information (such as oranges) within a dataset (a table) and extract pertinent information about it (such as the price of oranges). 

VLOOKUP is often used in financial analysis as well as financial modeling so that the models include multiple scenarios and are therefore more dynamic. An example would be a financial model that has a debt schedule in which a company has three scenarios for possible interest rates: 2%, 3%, and 4%. VLOOKUP could be used to search for a low, medium, or high scenario, then output the matching interest rate directly into the financial model.

How to Use VLOOKUP

Here are the steps to execute if you’d like to perform VLOOKUP on an Excel worksheet:

Excel Bootcamp: Live & Hands-on, In NYC or Online, Learn From Experts, Free Retake, Small Class Sizes, 1-on-1 Bonus Training. Named a Top Bootcamp by Forbes, Fortune, & Time Out. Noble Desktop. Learn More.
  • Organize data. In order for VLOOKUP to work correctly, you must first make sure that the data is clean and organized. Because VLOOKUP functions in a left to right order, it’s essential that the information that you wish to look up is placed to the left of the corresponding data you’d like to extract.
  • Tell VLOOKUP what to search for. In order to tell this function what it should search for, begin by typing the formula “=VLOOKUP.” Then, choose the cell with the information you’d like to look up. 
  • Specify where the function should look. Next, you select the table in which the data is located. Instruct Excel to look in the leftmost column for the desired information.
  • Choose the column from which you’d like to output data. Excel must be told what column contains the data you want as an output for VLOOKUP. Excel requires a number corresponding to the column number in the table to do so. For example, if the data is located in the second column, enter “2” into the formula.
  • Specify if you’d like an exact or approximate match. By default, VLOOKUP searches for an approximate match. However, this can be changed. If you would like an exact match, type “FALSE” into the formula. If you are instead looking for an approximate match, select “TRUE” as the parameter. 

What is SUMIF?

SUMIF formulas occur when SUM and IF functions are combined. The SUM function is used for adding items. When combined with the IF function, SUMIF incorporates the added choice to SUM only the items that satisfy the given criteria. To put it another way, the SUMIF function allows you to add the values within a range that meet the criteria you establish. For example, you’d use the SUMIF function is if you want to add the values in a column of numbers that are greater than ten or if you want to add the number of pizza parlors in the country that are located in a specific zip code and whose profits are above a set dollar amount. 

SUMIF is a relatively new function. It was released in Excel 2007 and has grown in popularity over the past 15 years.

Using SUMIF

The SUMIF function contains three arguments:

  • Range is a requirement of this function. It covers the range of cells you’d like to have evaluated by the criteria. The cells contained in each range have to be names, arrays, numbers, or references containing numbers. Any text values or blank cells will be ignored. The range may include dates so long as they are in the standard Excel format. 
  • Criteria is another requirement of SUMIF. Criteria can take various forms, such as a cell reference, text, function that defines which cells are to be added, a number, or an expression. In addition, you can include wildcard characters such as question marks or asterisks as needed to match sequences of characters. Remember that criteria that include mathematical or logical symbols need to be bookended with double quotation marks. However, if the criteria are numeric, no double quotes are required.
  • Sum_range is an optional argument in SUMIFs. Sum_range pertains to the cells to add in the case when you wish to add additional cells to those noted in the range argument. If this argument is omitted, Excel will sum the cells that are listed in the range argument. Note that this argument should be the same shape and size as range else its functionality may be affected.

Using VLOOKUP & SUMIF Together

In addition to being used on their own, the VLOOKUP and SUMIF Excel functions can also be used together for additional analytic power. Here are a few other examples of when it may be useful to combine these two functions:

  • Use VLOOKUP within SUMIF when you want to sum values meeting a specified condition and the criteria are located in different tables.
  • Use SUMIF within VLOOKUP in instances where you have to search for a value that’s based on the summed total. 
  • Use SUMIF along with VLOOKUP and an additional function to help handle more complicated scenarios. For instance, if you’d like to sum across more than one sheet and locate an approximate match from a lookup table based on any totals you have, you could use SUMIF, along with VLOOKUP and SUMPRODUT. 

Whether you decide to use VLOOKUP and SUMIF alone or combine them for additional Excel analytic capabilities, these two Excel functions are among the most useful tools offered by this spreadsheet application.

Learn More About Excel with Hands-On Classes

Are you looking to learn more about SUMIFs and VLOOKUP? If so, Noble Desktop currently offers a variety of Excel courses in NYC and live online. Classes are available for those who are new to Excel, as well as learners who regularly work with this spreadsheet application and wish to brush up on their skills. In addition, there are also in-person and live online Excel courses available through Noble Desktop or one of its affiliate schools. A variety of course options are offered, ranging in duration from three hours to two days and costing between $229 and $1,099. 

Noble Desktop’s Excel Bootcamp provides a great option for those who want to master core Excel concepts, such as working with PivotTables and What-If Analysis. Students who enroll in this rigorous, 21-hour course can elect to study in person in Manhattan or learn in the live online environment. This small class comes with the option of a free retake and covers a variety of Excel concepts applicable to the business world.