Excel Class Free VBA Tutorial – Variables

Few things in life are constant. Heraclitus said: The only thing constant is change. Change is constant and variables are used to capture change. In the context of Excel, the names of workbooks change, the number of workbooks you have open is variable, the number of sheets, pivot tables and charts is usually variable, the name of sheets is variable, the values in cells are variable, and on, and on.

Continue reading

Excel Tutorial – Vlookup Multiple Columns To The Right With An Array Formula

In the process of teaching Excel classes, many students ask:

How can I write a single vlookup function that returns multiple values to the right, instead of a single value, which causes me to write many vlookup functions in many columns.

This tutorial does not teach vlookup, that feature is taught in the Introduction and Advanced classes. Here we focus on vlookup and array formulas. The purpose of this tutorial is to supplement the class notes.

When performing calculations in Excel, typically a single cell contains a formula with functions that return a single value. An array formula is different in that it will typically include many cells (rows or columns) and return many values. Where I would teach students to press the enter key to commit an edit, to create an array formula we are required to hold Ctrl+Shift+Enter.
To follow along with this example, please download a workbook with this link:

Vlookup Array Workbook

Though not required, I suggest when you open this workbook, click the Enable macros command which will create two windows and tile them side by side.
In this example, the idea is to write a single vlookup function on the VlookupOrders sheet. The single vlookup will find the order number on the Data sheet and return three values, the Company Name, the Country and the Sale.

Example Of Data In Vlookup Array Workbook

vlookup array formula purpose

1) On the VlookupOrders worksheet tab, select/highlight cells C2:E2
2) Create the following formula:
=VLOOKUP(B2,Data!B:F,{3,2,5},0)

vlookup array formula purpose

Note: For the third argument, the col_index_num, we are allowed to use as many values as needed. In this example I am trying to illustrate that as much as I can return three columns to three cells — in any sequence — we can choose any number of columns as needed. However, to vlookup four columns, in step 1, select/highlight four cells. Also note that we cannot simply add parentheses around the values of the col_index_num, use curly braces {} (squiggly brackets – typically found to the right of the letter P on your keyboard).

3) To convert this to an array formula, hold the Ctrl+Shift keys and press the Enter key. The array formula adds {} (curly braces) around the vlookup formula.

A final word of warning: Array formulas have drawbacks. One drawback is that array formulas recalculate a bit slowly over thousands of rows. A bigger drawback is that we have lost the ability to insert a new column within the arrayed formula range. To verify the loss of the ability to insert a column inside the array, on the VlookupOrders sheet, right click column D and left click the Insert command.

Data Tables And Interactive Charts

The Excel Class blog provides an opportunity to supplement the material we teach in our class. This tutorial assumes our readers are familiar with named ranges. (See: Advanced Excel Class)

Excel’s data table feature is a what-if analysis tool that allows users to create tables (rows/columns) of results based on a one or two variable formula. Data tables are useful for simple calculations like future value tables or mortgage payment tables to examine different scenarios based on varying input assumptions. Our previous post examined array formulas with vlookup (Vlookup Array Formula Tutorial) and data tables are an extension of the discussion of array formulas.

Download the finished workbook here

In the following example we use a data table to show rates of return for an investment and then use the data table as the basis of an interactive chart using an offset function in an array formula.

The formula for Future Value is:

FV = PV * (1 + i) ^ t

Where:

FV means future value

PV means present value

i is the interest rate

t is time

To Create A Data Table

1. Set up variable inputs, optionally (recommended) name the cells to use in the formula;
Data Table Step 1

2. To the right of the PV label, in cell B1, type a number like 1. In this example 1 means $1, our present value.

3. In a separate cell, create the formula that references the named ranges. In this example the formula appears in cell C4 which will become the upper left corner of the data table;
Data Table Step 2

4. To the right and below the formula, add variable inputs;
Data Table Step 3

5. Select the range to use for the data table, including the formula in the upper left corner;
In this example select cells C4:K24;

6. On the ribbon, select the Data tab > What If Analysis > Data Table command;
Select a row input cell (refer to a cell with a name); In this example, cell B2 is the row input (interest).
Select a column input cell (refer to a cell with a name); In this example, cell B3 is the column input (time).
Click the OK button to generate the data table.
Data Table Step 5

Note: The formulas produced by the data table in cells D5:K24 are array formulas. Be aware that array formulas remove the ability to insert or delete a row or a column inside the data table.

To Create An Interactive Chart Based On The Data Table

1. In cell C26, type a 0 (zero);

2. Select (highlight) cells D26:K26;

3. Type an = (equals) sign;

Enter the following formula:
=OFFSET(D5:K5,C26,0)

To create an array formula, hold the Ctrl+Shift keys and press the Enter key.

The offset function offsets the range D5:K5 by the value in cell C26 (zero rows)
Interactive Chart Array Formula

4. To add an interactive spinner control, show the Developer tab of the ribbon. To show the Developer tab, right click any tab of the ribbon, choose the Customize Ribbon… command. In the Customize Excel Options dialog, check the Developer tab.

5. On the Developer tab of the ribbon, select the Insert > Form Controls > Spinner command.
Developer Tab Insert Spinner Command

6. Click and drag a small spinner control in a cell to the left of cell C26.

7. Right click the spinner control, left click the Format Control command.

In the Format Control dialog:

Set the minimum value to 0

Set the maximum value to 20 (the number of years in column C)

Set the link cell to cell C26
Developer Tab Insert Spinner Command

8. The final step is to create the interactive chart.

Select cells D26:K26

On the ribbon, select the Insert tab > 2D Line Chart command
Insert 2D Line Chart

9. To see the chart become interactive, click the up arrow on the spinner control to increase the value in cell C26.

Note that as the value increases the offset function plots different rows of the data table in the chart.

Final note: I am obligated to give credit to Tushar Mehta for his excellent charting tutorials. Learn more about Tushar’s chart tutorials here: http://tushar-mehta.com/excel/charts/ In fact, I always encourage Excel students to study Tushar’s tutorials on: 3D Surface Charts, Normal Distribution (Bell Curves), Waterfall (Cash flows), and Values That Differ By Magnitude.

SumProduct Function Applications

SumProduct Function Applications

When teaching Excel classes students occasionally ask about the SumProduct function. There are many aspects of SumProduct that mirror the SumIfs functions capabilities but SumProduct can produce different answers when combined with the unary operator. This tutorial is not meant to be a thorough discussion of SumProduct but is meant to provide an addendum to class notes.

Click Here To Download A Sample Workbook

I have provided a picture of the worksheet for those who cannot open Excel workbooks:
Sumproduct

SumProduct – Example 1

The SumProduct function multiplies one array by another array. An array is typically a range of cells in a single column. In the first example imagine that our goal is to calculate the total gross pay for all employees in all divisions and departments. The ‘old fashioned’ Excel approach is to write a formula to calculate hours * rate for each employee (cells G2:G21) then Sum those values. In this Level 1 example, we would need to generate 20 formulas in 20 cells to find the answer, where a SumProduct function calculates the result with less effort.

With SumProduct
=SUMPRODUCT(E:E,F:F)

Without SumProduct
=(E2*F2)+(E3*F3)+(E4*F4)+(E5*F5)+(E6*F6)+(E7*F7)+(E8*F8)+(E9*F9)+(E10*F10)+(E11*F11)+(E12*F12)+(E13*F13)+(E14*F14)+(E15*F15)+(E16*F16)+(E17*F17)+(E18*F18)+(E19*F19)+(E20*F20)+(E21*F21)

SumProduct – Example 2

About fifteen years ago Excel did not offer a SumIfs function and SumProduct was an alternative to using an array formula. Like the SumIfs function, SumProduct can work conditionally using criteria. Where SumIfs only allows AND criteria, SumProduct also supports OR criteria (but this generally double counts and generates incorrect results). Several studies have shown that SumIfs functions calculate significantly faster than SumProduct functions.

In example 2 imagine that we want to only sum the gross pay if the division equals Maine and the department equals Sales. In this example the asterisk (*) between Array1 and Array2 operates as an AND operator. The formula reads, If column C contains “Maine” AND Column D contains “Sales”, then Sum the value in column E times column F. If we use a plus (+) instead of an asterisk, this represents an OR criteria but double counts the products of Maine and Sales.

With SumProduct
=SUMPRODUCT((C:C=”Maine”)*(D:D=”Sales”),E:E,F:F)

With SumIfs
=SUMIFS(G:G,C:C,”Maine”,D:D,”Sales”)

Array Formula Sum If
{=SUM(IF((C:C=”Maine”)*(D:D=”Sales”),E:E*F:F))}

SumProduct – Example 3

At times we see a double hyphen (- -) in a formula. In Excel the double hyphen is known as a unary operator. The purpose of the unary operator is to force text values to 0/1 values where zero means false and one means true.

SumProduct Formula
=SUMPRODUCT(–(C:C=”Maine”)*–(D:D=”Sales”),E:E,F:F)

Like example 2 except now the – operator converts values where a cell contains Maine or Sales to 0 for false and 1 for true, then uses the corresponding 1 values to determine which values in columns E and F are multiplied and summed.

SumProduct is also used to compare values in two columns to count how many words or numbers match (typically columns in separate workbooks). In general, do not sort the data, this technique compares corresponding values within the same row.

SumProduct Formula
=SUMPRODUCT(–($C:$C=”Maine”),–($I:$I=”Maine”))

This formula asks Excel to compare the values in column C to the values in column I for the word Maine. The unary operator converts results to 0 for false or 1 for true, and then the SumProduct function sums the values for 1/true.

SumProduct can also compare the value in two columns to count identical values

SumProduct Formula
=SUMPRODUCT(–(E:E=40),–(K:K=40))

Like the previous example, this formula asks SumProduct to sum the 1 values if the numbers in column E and K are identical and equal 40.

SumProduct – Example 4

The most valuable use of the SumProduct is to calculate a weighted average (this question only occurs about once every ten years). The straight average sums the values and divides by the count. A weighted average considers some values have a higher weight. In this example imagine the weighted average is based on the count of Divisions, where there are six Connecticut values, four Maine values, and five New Hampshire and five Vermont values.

In this example the SumProduct weights the average gross pay based on the count of divisions.

SumProduct Formula – Longhand notation for weighted average
=SUMPRODUCT({780,780,860,504,589,504,350,213,860,589,472.15,176,860,880,443.75,880,472.15,780,840,350},{4,6,4,6,5,6,5,6,5,6,5,5,5,5,5,5,6,4,4,5})/SUM(H2:H21)

SumProduct Formula – Shorthand notation for weighted average
=SUMPRODUCT(E2:E21,F2:F21,H2:H21)/SUM(H2:H21)

Two Criteria Lookups

Two Criteria Lookups

In the process of teaching Excel over the past 20 years I have found that most students are often shocked and unaware that Vlookup and Index/Match can only locate the first item in a list. They find this unacceptable and I would tend to agree. When a value repeats in many rows, Microsoft suggest you use the upcoming Xlookup function to locate what is known as the ‘nth’ occurrence, but most Office365 installations will not offer that feature before July 2020 – at the earliest! For a long time there has been a workaround that does not require Xlookup, which despite Microsoft’s best intentions likely is not necessary!


In the “real world” a list typically includes items that repeat. Consider an invoice, it will typically include many line items for a single invoice number. Where one column may contain a repeating invoice number, the line items in rows could be for the same product with different size or color options. I recently came across several clients who require this functionality. While the topic of this blog post is beyond most students needs, I include it here because the class notes have finite space. This post assumes that students have taken the advanced Excel class and already understand how to work with Index and Match functions.


To follow along with the example, I recommend you download this sample workbook: Index Match Two Criteria


In this example notice that column A contains invoice numbers that repeat (orange cells – first criteria), and column B shows a unique price (second criteria). The goal is to locate the correct quantity for each invoiced item based on its price.


two criteria lookups

The formula required in cell C2 is:


=INDEX(DataToSearch!$F$1:$F$5061,MATCH(1,(InvoiceTwoCriteria!A2=DataToSearch!$B$1:$B$5061)*(InvoiceTwoCriteria!B2=DataToSearch!$E$1:$E$5061),0))


Given the formula’s complexity, I also recommend simplifying the readability by using named ranges, which could read:


=INDEX(Quantity,MATCH(1,(InvoiceTwoCriteria!A2=InvoiceNumber)*(InvoiceTwoCriteria!B2=Price),0))


After copying one of these formulas, take a moment to study and read it but… do not simply press the enter key, this formula requires the use of an array – hold both Ctrl+Shift while pressing the Enter key. As you study the format the formula, pay special attention to the placement of the open and close parentheses!


Because it is any array formula, we cannot reference an entire column, it requires a fixed range with absolute references (recall that named ranges are also absolute references). Also bear in mind that array formulas have limitations that can be found at this URL: https://docs.microsoft.com/en-us/office/troubleshoot/excel/arrays-limitations


The way to read and interpret the formula is:


Index (return) a value from column F. The Match lookup value of 1 creates a matrix of 1/0 values. The Match lookup array for criteria one reads: Look for the value in cell A2 (invoice #1) by searching cells B1 to B5061. The multiplication symbol before the second lookup array is treated like an AND operator. The second lookup array reads: find the price in cell B2 (34.95) by searching cells E1 to E5061. When the matrix returns a 1 match (true) for the invoice in column B, and a 1 match (true) for the price in column e, then return the corresponding value from column F (price). And if some of you are wondering, yes, this can be extended to include three criteria if needed!


If you would like to practice creating these types of formulas, download the following workbook and enable macros. Note that the submit answer feature is not expecting you use named ranges, though not incorrect the program is looking for range references, not named ranges! Index Match Two Criteria Lab