How does Excel set input data automatically into numbers?

Work, in order to statistical convenience, the need to transform the data layer simple numbers, more convenient to count, for example, I first enter the city name, and then output is the corresponding number, today to share this method.

1, create a new worksheet, set up a data source

2, such as the figure, such as in the D1 cell input "Beijing", will be in the E1 cell output "1" in the E1 cell input formula E1=vlookup (d1,a:b,2,0)

3, double-click Cell E1, you can see the formula specified area, "D1" for the studio needs to enter the name of the city, "a:b" refers to the reference data source, popular speaking is the comparison table, enter A column of data and then B column automatically generate numbers, the formula of the other without changes, and then enter the car can be

4, the following figure, enter the Drop-down cell fill formula, red box input "Nanjing", the corresponding is not a numerical value, is #n/a, because the green box in the data source is not the corresponding figures in Nanjing

5, now increase the data source, specify Nanjing corresponding value of 999, as shown, conversion success

6, the above is in a work table, if in two worksheets, if the data source in the Sheet1, data entry is in the Sheet2, Sheet2 worksheet formula using the following figure, red box, and the second step in the comparison of the formula, more than a "sheet1!", This represents the data source referencing the Sheet1.

7, red box refers to the data source worksheet, if the data source worksheet named "Control", the red box is "Control!", note, "!" The symbol must be the input method for the English state, or wrong formula

Note: Excel tables can simplify productivity and later share more usage!.