EXECL mapping table Easy to handle multi-account mapping

First, create a new workbook by execl, design the structure of the User mapping table, and import all employee names (Figure 1). Second, you will save the Account table exported from each application system database as a execl table and import it into the map workbook, and change the content uniformly, leaving only the names and accounts in the Account table in the two fields.

Next, we start with the VLOOKUP function syntax, and in the B3 table of the User Mapping table, enter the formula:

"=vlookup (User mapping Table!) A3,erp Account table! $A $: $B $11,2,0) ", meaning that, in the" ERP Account table "A3 to B11 area, find the" User mapping table "of the A3 data, such as matching, the" ERP Account table "in the second column (that is, column B) of the data, copy to the B3 table. Due to the "User mapping table in the formula!" A3 "takes relative values, so check the B3 column and drag the mouse down the data area to pull the fill formula."

According to the above formula, the other application system accounts are automatically populated in the User mapping table (Figure 2) in turn.

Finally, we only need to modify the "User Mapping table" slightly. Copy the user map data, and in the new worksheet, right-click, select "Paste", in the pop-up window, select "Value", OK then leave only the value, and remove all the formulas, and then press the shortcut key ctrl+f, pop-up "Find and replace" window, switch to the "Replace" tab, in the " Find what column, enter #n/a, and leave the "Replace with" column blank (Figure 3), click Replace All to get the final result (Figure 4).

　　Small tip:

Open a Execl table, click the Insert Function icon in the toolbar, and in the pop-up Insert Function window, the wizard guides you through the function that satisfies the requirement. In the Common Functions list box, select the VLOOKUP function to display the function immediately below the window: search for the element in the first column of the table that satisfies the condition, determine the row ordinal of the cell in the range you want to retrieve, and then return the value of the selected cell.