Siddique ssfetggn Syed

Excel is a powerful spreadsheet used by most people working in data analysis. The increase of volume of data and development user-friendly tools is an opportunity of improvement of Excel reports by mixing them with another tool or language.

As working for a financial reporting department I faced the need to boost our reporting tools. A simple way to start working with a new language is to translate what we use to do in excel, in another language. “How can I pivot this?”, “How can I vlookup that ?”.

In this article I will share with you how you can make VLOOKUP in 5 different languages: VBA, python, SQL, DAX (Power BI), M (Power query). it will simple tips but if you want to get more detailed article don’t forget to follow me!

VLOOKUP is a function used in Excel to look up data in a table or range organised vertically.

We will use a simple example with a tab with items and their relative prices. In cell E2 we can put an item and with the VLOOKUP formula in F2 we get its relative price.

Visual Basic for Application (VBA) is an implementation of Microsoft Visual Basic integrated into Microsoft Office applications.

On your code, you can set up a variable to store the result of the VLOOKUP result using the statement “WorksheetFunction.vlookup”. It works exactly as the function itself. Then you can set the value of the cell F2 as the variable.

Additional tip: This case is really simple as we want to get the result of the VLOOKUP into a single cell. But what if we want to loop through all the lines of a table? For example, if we want to put in cells E3 and E4 other items and get in cells F3 and F4 the price using VLOOKUP?

If we loop the same formula we will get the as result the same price as the VLOOKUP will always be done on the same item (always the cell “E2”). In this case, I suggest using the statement “FormulaR1C1”, where R and C stand for Row and Column. In this statement instead of putting the exact cell where is the value we are looking for, we will say “The cell where is the value we look for is situated at X rows and Y columns from here”.

Then we can loop like this:

SQL ( Structured Query Language) or sequel, is a standard language for storing, manipulating and retrieving data in databases. It is one of the common upgrade done by companies that face limits with Excel. Usually the first reaction is to negotiate some budget in order to store the data into a database and use SQL to “speak” with this database and organise, manipulate the data. The language is also highly appreciable. Close to a natural language, you don’t feel coding when typing simple SQL request.

Let’s build a table1 that will match with the RANGE(“A1:B4) of our Excel. In table2 we will mention just the item for which we are looking for the price.

Then we will use a “Right.Join” request to show the item of table 2 with its relative price of table 1.

This request means that we select the item and its price in table1 if the item is present in table 2.

Additional tip: Right join return all rows from the right table and the matched rows from the left table. based on your database you can switch with Left join (in the opposite way), inner join if the match is done in both table, full join if we want all rows that match in one table.

Python is an interpreted, high level language with a generic purpose. It is used in a large range of application, including data analysis. We can present python by saying “For all application its libraries”. And for data, without surprise, we will use the famous Pandas.

The logic used by pandas to do a VLOOKUP is close to the one of SQL. We will create two data frames, one (df1) for the items and their relative prices (cells A1 to B4), one (df2) for the items we want to look up the price. And then we will create a third data frame (Resultdf) that will join the item of df1 with prices of df2.

Additional tip: There are tones of way to interact directly with Excel in python. If you are interested into that; you can check another article specially for this topic that you can check here: https://medium.com/analytics-vidhya/spice-up-your-excel-with-python-621c9693c027

M is the powerful language behind the tool power query. Even if you are working on the query editor, every single step will be written in M. M stands for Data Mashup or Data Modeling. I highly recommend to have a look at this language, we can do so much more than just using the graphical interface.

The request behind VLOOKUP in M is a join method between the table where we have the items and the prices, and the table where we have the item we look for the price.

To create both tables you just have to select the range of cells and use short cut ctrl+T (or command T in mac).

In our sample “Table 1” is my table with items and prices, “Table2” is the one with the lookup.

The method Table.NestedJoin will join the rows of table 1 (with the prices) with the row of table 2; based on the equality of the key element of each table. The final result will be on the new Table 2 with additional column. We add an optional argument to specify that we do a right join.

Additional tip: This join method can be done directly in the query editor. Even if I recommend to learn M as a all features available these last month in the query editor were already possible with M since years, I will share here how do to it using the query editor:

First we have 2 tables:

Then in “Data” we select from Table/Range after selecting Table 1.

Automatically the query editor will appear with Table1 and the M query of the creation of this table.

Then we can load the result and do the same with Table2. Once done, in Excel we have a query panel with both table. Just select one of them with a click right and click on Merge.

A merge editor will appear, where you will put Table 1vas the first table, table 2 as second table and select both column with items. Then click on OK.

And here is the result.

DAX stands for Data Analysis Expressions. More than a language, it is a library of functions and operators that can be used to build formulas in Power BI and Power Pivot.

Some functions are already built by default, and thankfully LOOKUP is one of them!

The logical is close the VLOOKUP excel formula but with argument set in a different order.

result_columnName: is the name of the column where is the result we are looking for;search_columnName: is the name of the column where is the value on with we do the lookup;search_value: value on which we do the lookup.

In our case the function would be: