Chapter 9
Using Powerful Functions: Logical, Lookup, and Database Functions

This chapter covers four groups of workhorse functions. If you process spreadsheets of medium complexity, you turn to logical and lookup functions regularly.

• The logical functions, including the ubiquitous IF function, help make decisions.

• The information functions might be less important than they once were because Microsoft has added the IFERROR function, but INFO, CELL, and TYPE still come in handy. The lookup functions include the powerful VLOOKUP, MATCH, and INDIRECT functions. These functions are invaluable, particularly when you are doing something in Excel when it would be better to use Access 2019.

• The database functions provide the D functions, such as DSUM and DMIN. Even though these functions fell out of favor with the introduction of pivot tables, they are a powerful set of functions that are worthwhile to master.

• Linked Data Types debuted in 2018 with support for Stock Quotes and Geography. Over time, expect Excel to support more data types. This chapter ends with a discussion of formulas to pull data from Linked Data Types.

Table 9.1 provides an alphabetical list of all the logical functions in Excel 2019. Detailed examples of these functions are provided later in this chapter.

Table 9.2 provides an alphabetical list of the information functions in Excel 2019. Detailed examples of these functions are provided in the remainder of the chapter.

Table 9.3 provides an alphabetical list of the lookup functions in Excel 2019. Detailed examples of these functions are provided later in this chapter.

Table 9.4 provides an alphabetical list of all the database functions in Excel 2019. Detailed examples of these functions are provided later in this chapter.

Examples of Logical Functions

With only eight functions, the logical function group is one of the smallest in Excel. The IF function is easy to understand, and it enables you to solve a variety of problems.

Using the IF Function to Make a Decision

Many calculations in our lives are not straightforward. Suppose that a manager offers a bonus program if her team meets its goals. Or perhaps a commission plan offers a bonus if a certain profit goal is met. You can solve these types of calculations by using the IF function.

Syntax:

Click here to view code image

IF(logical_test,value_if_true,value_if_false)

There are three arguments in the IF function. The first argument is any logical test that results in a TRUE or FALSE. For example, you might have logical tests such as these:

A2>100
B5="West"
C99<=D99

All logical tests involve one of the comparison operators shown in Table 9.5.

The remaining two arguments are the formula or value to use if the logical test is TRUE and the formula or value to use if the logical test is FALSE.

When you read an IF function, you should think of the first comma as the word then and the second comma as the word otherwise. For example, =IF(A2>10,25,0) would be read as “If A2>10, then 25; otherwise, 0.”

Figure 9.1 calculates a sales commission. The commission rate is 1.5 percent of revenue. However, if the gross profit percentage is 50% or higher, the commission rate is 2.5 percent of revenue.

In this case, the logical test is H2>=50%. The formula for whether that test is true is 0.025*F2. Otherwise, the formula is 0.015*F2. You could build the formula as =IF(H2>=50%,0.025*F2,0.015*F2).

Using the AND Function to Check for Two or More Conditions

The previous example had one simple condition: If the value in column H was greater than or equal to 50%, the commission rate changed.

However, in many cases, you might need to test for two or more conditions. For example, suppose that a retail store manager offers a $25 bonus for every leather jacket sold on Fridays this month. In this case, the logical test requires you to determine whether both conditions are true. You can do this with the AND function.

Syntax:

AND(logical1,logical2,...)

The arguments logical1,logical2,... are from one to 255 expressions that evaluate to either TRUE or FALSE. The function returns TRUE only if all arguments are TRUE.

In Figure 9.2, the function in cell F2 checks whether cell E2 is a jacket and whether the date in cell D2 falls on a Friday:

=AND(E2="Jacket",WEEKDAY(D2,2)=5)

Using OR to Check Whether One or More Conditions Are Met

In the earlier examples, all the conditions had to be met for the IF function to be true. In other cases, you might need to identify when exactly one condition is true, or when one or more conditions are true.

For example, a sales manager may want to reward big orders and orders from new customers. The manager may offer a commission bonus if the order is more than $50,000 or if the customer is a new customer this year. The bonus is awarded if either condition is true. But only one bonus is paid; you do not give two bonuses if a customer is both new and the order is large. In this case, you would use the OR function with logical tests to check whether the customer is new or if the order is large.

To test whether a particular sale meets either condition, use the OR function. The OR function returns TRUE if any condition is TRUE and returns FALSE if none of the conditions are TRUE.

Syntax:

OR(logical1,logical2,...)

The OR function checks whether any of the arguments are TRUE. It returns a FALSE only if all the arguments are FALSE. If any argument is TRUE, the function returns TRUE.

The arguments logical1,logical2,... are 1 to 255 conditions that can evaluate to TRUE or FALSE.

Nesting IF Functions Versus IFS SWITCH or CHOOSE

The IF function offers only two possible values: Either the logical test is TRUE, and the first formula or value is used, or the logical test is FALSE and the second formula or value is used.

Many situations have a series of choices. For example, in a human resources department, annual merit raises might be given based on the employee’s numeric rating in an annual review in which employees are ranked on a five-point scale. The rules for setting the raise are as follows:

• 5: 8% raise

• 4: 7% raise

• 3: 5% raise

• 2: 3% raise

• 1: No raise

Traditionally, you would test for five conditions by nesting four IF functions:

Click here to view code image

=IF(D2=5,8%,IF(D2=4,7%,IF(D2=3,5%,IF(D2=2,3%,0%))))

You only needed four IF functions to test for five conditions. After testing for the first four conditions, the fifth answer would be provided in the Value_If_False for the last IF function.

In February 2017, Office 365 customers were offered two alternatives:

Click here to view code image

=IFS(D2=5,8%,D2=4,7%,D2=3,5%,D2=2,3%,TRUE,0%)

=SWITCH(D2,5,8%,4,7%,3,5%,2,3%,0%)

Syntax:

Click here to view code image

IFS(logical_test1, value_if_true1, [logical_test2, value_if_true2…])

Syntax:

Click here to view code image

SWITCH(Expression, Value1, Result1,[Default_or_value2],[Result2]…)

In the IFS function, you can handle multiple conditions without nesting new functions. The IFS means that you have multiple IF conditions. The IFS formula above reads, “If D2 is 5, then return 8%. Otherwise, if D2=4, then return 7%. Otherwise, if D2=3, then return 5%. Otherwise, if D2=2, then return 3%.” The last two arguments in IFS are a little bizarre. You essentially want to have a value to return if none of the previous conditions are true. You need to put a logical test that is always True. Explicitly typing TRUE solves the problem.

The SWITCH function is better, in this case, because you only specify cell D2 once. You tell the SWITCH function that you want to return a value based on the value of D2. If it is 5, then 8%. If it is 4, then 7%. If it is 3, then 5%. If it is 2, then 3%. For any other value, use 0%. Note that SWITCH does not require you to enter the TRUE argument as the second-to-last argument.

In this particular case, because the five possible scores are 1 through 5, the CHOOSE function will be shortest:

=CHOOSE(D2,0,3%,5%,7%,8%)

The CHOOSE function points to a single value and then expects the value to return if the answer is 1, 2, 3, 4, 5, and so on.

Note that Excel classifies CHOOSE as a Lookup and Reference function instead of a Logical function.

Figure 9.3 compares the four formulas. In this case, CHOOSE is the shortest.

CHOOSE will not always be the shortest formula. SWITCH will win if you have to look for values that are not sequential or don’t have to start with 1:

Click here to view code image

=SWITCH(A2,30,"CURRY",35,"DURANT",23,"GREEN",11,"THOMPSON","OTHER")

IFS will be better if you need to look for ranges of values:

Click here to view code image

=IFS(A2>80,"Top Tier",A2>50,"Group 2",A2>20,"Group 3",TRUE,"Bottom Tier")

Nested IF will be better if there is any chance the workbook will be opened in Excel 2016, Excel 2013, Excel 2010, or an earlier version. The new IFS and SWITCH functions will return #NAME? error if opened in a prior version of Excel.

Using the NOT Function to Simplify the Use of AND and OR

In the language of Boolean logic, there are typically NAND, NOR, and XOR functions, which stand for Not And, Not Or, and Exclusive Or. To simplify matters, Excel offers the NOT function.

Syntax:

NOT(logical)

Quite simply, NOT reverses a logical value. TRUE becomes FALSE, and FALSE becomes TRUE when processed through a NOT function.

For example, suppose you need to find all flights landing outside Oklahoma. You can build a massive OR statement to find every airport code in the United States. Alternatively, you can build an OR function to find Tulsa and Oklahoma City and then use a NOT function to reverse the result: =NOT(OR(A2="Tulsa",A2="Oklahoma City")).

Using the IFERROR or IFNA Function to Simplify Error Checking

The IFERROR function, which was introduced in Excel 2007, was added at the request of many customers. To better understand the IFERROR function, you need to understand how error checking was performed during the 22 years before Excel 2007 was released.

Consider a typical spreadsheet that calculates a ratio of sales to hours. A formula of =B2/C2 returns the #DIV/0 error in the records when column C contains a zero. The typical workaround is to test for the error condition: =IF(C2=0,0,B2/C2).

In legacy versions of Excel, it was typical to use this type of IF formula on thousands of rows of data. The formula is more complex and takes longer to calculate than the new IFERROR function. However, this particular formula is tame compared to some of the formulas needed to check for errors.

A common error occurs when you use the VLOOKUP function to retrieve a value from a lookup table. In Figure 9.4, the VLOOKUP function in cell D2 asks Excel to look for the rep number S07 from cell B2 and find the corresponding name in the lookup table of F2:G9. This works great, returning JESSE from the table. However, a problem arises when the sales rep is not found in the table. In row 7, rep S09 is new and has not yet been added to the table, so Excel returns the #N/A result.

If you want to avoid #N/A errors, the generally accepted workaround in legacy versions of Excel was to write this horrible formula:

Click here to view code image

=IF(ISNA(VLOOKUP(B7,$F$2:$G$9,2,FALSE)),"New Rep", VLOOKUP(B7,$F$2:$G$9,2,FALSE))

In English, this formula says first to find the rep name in the lookup table. If the rep is not found and returns the #N/A error, then use some other text, which in this case is the words New Rep. If the rep is found, then perform the lookup again and use that result.

Because VLOOKUP was one of the most time-intensive functions, it was horrible to have Excel perform every VLOOKUP twice in this formula. In a data set with 50,000 records, it could take minutes for the VLOOKUP to complete. Microsoft wisely added the new IFERROR function in Excel 2010 to handle all these error-checking situations.

Starting in Excel 2013, Microsoft has added the IFNA function. It works just like the IFERROR function, but the second argument is used only when the first argument results in an #N/A error. You might be able to imagine a situation in which you want to replace the #N/A errors but allow other errors to appear.

Syntax:

IFERROR(value,value_if_error)

The advantage of the IFERROR function is that the calculation is evaluated only once. If the calculation results in any type of an error value, such as #N/A, #VALUE!, #REF!, #DIV/0!, #NUM!, #NAME?, or #NULL!, Excel returns the alternative value. If the calculation results in any other valid value, whether it is numeric, logical, or text, Excel returns the calculated value.

Syntax:

IFNA(value,value_if_na)

If the expression evaluates to a value of #N/A, then IFNA returns value_if_na instead of the expression. Added in Excel 2013, this function replaces only #N/A errors and allows other errors to appear as the result.

The formula from the preceding section can be rewritten as =IFERROR(VLOOKUP(B7, $F$2:$G$9,2,FALSE),"New Rep") or as =IFNA(VLOOKUP(B7, $F$2:$G$9,2,FALSE),"New Rep"). Although IFNA is a bit shorter than IFERROR, the new IFNA function fails for anyone using Excel 2010 or earlier. This makes IFERROR a safer function to use for the next several years. Either IFERROR or IFNA calculates much more quickly than putting two VLOOKUPs in an IF function.

Examples of Information Functions

Found under the More Function icon, the 20 information functions return eclectic information about any cell. Eleven of the 20 functions are called the IS functions because they test for various conditions.

Using the ISFORMULA Function with Conditional Formatting to Mark Formula Cells

The Excel team introduced the ISFORMULA function in Excel 2013 to identify whether a cell contains a formula. A hack had been floating around for years to mark formula cells using an old XL4 Macro Language function. Being able to use ISFORMULA is a great improvement.

Syntax:

ISFORMULA(reference)

Checks whether reference contains a formula. Returns TRUE or FALSE.

Figure 9.5 shows a worksheet in which all the cells have a conditional formatting formula that uses =ISFORMULA. Any cells that contain a formula are shown in white text on black fill.

Using IS Functions to Test for Types of Values

The remaining IS functions enable you to test whether a cell contains numbers, text, or various other data types.

Figure 9.6 shows a common solution. Column C contains a mix of text and numeric ZIP Codes. The formula in column D, =IF(ISNONTEXT(C5),RIGHT("0000"&C5,5),C5), replaces numeric ZIP Codes with text ZIP Codes. If the value in column C is nontext, the program pads the left side of the ZIP Code with zeros and then takes the five rightmost digits.

Using the N Function to Add a Comment to a Formula

You can call Excel’s N function a creative use for an obsolete function. Lotus 1-2-3 used to offer an N() function that converted True to 1 and False to 0. The N of any text is zero. Some have figured they could use this function to add a comment to a formula:

Click here to view code image

=VLOOKUP(A2,MyTable,2,False)+N("The False ensures an exact match. Don't omit False")

Using the NA Function to Force Charts to Not Plot Missing Data

Suppose that you are in charge of a school’s annual fund drive. Each day, you mark the fundraising total on a worksheet by following these steps:

1. In column A, you enter the results of each day’s collection through nine days of the fund drive (see Figure 9.7).

   

2. You enter a formula in column C to keep track of the total collected throughout the fund drive.

3. To avoid making it look like the fund drive collected nothing in days 10 through 14, you enter a formula in column C to check whether column A is blank. If it is, then the IF function inserts a null cell in column C. For example, the formula in cell C15 is =IF(ISBLANK(A15),"",A15+C14).

4. You build a line chart based on B1:C15. You then add a trendline to the chart to predict future fundraising totals.

5. As shown in columns A:C of Figure 9.7, this technique fails. Even though the totals for days 10 through 14 are blank, Excel charts those days as zero. The linear trendline predicts that your fundraising will go down, with a projected total of just over $2,000.

6. You try the same chart again, but this time you use the NA function instead of "" in the IF statement in step 3. The formula is shown in cell H16, and the results are in cell J15. Excel understands that NA values should not be plotted. The trendline is calculated based on only the data points available and projects a total just under $18,000.

In many cases, you are trying to avoid #N/A! errors. However, in the case of charting a calculated column, you might want to have #N/A! produce the correct look to the chart.

Using the CELL Function to Return the Worksheet Name

The CELL function can tell you information about a specific cell. Although the function can return many ancient bits of information (Excel 2003 color index, for example), it has one argument that allows you to put the worksheet name in a cell.

=CELL("filename",A1) returns the complete path, filename, and worksheet name. The technique is to locate the right square bracket at the end of the filename. Everything after that character is the worksheet tab name.

Figure 9.8 shows an example.

Examples of Lookup and Reference Functions

The Lookup & Reference icon contains 18 functions. The all-star of this group is the venerable VLOOKUP function, which is one of the most powerful and most used functions in Excel. As database people point out, a lot of work done in Excel should probably be done in Access. The VLOOKUP function enables you to perform the equivalent of a join operation in a database.

This lookup and reference group also includes several functions that seem useless when considered alone. However, when combined, they allow for some very powerful manipulations of data. The examples in the following sections reveal details on how to use the lookup functions and how to combine them to create powerful results.

Using the CHOOSE Function for Simple Lookups

Most lookup functions require you to set up a lookup table in a range on the worksheet. However, the CHOOSE function enables you to specify up to 254 choices right in the syntax of the function. The formula that requires the lookup should be able to calculate an integer from 1 to 254 to use the CHOOSE function.

Syntax:

Click here to view code image

CHOOSE(index_num,value1,value2,...)

The CHOOSE function chooses a value from a list of values, based on an index number. The CHOOSE function takes the following arguments:

• index_num—This specifies which value argument is selected. index_num must be a number between 1 and 254 or a formula or reference to a cell containing a number between 1 and 254:

  • If index_num is 1, CHOOSE returns value1; if it is 2, CHOOSE returns value2; and so on.

  • If index_num is a decimal, it is rounded down to the next lowest integer before being used.

  • If index_num is less than 1 or greater than the number of the last value in the list, CHOOSE returns a #VALUE! error.

• value1,value2,...—These are 1 to 254 value arguments from which CHOOSE selects a value or an action to perform based on index_num. The arguments can be numbers, cell references, defined names, formulas, functions, or text.

The example in Figure 9.9 shows survey data from some respondents. Columns B:F indicate their responses on five measures of your service. Column G calculates an average that ranges from 1 to 5. Suppose that you want to add words to column H to characterize the overall rating from the respondent. The following formula is used in cell H4:

Click here to view code image

=CHOOSE(G4,"Strongly Disagree","Disagree","Neutral","Agree","Strongly Agree")

Using VLOOKUP with TRUE to Find a Value Based on a Range

VLOOKUP stands for vertical lookup. This function behaves differently, depending on the fourth parameter. This section describes using VLOOKUP in which you need to choose a value based on a table that contains ranges.

Suppose that you have a list of students and their scores on a test. The school grading scale is based on these ranges:

• 92–100 is an A.

• 85–91 is a B.

• 70–84 is a C.

• 65–69 is a D.

• Below 65 is an F.

Follow these steps to set up a VLOOKUP for this scenario:

1. Because in this version of VLOOKUP you do not have to list every possible grade, build a table showing the scores where the grading scale changes from one grade to the next.

2. Although the published grading scale starts with the higher values, your lookup table must be sorted in ascending sequence. This requires a bit of translation as you set up the table. Although the grading scale says that below 65 is an F, you need to set up the table to show that an F corresponds to any grade at 0 or higher. Therefore, in cell E2 enter 0, and in cell F2, enter F (see Figure 9.10).

   

3. Continue building the grading scale in successive rows of columns E and F. Anything above a 65 is given a D. Anything above 70 is given a C. Note that this is somewhat counterintuitive because it is the opposite order that you would use if you were building a grading scale using nested IF functions.

4. Ensure that the numeric values are the leftmost column in your lookup table. In Figure 9.10, the lookup table range is E2:F6. When you use VLOOKUP, Excel searches the first column of the lookup table for the appropriate score.

5. When using this version of VLOOKUP with ranges, sort the list in ascending order. If you are not sure of the proper order, use the Sort command from the Home tab to sort the table.

6. Because the first argument in the VLOOKUP function is the student’s score, in cell C2, enter =VLOOKUP(B2,.

7. Because the next argument is the range of the lookup table, be sure to press the F4 key after entering E2:F6 to change to an absolute reference of $E$2:$F$6.

8. Ensure that the third argument specifies which column of the lookup table should be returned. Because the letter grade is in the second column of E2:F6, use 2 for the third argument.

9. Ensure that the final argument is either TRUE or simply omitted. This tells Excel that you are using the sorted range variety of lookup.

10. After you enter the formula in cell C2, again select cell C2 and double-click the fill handle to copy the formula down to all students.

Using VLOOKUP with FALSE to Find an Exact Value

In some situations, you do not want VLOOKUP to return a value based on a close match. Instead, you want Excel to find the exact match in the lookup table.

Figure 9.11 shows a table of sales. The original table had columns A through C: Rep, Date, and Sale Amount. Although a data analyst might have all the rep numbers memorized, the manager who is going to see the report prefers to have the rep names on the report.

To fill in the rep names from a lookup table, you follow these steps:

1. In columns F and G, enter a table of rep numbers and rep names. Note that it is not important for this table to be sorted by the rep number field. It is fine that the table is sorted alphabetically by name.

2. Use FALSE as the fourth parameter in VLOOKUP. You need to do this because close matches are not acceptable here. If something was sold by a new rep with number R9, you do not want to give credit to the name associated with R8 just because it is a close match. Either Excel finds an exact match and returns the result, or Excel does not give you a result.

3. For cell D2, you want Excel to use the rep number in A2, so in cell D2, enter =VLOOKUP(A2,.

4. The lookup table is in F2:G7, so enter F2:G7 and then press the F4 key to make the reference absolute. This enables you to copy the formula in step 7. After pressing F4, type a comma.

5. In the lookup table, the rep name is in column 2 of the table, so type 2 to specify that you want to return the second column of the lookup table.

6. Finish the function with FALSE). Press Ctrl+Enter to accept the formula and keep the cursor in cell D2.

7. Double-click the fill handle to copy the formula down to all the rows.

8. VLOOKUP is a very time-intensive calculation. Having thousands of VLOOKUP formulas significantly affects your recalculation times. In this particular case, you have successfully added rep names. It would be appropriate to convert these live formulas to their current values. Therefore, press Ctrl+C to copy. Then, from the Home tab, select Paste, Paste Values to convert the formulas to values.

   Note

   If your lookup table is arranged sideways, with going across a row, you should use HLOOKUP. If your data is vertical, but the key field is not the leftmost column, you can use a combination of INDEX and MATCH, also explained later in this chapter.

9. Look through the results. If a sale was credited to a new rep who is not in the table, the name appears as #N/A. Manually fix these records, if needed.

To recap, the two versions of the VLOOKUP formula behave very differently. VLOOKUP with FALSE as the fourth parameter looks for an exact match, whereas VLOOKUP with TRUE as the fourth parameter looks for the closest (lower) match. In the TRUE version, the lookup table must be sorted. In the FALSE version, the table can be in any sequence. In every case, the key field must be in the left column of the lookup table.

Syntax:

Click here to view code image

VLOOKUP(lookup_value,table_array,col_index_num,range_lookup)

VLOOKUP searches for a value in the leftmost column of a table and then returns a value in the same row from a column you specify in the table. The VLOOKUP function takes the following arguments:

• lookup_value—This is the value to be found in the first column of the table. lookup_value can be a value, reference, or text string.

• table_array—This is the table of information in which data is looked up. You can use a reference to a range, such as E2:F9, or a range name such as RepTable.

• col_index_num—This is the column number in table_array from which the matching value must be returned. A col_index_num value of 1 returns the value in the first column in table_array; a col_index_num value of 2 returns the value in the second column in table_array, and so on. If col_index_num is less than 1, VLOOKUP returns the #VALUE! error value; if col_index_num is greater than the number of columns in table_array, VLOOKUP returns the #REF! error value.

• range_lookup—This is a logical value that specifies whether VLOOKUP should find an exact match or an approximate match. If it is TRUE or omitted, an approximate match is returned. In other words, if an exact match is not found, the next largest value that is less than lookup_value is returned. If it is FALSE, VLOOKUP finds an exact match. If one is not found, the error value #N/A is returned. If VLOOKUP cannot find lookup_value and if range_lookup is TRUE, it uses the largest value that is less than or equal to lookup_value. If lookup_value is smaller than the smallest value in the first column of table_array, VLOOKUP returns an #N/A error. If VLOOKUP cannot find lookup_value, and range_lookup is FALSE, VLOOKUP returns an #N/A error.

Using VLOOKUP to Match Two Lists

If Excel is used throughout your company, you undoubtedly have many lists in Excel. People use Excel to track everything. How many times are you faced with a situation in which you have two versions of a list and you need to match them up?

In Figure 9.12, the worksheet has two simple lists. Column A shows last week’s version of who was coming to an event. Column C shows this week’s version of who is coming to an event. Follow these steps if you want to find out quickly if anyone is new:

1. Add the heading There? to cell D2.

2. Because the formula in cell D3 should look at the value in cell C3 to see whether that person is in the original list in column A, start the formula with =VLOOKUP(C3,$A$3:$A$15,.

3. Because your only choice for the column number is to return the first column from the original list, finish the function with 1,FALSE). Then press Ctrl+Enter to accept the formula and stay in cell D3.

4. Double-click the fill handle to copy the formula down to all rows.

   

For any cells in which column D contains a name, it means that the person was on the RSVP list from last week. If the result of the VLOOKUP is #N/A, you know that this person is new since the previous week.

Using the MATCH Function to Locate the Position of a Matching Value

At first glance, MATCH seems like a function that would rarely be useful. MATCH returns the relative position of an item in a range that matches a specified value in a specified order. You use MATCH instead of one of the lookup functions when you need the position of an item in a range instead of the item itself.

Suppose that your manager asks, “Can you tell me on which row I would find this value?” The manager wants to know the value or some piece of data on that record. However, rarely would the manager want to know that XYZ is found on the 111th relative row within the range A99:A11432.

MATCH comes in handy in several instances. In the first instance, consider a situation in which you are using VLOOKUP to find whether an item is in a list. In this case, you do not care what value is returned. You are interested in seeing either whether a valid value is returned, meaning that the entry is in the old list, or whether an #N/A is returned, meaning that the entry is new. In this case, using MATCH is a slightly faster way to achieve the same result.

Another handy way to use MATCH is with the INDEX function. MATCH has two features that make it more versatile than VLOOKUP. MATCH allows for wildcard matches. MATCH also allows for a search based on an exact match, based on the number just below the value, or based on a value greater than or equal to the lookup value. This third option is not available in the VLOOKUP or HLOOKUP functions.

Syntax:

Click here to view code image

MATCH(lookup_value,lookup_array,match_type)

The MATCH function returns the relative position of an item in a column or row of values. It is useful for determining if a certain value exists in a list.

The MATCH function takes the following arguments:

• lookup_value—This is the value you use to find the value you want in a table. lookup_value can be a value, which is a number, text, or logical value or a cell reference to a number, text, or logical value.

• lookup_array—This is a contiguous range of cells that contains possible lookup values. lookup_array can be an array or an array reference.

• match_type—This is the number –1, 0, or 1. Note that you can use TRUE instead of 1 and FALSE instead of 0. match_type specifies how Microsoft Excel matches lookup_value with values in lookup_array. If match_type is 1, MATCH finds the largest value that is less than or equal to lookup_value. lookup_array must be placed in ascending order, such as –2, –1, 0, 1, 2,...; A–Z; or FALSE, TRUE. If match_type is 0, MATCH finds the first value that is exactly equal to lookup_value. lookup_array can be in any order. If match_type is –1, MATCH finds the smallest value that is greater than or equal to lookup_value. lookup_array must be placed in descending order, such as TRUE, FALSE; Z–A; or 2, 1, 0, –1, –2,... If match_type is omitted, it is assumed to be 1.

MATCH returns the position of the matched value within lookup_array, not the value itself. For example, MATCH("b",{"a","b","c"},0) returns 2, the relative position of b within the array {"a","b","c"}.

MATCH does not distinguish between uppercase and lowercase letters when matching text values. If MATCH is unsuccessful in finding a match, it returns an #N/A error.

If match_type is 0 and lookup_value is text, lookup_value can contain the wildcard characters asterisk (*) and question mark (?). An asterisk matches any sequence of characters; a question mark matches any single character.

Using MATCH to Compare Two Lists

You might face situations in which you have two versions of a list, and you need to match them up.

In Figure 9.13, the worksheet has two simple lists. Column A shows last week’s list. Column C shows this week’s version of the list. You want to find out quickly which items are new. Here’s how you do it:

1. Add the heading There? to cell D2.

2. Because the formula in cell D3 looks at the value in cell C3 to see if that value is in the original list in column A, start the formula with =MATCH(C3,$A$3:$A$11,.

3. Because you want an exact match, use 0 as the third parameter. Finish the function with a ). Press Ctrl+Enter to accept the formula and stay in cell C3.

4. Double-click the fill handle to copy the formula down to all rows.

   

For any cells in which column D contains a number, it means that the entry was on the original list from last week. If the result of MATCH is #N/A, you know that this item is new since the previous week.

Using INDEX and MATCH for a Left Lookup

INDEX is another function that does not immediately seem to have many great uses. In its basic form, INDEX returns the value from a particular row and column of a rectangular range. It returns a value from a particular position of a vertical or horizontal vector.

Typically, you specify a rectangular range and then indicate the row number and column number of the value that you want to return. In Figure 9.14, the formula in C3 returns the third row and second column of B5:D9. Certainly, this is a needlessly complicated way to point to cell C7.

INDEX becomes interesting when you have a formula calculating the position argument. Still in Figure 9.14, a list of people is in M1:M7. You can randomly select from the list by using INDEX and RANDBETWEEN(1,7), as shown in C4.

If you specify zero as the row or column argument, INDEX returns the entire row or column. The INDEX in H8 is returning all three values from row 4 of the table, so you have to wrap the index function in a SUM or COUNT or AVERAGE function.

The data in row 14 illustrates an undocumented feature of INDEX. When the reference contains data in a single row, you can specify the column number as the second argument. To get the data for September, you can use the correct =INDEX(A14:L14,0,9) or the shortened =INDEX(A14:L14,9). In Figure 9.14, the formula in C11 returns the value from the current month by using =MONTH(TODAY()) to return a 9 as the second argument of the INDEX function. (This was written in September, hence the 9.0.)

You’ve reached Excel guru status when you start combining INDEX and MATCH. On its own, neither INDEX nor MATCH seems particularly useful. Used together, though, they become a powerful combination that is more flexible than VLOOKUP and often faster to calculate than VLOOKUP.

In Figure 9.15, a customer number is in cell B1. The customer lookup table appears in columns F, G, and H. The main problem is that the customer table does not have the customer number on the left side.

In many cases, you would copy column H to column E and use column E as the key of the table. However, the table in F:H is likely to be repopulated every day from a web query or an OLAP query. Therefore, it might become monotonous to move the data after every refresh. The solution is to use a combination of INDEX and MATCH. Here’s what you do:

1. Use the formula =MATCH(B1,H2:H89,0) to search through column H to find the row with the customer number that matches the one in cell B1. In this case, C499 is in row 9, which is the eighth row of the table.

2. Be sure to use exactly the same shape range as the first argument in the INDEX function: =INDEX(F2:F89,WhichRow,WhichColumn) searches through the customer names in column F.

3. For the second parameter of the INDEX function, specify the relative row number. This information was provided by the MATCH function in step 1.

4. Ensure that the third parameter of the INDEX function is the relative column number. Because the range F2:F89 has only one column, this is either 1 or can simply be omitted.

5. Putting the formula together, the formula in cell B2 is =INDEX(F2:F89,MATCH(B1,H2:H89,0),1).

Syntax:

Click here to view code image

INDEX(array,row_num,[column_num])
INDEX(reference,row_num,[column_num],[area_num])

The INDEX function returns the value at the intersection of a particular row and column within a range. This function takes the following arguments:

• array—This is a range of cells or an array constant. If array contains only one row or column, the corresponding row_num or column_num argument is optional. If array has more than one row and more than one column, and if only row_num or column_num is used, INDEX returns an array of the entire row or column in array.

• row_num—This selects the row in array from which to return a value. If row_num is omitted, column_num is required.

• column_num—This selects the column in array from which to return a value. If column_num is omitted, row_num is required.

If both the row_num and the column_num arguments are used, INDEX returns the value in the cell at the intersection of row_num and column_num.

If you set row_num or column_num to 0, INDEX returns the array of values for the entire column or row, respectively. To use values returned as an array, you use the INDEX function as an array formula in a horizontal range of cells for a row and in a vertical range of cells for a column. To enter an array formula, you press Ctrl+Shift+Enter. Thanks to modern arrays introduced in September 2018, customers using Office 365 will not need to press Ctrl+Shift+Enter.

row_num and column_num must point to a cell within array; otherwise, INDEX returns a #REF! error.

Using MATCH and INDEX to Fill a Wide Table

The lookup functions VLOOKUP, HLOOKUP, and MATCH can be very processor intensive when the lookup table contains hundreds of thousands of rows. The problem is worse when you have to return multiple columns from the same row of the lookup table. If it takes Excel 3 seconds to find the matching row for column 2 of the table, it will take another 3 seconds to find the matching row for column 3 of the table. If you hope to return 12 monthly columns, it could take 36 seconds.

Instead, you could find the matching row once using a MATCH function in a helper column. After the MATCH identifies the correct row, 12 INDEX functions can return the values for each month. INDEX is incredibly fast. The 13 formulas will run in 12% of the time it takes to run 12 VLOOKUP formulas.

Figure 9.16 illustrates the technique. A MATCH function in column C figures out which row contains the match. INDEX functions in D5:O12 return the monthly numbers.

Performing Many Lookups with LOOKUP

Even Excel Help tells you to avoid the old LOOKUP function. However, LOOKUP can do one useful trick that VLOOKUP and HLOOKUP cannot do—it can process many lookups in one single array formula. LOOKUP can also deal with a lookup range that is vertical and a return range that is horizontal, or vice versa.

One additional superpower of the old LOOKUP function is the capability to look up several values at once. You have to use Ctrl+Shift+Enter to accept the formula, and because LOOKUP will be returning an array of answers, you should enclose the LOOKUP in a wrapper function such as SUM to add all the results from the function.

In Figure 9.17, a series of invoices appear in rows 4 through 17. A GP% (gross profit percentage) is associated with each invoice. The sales rep will earn a bonus depending on the GP% of each invoice, as shown in E6:F10. Instead of calculating a bonus for each row, you can calculate a bonus for all the rows at once. The formula in B1 of Figure 9.17 specifies an array of B4:B17 as the lookup value. This causes Excel to perform the LOOKUP 14 times, once for each value in the range B4:B17. The formula wraps the LOOKUP results in a SUM function to add up all the bonus results. To calculate correctly, you must hold down Ctrl+Shift while pressing Enter after typing this formula. When you press Ctrl+Shift+Enter, Excel adds the curly braces around the formula. You do not type the curly braces manually. Typing the curly braces will not work.

As of late 2018, any Office 365 customers will have access to modern arrays. This is a major change to how Excel calculates. You will no longer have to use Ctrl+Shift+Enter in the example above. Also, with the introduction of modern arrays, every Excel function can accept an array, so you can use =SUM(VLOOKUP(B4:B17,E6:F10,2,False)), and it will calculate correctly. Modern arrays are not in Excel 2019, so those customers will still have to use LOOKUP and Ctrl+Shift+Enter.

Using FORMULATEXT to Document a Worksheet

Quiz: Which Excel function is used the most in this book? It is FORMULATEXT. The FORMULATEXT function was added in Excel 2013. If you ask for the =FORMULATEXT(A1), Excel shows the formula that is in cell A1 as text. All the formulas shown in this book (such as cell C1 in Figure 9.17) are generated with the FORMULATEXT function.

You can use FORMULATEXT to document the formulas used in your worksheet. Normally, you can either print your worksheet with formulas showing or with the results from the formulas. By using FORMULATEXT, you can show both the formula and the result.

If you use FORMULATEXT on a cell with an array formula, the resulting text will be wrapped in curly braces that would be shown in the formula bar.

In Figure 9.18, the text of the formula shown in C3 comes from a FORMULATEXT function.

Syntax:

FORMULATEXT(reference)

This function returns a formula as text.

Troubleshooting

FORMULATEXT fails when your reference does not contain a formula.

There are people who had written their own versions of FormulaText as a VBA function before Excel 2013 added FormulaText. The version that was made popular on the Internet would return the formula as text if the cell contained a formula. Otherwise, it would return the value in the cell.

However, Excel’s built-in version of FORMULATEXT returns a #N/A error if the cell does not contain a formula.

One workaround is to wrap FORMULATEXT in IFNA. Instead of =FORMULATEXT(C2), use =IFNA(FORMULATEXT(C2),C2).

Two cells show an answer of 4. Next to each cell, a FORMULATEXT function attempts to show the formula in the adjacent cell. This works for the first cell, where the formula is =2+2. However, in the second cell, the FORMULATEXT returns a #N/A error because the cell simply contains the number 4 instead of a formula. A workaround of =IFNA(FORMULATEXT(B2),B2) will work for either formulas or values.

Using Numbers with OFFSET to Describe a Range

The language of Excel is numbers. There are functions that count the number of entries in a range. There are functions that can tell you the numeric position of a looked-up value. You might know that a particular value is found in row 20, but what if you want to perform calculations on other cells in row 20?

The OFFSET function handles this very situation. You can use OFFSET to describe a range using mostly numbers. OFFSET is flexible: It can describe a single cell, or it can describe a rectangular range.

Although INDEX can return a single cell, row, or column from a rectangular range, it has limitations. If you specify C5:Z99 as the range for an INDEX function, you can select only cells below and/or to the right of C5. The OFFSET function can move up and down or left and right from the starting cell, which is C5.

Syntax:

Click here to view code image

OFFSET(reference,rows,cols,height,width)

The OFFSET function returns a reference to a range that is a given number of rows and columns from a given reference. This function takes the following arguments:

• reference—This is the reference from which you want to base the offset. reference must be a reference to a cell or range of adjacent cells; otherwise, OFFSET returns a #VALUE! error.

• rows—This is the number of rows, up or down, that you want the upper-left cell to refer to. Using 5 as the rows argument, for example, specifies that the upper-left cell in the reference is five rows below reference. rows can be positive, which means below the starting reference, or negative, which means above the starting reference.

• cols—This is the number of columns to the left or right that you want the upper-left cell of the result to refer to. For example, using 5 as the cols argument specifies that the upper-left cell in the reference is five columns to the right of reference. cols can be positive, which means to the right of the starting reference, or negative, which means to the left of the starting reference. If rows and cols offset reference over the edge of the worksheet, OFFSET returns a #REF! error. Figure 9.19 demonstrates various combinations of rows and cols from a starting cell of cell C5.

  

• height—This is the height, in number of rows, that you want the returned reference to be. height must be a positive number.

• width—This is the width, in number of columns, that you want the returned reference to be. width must be a positive number. If height or width is omitted, Excel assumes it is the same height or width as reference.

OFFSET enables you to specify a reference. It does not move a cell. It does not change the selection. It is just a numeric way to describe a reference. OFFSET can be used in any function that is expecting a reference argument.

Excel Help provides a trivial example of =SUM(OFFSET(C2,1,2,3,1)), which sums E3:E5. However, this example is silly because no one would ever write such a formula! If you were to write such a formula, you would just write =SUM(E3:E5) instead. The power of OFFSET comes when at least one of the four numeric arguments is calculated by the COUNT function or a lookup function.

In Figure 9.20, you can use COUNT(A5:A99) to count how many entries are in column A. If you assume that there are no blanks in the range of data, you can use the COUNT result as the height argument in OFFSET to describe the range of numbers. Here’s what you do:

1. There is nothing magical about the reference, so write it as =OFFSET(A5,.

2. Do not move the starting position any rows or columns from cell A5. The starting position is A5, so you always use 0 and 0 for rows and columns. Therefore, the formula is now =OFFSET(A5,0,0,.

3. If you want to include only the number of entries in the list, use COUNT(A5:A999) as the height of the range. The formula is now =OFFSET(A5,0,0,COUNT(A5:A999),.

4. The width is one column, so make the function =OFFSET(A5,0,0,COUNT(A5:A999),1).

5. Use your OFFSET function anywhere you would normally specify a reference. You can use =SUM(OFFSET(A5,0,0,COUNT(A5:A999),1)) or specify that formula as the series in a chart. This creates a dynamic chart that grows or shrinks as the number of entries changes.

   

   Troubleshooting

   OFFSET is a volatile function and will slow recalculation of your worksheet. Avoid OFFSET using a little-known version of INDEX.

   Normally, =INDEX(A1:A10,5) will return the value stored in the fifth row of A1:A12. Excel guru Dan Mayoh discovered an alternate use for INDEX. If the INDEX function is placed adjacent to a colon, the INDEX function returns the cell address instead of the value stored in the cell. =SUM(A1:INDEX(A1:A12,5)) will sum A1:A5.

   Of course, you would not hard-code the 5 in the INDEX function. You might use MONTH(TODAY()) to dynamically choose the number corresponding to the current month.

   =SUM(A1:INDEX(A1:A12,MONTH(TODAY()))) is not volatile and does the same thing as =SUM(OFFSET(A1,0,0,MONTH(TODAY()),1)).

Using INDIRECT to Build and Evaluate Cell References on the Fly

The INDIRECT function is deceivingly powerful. Consider this trivial example: In cell A1, enter the text B2. In cell B2, enter a number. In cell C3, enter the formula =INDIRECT(A1). Excel returns the number that you entered in cell B2 in cell C3. The INDIRECT function looks in cell A1 and expects to find something that is a valid cell or range reference. It then looks in that address to return the answer for the function.

The reference text can be any text that you can string together using various text functions. This enables you to create complex references that dynamically point to other sheets or to other open workbooks.

The reference text can also be a range name. You could have a validation list box in which someone selects a value from a list. If you have predefined a named range that corresponds to each possible entry on the list, INDIRECT can point to the various named ranges on the fly.

When you use traditional formulas, even absolute formulas, there is a chance that someone might insert rows or columns that will move the reference. If you need a formula to always point to cell J10, no matter how someone rearranges the worksheet, you can use =INDIRECT("J10") to handle this.

Syntax:

INDIRECT(ref_text,a1)

The INDIRECT function returns the reference specified by a text string. This function takes the following arguments:

• ref_text—This is a reference to a cell that contains an A1-style reference, an R1C1-style reference, a name defined as a reference, or a reference to a cell as a text string. If ref_text is not a valid cell reference, INDIRECT returns a #REF! error. If ref_text refers to an external workbook, the other workbook must be open. If the source workbook is not open, INDIRECT returns a #REF! error.

• a1—This is a logical value that specifies what type of reference is contained in the cell ref_text. If a1 is TRUE or omitted, ref_text is interpreted as an A1-style reference. If a1 is FALSE, ref_text is interpreted as an R1C1-style reference.

Figure 9.21 is a monthly worksheet in a workbook that has 12 similar sheets. In each worksheet, the data headings are in row 6, and the total for the worksheet appears in cell D2. To build a summary sheet that points to D2 on the individual worksheets, you can concatenate the month name from column A with “!D2” to build a reference.

Using the HYPERLINK Function to Add Hyperlinks Quickly

Excel enables you to add a hyperlink by using the Excel interface. On the Insert tab, select the Hyperlink icon. Next, you specify text to appear in the cell and the underlying address. Building links in this way is easy, but it is tedious to build them one at a time. If you have hundreds of links to add, you can add them quickly by using the HYPERLINK function.

Syntax:

Click here to view code image

HYPERLINK(link_location,friendly_name)

The HYPERLINK function creates a shortcut that opens a document stored on your hard drive, a network server, or the Internet. This function takes the following arguments:

• link_location—This is the URL address on the Internet. It could also be a path, filename, location in the same workbook, and location in another file. For example, you could link to "[C:filesJan2018.xls]!Sheet1!A15". Note that link_location can be a text string enclosed in quotes or a cell that contains the link.

• friendly_name—This is the underlined text or numeric value that is displayed in the cell. friendly_name is displayed in blue and is underlined. If friendly_name is omitted, the cell displays the link_location value as the jump text. friendly_name can be a value, a text string, a name, or a cell that contains the jump text or value. If friendly_name returns an error (for example, #VALUE!), the cell displays the error instead of the jump text.

Figure 9.22 shows a list of web pages in column A. Column B contains the titles of those web pages. To quickly build a table of hyperlinks, you use =HYPERLINK(A2,B2) in cell C2 and copy the formula down the column. Unfortunately, you must keep columns A and B intact for the hyperlink to keep working. You can hide those columns, but there is no Paste Special option to convert the formula to values that will keep the hyperlink.

To keep only the hyperlinks, copy column C and paste to a blank Word document. Open a new workbook. Copy from Word and paste back to the new Excel document.

Alternatively, use ="#HYPERLINK("""&A2&""""&", "&""""&B2&""""&")" in C2. Copy down and paste special values. Use Find and Replace to change # to =.

Using the TRANSPOSE Function to Formulaically Turn Data

With many people using Excel in a company, there are bound to be different usage styles from person to person. Some people build their worksheets horizontally, and other people build their worksheets vertically. For example, in Figure 9.23, the monthly totals stretch horizontally across row 80. However, for some reason, you need these figures to be arranged going vertically down from cell B84.

The typical method is to copy C80:N80 and then use Home, Paste, Transpose. This copies a snapshot of the totals in row 80 to a column of data.

This is fine if you need only a snapshot of the totals. However, what if you want to see the totals continually updated in column B? Excel provides the TRANSPOSE function for such situations.

Because the function returns several answers, you need to use special care when entering the formula. Here’s how:

1. Note that C80:N80 contains 12 cells.

2. Select an identical number of cells starting in B84. Select B84:B95.

3. Even though you have 12 cells selected, type the formula, =TRANSPOSE(C80:N80), as if you had only one cell selected.

4. To tell Excel 2019 that this is a special type of formula called an array formula, hold down Ctrl+Shift while you press Enter. Pressing Ctrl+Shift+Enter is not required in Office 365 thanks to modern array formulas introduced in September 2018.

Excel shows the formula surrounded by curly braces in the formula bar. This is one single formula entered in 12 cells. Therefore, you cannot delete or change one cell in the range. If you want to change the formula, you need to delete all 12 cells in B84:B95 in a single command.

Syntax:

TRANSPOSE(array)

The TRANSPOSE function transposes a vertical range into a horizontal array or vice versa.

The argument array is an array or a range of cells on a worksheet that you want to transpose. The transposition of an array is accomplished by using the first row of the array as the first column of the new array, the second row of the array as the second column of the new array, and so on.

Using GETPIVOTDATA to Retrieve One Cell from a Pivot Table

You might turn to this book to find out how to use most of the Excel functions. However, for the GETPIVOTDATA function, you are likely to turn to this book to find out why the function is being automatically generated for you.

Suppose that you have a pivot table on a worksheet. You should click outside the pivot table. Next, you type an equal sign and then use the mouse to click one of the cells in the data area of the pivot table. Although you might expect this to generate a formula such as =E9, instead, Excel puts in the formula =GETPIVOTDATA("Sales",$B$5,"Customer","Astonishing Glass Company","Region","West"), as shown in Figure 9.24.

This function is annoying. As you copy the formula down to more rows, the function keeps retrieving sales to Astonishing Glass in the West region. By default, Excel is generating this function instead of a simple formula such as =E9. This happens whether you use the mouse or the arrow keys to specify the cell in the formula.

To avoid this behavior, you can enter the entire formula by manually typing it on the keyboard. Typing =E9 in a cell forces Excel to create a relative reference to cell E9. You are then free to copy the formula to other cells.

There is also a way to turn off this behavior permanently:

1. Select a cell inside an active pivot table.

2. The Pivot Table Tools tabs are displayed. Select the Analyze tab. From the PivotTable group, select the Options drop-down menu and then select the Generate GetPivotData icon. The behavior turns off.

3. Enter formulas by using the mouse, arrow keys, or keyboard without generating the GETPIVOTDATA function.

Microsoft made GETPIVOTDATA the default behavior because the function is pretty cool. Now that you have learned how to turn off the behavior, you might want to understand exactly how it works in case you ever need to use the function.

Syntax:

Click here to view code image

GETPIVOTDATA(data_field,pivot_table,field1,item1,field2,item2,...)

The GETPIVOTDATA function returns data stored in a pivot table report. You can use GETPIVOTDATA to retrieve summary data from a pivot table report, provided that the summary data is visible in the report. This function takes the following arguments:

• data_field—This is the name, enclosed in quotation marks, for the data field that contains the data you want to retrieve.

• pivot_table—This is a reference to any cell, range of cells, or named range of cells in a pivot table report. This information is used to determine which pivot table report contains the data you want to retrieve.

• field1, item1, field2, item2,...—These are 1 to 126 pairs of field names and item names that describe the data you want to retrieve. The pairs can be in any order. Fieldnames and names for items other than dates and numbers are enclosed in quotation marks. For OLAP pivot table reports, items can contain the source name of the dimension as well as the source name of the item.

Calculated fields or items and custom calculations are included in GETPIVOTDATA calculations.

• If pivot_table is a range that includes two or more pivot table reports, data is retrieved from whichever report was created in the range most recently.

• If the field and item arguments describe a single cell, the value of that cell is returned, regardless of whether it is a string, a number, an error, and so on.

• If an item contains a date, the value must be expressed as a serial number or populated by using the DATE function so that the value is retained if the spreadsheet is opened in a different locale. For example, an item referring to the date March 5, 2015, could be entered as 42068 or DATE(2015,3,5). Times can be entered as decimal values or by using the TIME function.

• If pivot_table is not a range in which a pivot table report is found, GETPIVOTDATA returns #REF!. If the arguments do not describe a visible field, or if they include a page field that is not displayed, GETPIVOTDATA returns #REF!.

Examples of Database Functions

If you were a data analyst in the 1980s and the early 1990s, you would have been enamored with the database functions. I used @DSUM every hour of my work life for many years. It was one of the most powerful weapons in any spreadsheet arsenal. Combined with a data table, the DSUM, DMIN, DMAX, and DAVERAGE functions got a serious workout when people performed data analysis in a spreadsheet.

Then, in 1993, Microsoft Excel added the pivot table to the Data menu in Excel. Pivot tables changed everything. Those powerful database functions seemed tired and worn out. Since that day in 1993, I had never used DSUM again until I created the example described in the following section. As far as I knew, the database functions had been living in a cave in South Carolina.

Maybe it is like the nostalgia of finding a box of photos of an old girlfriend, but I realized that the database functions are still pretty powerful. Customers whined enough to have Microsoft add AVERAGEIF to the COUNTIF and SUMIF arsenal. This was unnecessary: Customers could have done this easily by setting up a small criteria range and using DAVERAGE.

Inside OUT

The database functions would be far more popular if they did not require headings for the criteria range.

The DSUM is ultra-powerful to return all records in which a specific criteria range appears (such as the region is East). However, in real life, your manager is going to want to see the records where the region is Central and West instead of East. When you start to copy the DSUM function for other regions, you realize that you need an additional two cells to hold each criteria range.

If you wanted 100 rows of DSUM to return the sales for 100 employees, you would need 200 cells of criteria range. Arranging those cells vertically would make it difficult to point to the correct criteria range. In the figure here, a blend of horizontal criteria ranges, OFFSET, and MATCH provide a method, but it will be slow to calculate. This is why you don’t see DSUM functions everywhere.

As shown in the figure, modern functions, such as SUMIFS or FILTER, provide easier ways than DSUM to arrive at the results.

Eleven of the 12 database functions are similar. DSUM, DAVERAGE, DCOUNT, DCOUNTA, DMAX, DMIN, DPRODUCT, DSTDEV, DSTDEVP, DVAR, and DVARP all perform the equivalent operation of their non-D equivalents, but they allow for complex criteria to include records that meet certain criteria. See examples of each of these in Figure 9.25.

To save you the hassle of looking up the confusing few, DCOUNT counts numeric cells, and DCOUNTA counts nonblank cells. DSTDEV and DVAR calculate the standard deviation and variance of a sample of a population, respectively. DSTDEVP and DVARP calculate the standard deviation and variance of the entire population, respectively. The 12th database function, DGET, has the same arguments, but it acts a bit differently, as explained later in this chapter.

Using DSUM to Conditionally Sum Records from a Database

There are three arguments to every database function. It is very easy to get your first DSUM working. The criteria argument is the one that offers vast flexibility. The following section explains the syntax for DSUM. The syntax for the other 11 database functions is identical to this.

Syntax:

DSUM(database,field,criteria)

The DSUM function adds records from one field in a data set, provided that the records meet some criteria that you specify. The DSUM function takes the following arguments:

• database—This is the range of cells that make up the list or database, including the heading row. A database is a list of related data in which rows of related information are records, and columns of data are fields. In Figure 9.25, the database is the 5,002 rows of data located at A23:I5024.

• field—This indicates which column is used in the function. You have three options when specifying a field:

  • You can point to the cell with the fieldname, such as H23 for Revenue.

  • You can include the word Revenue as the field argument.

  • You can use the number 8 to indicate that Revenue is the eighth field in the database.

• criteria—This is the range of cells that contains the conditions specified. You can use any range for the criteria argument. The criteria range typically includes at least one column label and at least one cell below the column label for specifying a condition for the column. You can also use the computed criteria discussed in “Using the Miracle Version of a Criteria Range,” later in this chapter. Learning how to create powerful criteria ranges enables you to unlock the powerful potential of the database functions. Several examples are provided in the following sections.

  Note

  To conserve space, the remaining examples in the following sections show only the DSUM result. You can compare the various results to the $657,028 of revenue for the current example.

Creating a Simple Criteria Range for Database Functions

Although a criteria range needs only one field heading from the database, it is just as easy to copy the entire set of headings to a blank section of the worksheet. In Figure 9.25, for example, the headings in A17:I17, along with at least one additional row, create a criteria range.

In Figure 9.25, you see results of the 11 database functions for a simple criteria in which the customer is Best Paint Inc. Each formula specifies a database of $A$23:$I$5024. The field is H23, which is the heading for Revenue. The criteria range is A17:I18. In this example, the criteria range could have easily been A17:A18, but the A17:I18 form enables you to enter future criteria without specifying the criteria range again.

Using a Blank Criteria Range to Return All Records

This is a trivial example, but if the second row of the criteria range is completely blank, the database function returns the total of all rows in the data set. As shown in Figure 9.26, this is $256.6 million. This is equivalent to using the SUM function.

Using AND to Join Criteria

Many people who have used SUMIF in Excel 2003 and earlier are likely to want to know how to conditionally sum based on two conditions. This is simple to do with DSUM. If two criteria are placed on the same row of the criteria range, they are joined by an AND. In Figure 9.27, for example, the $123,275 is the sum of records in which the customer is Best Paint Inc. and the product is V937.

Using OR to Join Criteria

When two criteria are placed on separate rows of the criteria range, they are joined by an OR function. In Figure 9.28, the $2.1 million represents records for either Improved Radio Traders or Best Paint Inc.

You can use OR to join criteria from different fields. The criteria range in Figure 9.29 shows a Region value of West joined by an OR with a District value of Texas. This pulls a superset of all the West records plus just the Texas records, which happen to fall in the Central region.

Using Dates or Numbers as Criteria

The example in Figure 9.30 finds records with a date after 2017 and with revenue under $50,000. The criteria in F18 for the date could have used any of these formats:

>12/31/2017
>=1/1/2018
>31-Dec-2017

Using the Miracle Version of a Criteria Range

Using the criteria ranges in the preceding examples, you could easily build any complex criteria with multiple AND or OR operators.

However, this could get complex. Imagine if you wanted to pull all the records for five specific customers and five specific products. You would have to build a criteria range that is 26 rows tall. Basically, the first row is the headings for customer and product. The second row indicates that you want to see records for Customer1 and Product1. The third row indicates that you want to see records for Customer1 and Product2. The fourth row indicates that you want to see records for Customer1 and Product3. The seventh row indicates Customer2 and Product1. The 26th row indicates Customer5 and Product5.

If you need to pull the records for seven customers and seven products from five districts, your criteria range would grow to 246 rows tall and would probably never finish calculating.

There is a miraculous version of the criteria range that completely avoids this problem. Here’s how it works:

• The criteria range consists of a range that is two cells tall and one or more cells wide.

• Contrary to instructions in Excel Help, the top cell of the criteria range cannot contain a field heading. The top cell must be blank or contain anything that does not match the database header row. For example, you could use a heading of “Computed Criteria.”

• The second row in the criteria range can contain any formula that evaluates to TRUE or FALSE. This formula must point to cells in the first data row of the database. The formula can be as complex as you want provided the formula returns TRUE or FALSE. You can combine AND, OR, VLOOKUP, NOT, MATCH, and any other functions.

For a simple example, suppose you want to find records that match one of 15 customers. You copy the customers to K24:K38. In the second row of the criteria field, write the formula =NOT(ISNA( MATCH(A24,$K$24:$K$38,0))). This formula does a MATCH on the first customer in the database to see if it is in the list in K. The ISNA and NOT functions make sure that the criteria cell returns a TRUE when the customer is one of the 15 customers.

Very quickly and without complaint, Excel compares the 5,000 rows of your database with this complex formula, and the DSUM produces the correct value, as shown in Figure 9.31.

Using the DGET Function

The DGET function returns a single cell from a database. The problem is that this function is picky. If your criteria range matches zero records, DGET returns a #VALUE error. If your criteria range returns more than one row, DGET returns a #NUM! error.

To have DGET work, you need to write a criteria record that causes one and only one row to be evaluated as TRUE.

Syntax:

DGET(database,field,criteria)

The DGET function returns a single cell matching criteria from a data set.

Examples of Linked Data Types

Microsoft debuted linked data types in 2018. The first two data types are stock market data and geographic data.

Enter a list of states, cities, or countries in some cells. Select those cells and choose Geography from the Data Types gallery on the Data tab. As shown in Figure 9.32, a Map icon appears next to each value to tell you that the cells contain a geography data type.

If you click the map icon for a cell, a data card appears with information from Wikipedia about the state listed in the cell.

However, the more interesting feature is the new formulas that can point to the linked cell. In this example, Ohio is in H2. Go to any cell and type =H2 followed by a period. A list of fields appears. You can choose any of these fields to retrieve information about the geography. For example, =H2.Capital or =H2.Population. As shown in Figure 9.33, if the field name contains a space, wrap the name in square brackets: =H2.[Building Permits].

After entering the formula for the first state, you can copy the formula to pull similar data for each state. Figure 9.34 shows the largest city in each state.

If you type a different state name in a cell, the formulas will update. In Figure 9.35, Hawaii replaced Illinois. In a second or so, Chicago is replaced with Honolulu.

What if you mistype a name? You will see a brand-new error value called #FIELD!.

Given that this is a new feature, there are some odd limitations:

• The cell containing the Linked Data cannot be a formula. It must be a value. In Figure 9.35, once you have Columbus in I2, it would be nice to mark Columbus as geography and get the Columbus Ohio population with =I2.Population. However, this will not work.

• My computer always includes a custom list with the U.S. states from Alabama to Wyoming. This allows me to type Alabama and drag the fill handle to get the other state names. You must be careful to drag the states before marking Alabama as a Geography type. Once the cell is marked as Geography, the fill handle will not fill from custom lists.

• Your computer will have to be connected to the Internet to retrieve fields associated with a Linked Data Type.

Inside OUT

Microsoft Labs offer a free Fuzzy Lookup add-in for Excel.

Doing a VLOOKUP is impossible when you have names like “ABC Corp.” and “ABC Incorporated” and “A B C”. Back in Excel 2010, the research team at Microsoft Labs created a Fuzzy Lookup add-in for Excel. The technology was cool, but Microsoft ended up adding the functionality to SQL Server instead of to Excel.

However, the add-in still works in Excel 2019 and is free from Microsoft Labs. Download from https://www.microsoft.com/en-us/download/details.aspx?id=15011.

Set the tool to return the two best matches. In the figure below, there were two valid matches for Coca-Cola and a human will do a better job of knowing which one is your customer.