How does an Excel table fill in the native place according to the ID number?

ID Card number is unique, the new second-generation ID card number 18, the first 6 places of origin. Here we say, use VLOOKUP to fill in the place of origin in bulk

1, first download all the local origin of the first 6-digit code set, this search in Baidu under all the downloads. There is no link here, there is no addition.

2. Then we copy the data from the table we just downloaded to the table where you need to add your native place "Sheet2". "Sheet1" for the need to fill in native place.

3, now both tables are in a table. Now let's talk about how to use the formula. Put the mouse to the "birthplace" of the column, click "Insert Function."

4, find "VLOOKUP" function, click OK. Will jump out of the function parameters, we do not fill out here, nothing, directly click OK, and then there are pop-up prompts, also click OK. So there's a function "VLOOKUP" inside our text box.

5. Finally, we will enter the formula to find the corresponding native place. "=vlookup (Left (c3,6), sheet2! $A $: $B $6457,2,0)"

Here to explain. What it means.

Left (c3,6) is the number that represents the first 6 digits of the 3rd line in column C. For example, the "Bai Yue" ID number in our table is now on line 3rd of column C.

Sheet2! is the value of "Sheet2".

$A $ $B $6457 is the cell that represents the value of Sheet2. $ is absolute.

2, is the number of columns that represent the value to be taken.

0, is to say "absolute match".

When you have finished typing, just hit enter.

6, now we have entered a good formula. The next person's place of origin is much simpler. Just put the mouse in just the input good formula text box in the lower right corner of the mouse into a black cross, double-click the left mouse button. So all the people in this place are coming out of their native place.

Note: The two tables must be in the same Excel file, enter the formula in that column, the cell format must be regular.