VLOOKUP in Excel and Power Query PBI

Welcome to Excel Jet Consult. In this tutorial, we want to look at how to perform vertical lookup in Excel and Power Query PBI. Let’s get started

VLOOKUP in Excel

VLOOKUP function looks for a value in the leftmost column of a table, and then returns a value in the same row from a column specified.

VLOOKUP Syntax                             =VLOOKUP(lookup_value, table_array, col_index_num, [range_lookup])

In the caption below, we have a dataset. We want to look up to the table on the right and pull the corresponding country in column F into column C downwards based on the lookup value in column A downwards. Essentially, we want to return the countries each of the Sales Executive is operating from.

Note that the table array is formatted in Excel table (optional) and renamed lookup_tble

In cell C4, execute the formula: =VLOOKUP(A4,lookup_tbl,2,FALSE)

A4 is the lookup value; lokup_tbl is the table array; 2 is the column index number we want to pull corresponding value from based on the lookup value; FALSE returns exact range_lookup

From the caption above, we can see that Cynthia Harris is operating from Germany which exact in the row 7 of the table array. It worked perfectly



We want to perform similar operation in Power Query. Although, VLOOKUP function is unavailable in Power Query. The first thing we need to do is format the two tables in Excel Table and provide descriptive table name which I have done. Next, I imported the two table as separate queries into the Power Query for transformation. See the captions below

Click on the Source Data Query

In the Merge Queries drop-down of the Combine group of the Home tab, click on Merge Queries As New

In the Merge dialogue box, select Sales Executive column

Select lookup_tbl from the drop-down and select Sales Executive column from the second query. See the caption below

From the above caption, we can see that the selection matches 49 of 49 rows from the first table which is the fact table.

Click OK.

From the caption below, we have a new table column added that we need to expand

To expand, click on the double-upward pointed arrows

Unchecked use original column name as prefix and Sales Executive. See the caption below

Click OK

When we compare the result below to the Excel result, we discovered that they are exactly the same


VLOOKUP is one of the widely used functions in Excel after SUM and AVERAGE functions. However, the VLOOKUP function is not available in Power Query. However, with Merge Queries as New, we can look up a value from the dimension table and pull across to the fact table on a row by row context. I hope you enjoyed this tutorial.

Posted in Blog and tagged , , .

Leave a Reply

Your email address will not be published. Required fields are marked *