At a point, it becomes essential to code categorical variables or sometimes numeric variables to represent rankings or to provide additional insight. VLOOKUP can combine tables’ common fields: the tables with the coded field used to reference the table to be coded.
Different data sets can be combined in excel with the VLOOKUP technique by first identifying a common field. A common field should be identified, as seen in table 1 and table 2, which in this case is ‘Location code.’ In a different field, type in the VLOOKUP command, select from table 3 and the reference field table 2, separated by a comma, and enter the column number of the reference field separated by a comma and type false.
Note that the available tables will be allocated to different tabs as shown:
Table 1.
The reference tables (location information)
Table 2.
Table to be merged to include codes from table 2.
Using table 2, we selected from them and ran the code below to derive table 3 with the new field in the ‘H’ column. We have expanded the table and coded the Location Code in table 2 using reference table 1.
The example shown shows the code used.
Table 3.
The final merged table.
Figure 4.
Graphical representation with additional insight in terms of location Size.
Reference:
Arians, John David (2022) Using Power BI with Excel[See Exercise Files] https://www.linkedin.com/learning/using-power-bi-with-excel-14635015/combining-data-in-excel?autoSkip=true&autoplay=true&resume=false&u=99504138