Excel 2019 Bible

Chapter 25: Importing and Cleaning Data

2525

FIGURE 25.18

Using a lookup table to assign a region for a state

The two-column lookup table is in the range D2:E52. The formula in cell B2, which was cop-

ied to the cells below, is as follows:

=VLOOKUP(A2,$D$2:$E$52,2,FALSE)

A workbook that contains the examples in this section is available on this book’s website at www

.wiley.com/go/excel2019bible. The filename is classifying data.xlsx.

A side benefit is that the VLOOKUP function will return #N/A if an exact match is not found—a good way to spot
misspelled states, in this example. Using FALSE as the last argument in the function indicates that an exact match
is required.

Joining columns

To combine data in two more columns, you can use the CONCAT function in a formula. For

example, the following formula combines the contents of cells A1, B1, and C1:

=CONCAT(A1:C1)