Excel 2019 Bible

Chapter 14: Using Formulas for Matching and Lookups

14

3. Change the Name box to Married.


4. Click OK.


5. Select the range B15:E22.


6. Choose Define Name from the Formulas tab on the Ribbon.


7. Change the Name box to Single.


8. Click OK.

FIGURE 14.10

The New Name dialog box

There is a data validation drop-down in cell D25 in Figure 14.9. The drop-down box contains

the terms Married and Single, which are identical to the names that we just created. We’ll

be using the value in D25 to determine in which table we’ll look, so the values must be

identical.

The revised formula for computing the withholding follows:

=VLOOKUP(D29,INDIRECT(D25),3,TRUE)+(D29-VLOOKUP(D29,INDIRECT(D25),1,

TRUE))*VLOOKUP(D29,INDIRECT(D25),4,TRUE)

The formula in this example is strikingly similar to the one shown in Figure 14.7. The only

difference is that an INDIRECT function is used in place of the table’s location.

INDIRECT takes an argument named ref_text. The ref_text argument is a text representa-

tion of a cell reference or a named range. In Figure 14.9, cell D25 contains the text Single.

INDIRECT attempts to convert that into a cell or range reference. If ref_text is not a valid

range reference, as in our case, INDIRECT checks the named ranges to see whether there

is a match. Had we not already created a range named Single, INDIRECT would return the

#REF! error.

INDIRECT has a second optional argument named a1. The a1 argument is TRUE if ref_text

is in the A1 style of cell references and FALSE if ref_text is in the R1C1 style of cell