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
VLOOKUP IN POWER QUERY PBI
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
CONCLUSION
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.