Overcome Limitations of VLookup using Power Query in Excel
Power Query for Automation and Efficiency
This article is for all excel wizards, conversant with majority of excel features and functions but have not realised the potential of Power Query for data extraction and transformation automations and ease of use.
Power Query in itself is a powerful tool and needs mutiple articles to cover the features; however, this article will provide a brief and explain the use of Power Query to overcome the limitations of VLOOKUP, one of the most used function in excel. VLookup helps in deriving/sourcing the value from another table or sheet arranged vertically for lookup values in the current table. As an excel user, the first activity before performing any kind of analysis is — Data preparation and VLookup helps in sourcing the relevant data for analysis.
However, this function has its own challenges and limitations which can be easily resolved with Power Query or in other words Advanced VLookup can be performed using Power Query. But, lets start with the basics first!!
What is Power Query and how it can help
Power Query is a data preparation and transformation engine inbuilt in Microsoft Excel.With Power Query, the data extracion/sourcing and transformation tasks can be done with increased efficiency and can be automated without using any codes or functions, with just few clicks. Now, coming back to the issue in hand which is ‘how to overcome limitations of Vlookup’.
Vlookup Limitations
Scenario 1: Vlookup can only look to the right-In the illustration below, there are two tables -Lookup Table(yellow) and Employee Salary Table(Red). In the Employee_Salary_Table, we need to obtain Name (First Name and Last Name) of the employee from the Lookup table on the basis of lookup values in Employee ID column in Employee_Salary_Table. This seems quite easy; however, VLookup can not be used in this case as the Name values (First Name and Last name) are stored in the columns to the left of Employee ID in the Lookup Table. This may see bizarre but Vlookup function can only be used to obtain values stored in the columns which are to the right of the matching columns in the Lookup Table.
Scenario 2: VLookup finds the first match in the Lookup table and return the corresponding value- In the image below, if details of the employees (First name, Last name and Employee ID) working in the departments specified in the Department Table is derived using VLookup functio,we would only obtain details of the employee first matched on the basis of Department. Vlookup would fail to provide details of all employees working in the department specified in the Department column in Department Table.
Scenario 3: VLookup can only look for one Lookup Column not mutliple lookup column combinations
In the above illustration, if we use VLookup to retrive value of Employee ID in the Employee_Table from the Lookup Table on the basis of First name, the employee ID for the employee with the same first name (E.g: Carol in the Employee_Table would be retruned as A001) would be returned as same; irrespective of their different last name. To get the correct result in this case, the lookup value should be a combination of First and Last name. However VLookup can only match values on the basis of one lookup column. We can use a workaround by concatinating the First and Last name but that would increase the complexity in large data and increase of processing time.
Power Query to resolve these limitations:
Power Query can easily overcome these challenges of VLookup and offers much more flexibility in data sourcing/extraction. I would describe more Power Query features in my future articles but lets focus here on performing VLookup using Power Query!
VLookup is essentially merging of data from two Tables/Sheets. Let’s see it through an example and take the scenario 1 where First name and Last name are to derived in the Employee_Salary_Table from the Lookup table. The process of merging these two tables would be as follows:
- As Power Query works with Queries , first we need to convert both these tables into queries one by one.
- We can start with any of the table. Here, I have started with Employee_Salary_Table, select it and click on Data tab> In the ‘Get & Transform Data section’, select the ‘From Table/Range’ icon as shown in the image below. This will open up the ‘Power Query Editor window’ and the table would automatically be converted to a Query. Here, any transformation required to the table data can also be done. Click Close & Load(Choose Create Connection Only).
3. Repeat the process with Lookup Table. Now we have two queries for these two tables (visible in the Queries & Connections Pane). From the ‘Get Data’, dropdown > select Combine Queries>Merge.
4. Selecting ‘Merge’ would open up the Merge window as shown below. Choose the Queries one by one from the dropdown. It is better to select the table in which data to be sourced /derived as first. Choose the join kind, which by default would be Left Outer Join. Also select the column to be matched in both the tables . Here, I have selected ‘Employee ID’ in both the tables.
5. The joins work like in SQL and can be chosen as per your requirement from the available options — Left Outer Join, Right Outer Join, Full Outer Join, Inner Join, Left Anti and Right Anti.
6. On clicking ‘OK’ , the tables would be merged. Select the icon highlighed in the image below in the Lookup_Table header to select the columns that needs to be derived/sourced from Lookup Table. In our case, it is First name and Last Name. We can choose as many columns as required from the Lookup Table without the need to apply VLookup function multiple times.
7. After Column selection in the Lookup_table, click Close& Load. The merged data will be opened in another sheet in the current workbook.
8. The process explained here is applied for merging of tables in Power Query Editor and can be done as per the user requirement.
Resolving other two limitations
Case 2: To resolve the issue in scenario 2 in the VLookup limitation section, follow the same process in Power Query with only one change. Select Join Kind as ‘Right Outer’ which means select all values from the second table — ‘Lookup table’ in our case.
Case 3: To resolve the issue in scenario 3 in the VLookup limitation section, follow the same process by selecting the columns — First Name and Last name in both the tables. The result would be generated by comparing the combination of values in First name and Last name column.
Conclusion:
Power Query is a powerful and amazing tool that can automate the common data sourcing and transformation tasks in excel with increased efficiency and singificantly reduce the time spent on data preparation. It is also important to mention here, that all applied sourcing and transformation steps are recorded in Power Query and can be refreshed for new data in the tables. It is my recommendation to all excel users to learn and use Power Query as it is a powerful tool and significantly easy to learn.
Therefore, continue on your learning path and realise the true power of Power Query!!
