How to do Lookup in Excel and Power Query

Lookup in regular Excel function

The XLOOKUP function has been available to Office 365 and Excel 2021. For compatibility purposes with my workbook’s potential users, I shy away from using this function. Instead, I use the combination of INDEX and MATCH.

Why not VLOOKUP?

I’m not sure if there are any hardware efficiency benefit from using INDEX and MATCH over VLOOKUP or HLOOKUP. But I use INDEX and MATCH primarily for two reasons:

1. Ability to return the value of the column to the left of your lookup array (left-most column of the table array)
2. Never worry about adding a column between the returned column and the lookup array. Remember, you indicate the column index number being the returned column from your table array? What if you’ve not touched your file for a long time, return to it and inserting a column in a table, forgetting that you’ve used that table as a table array as a lookup somewhere in your worksheet? You’ll certainly break your VLOOKUP formula.

How to use these formulas

Sorry, but you have to look somewhere else for the usage of these formulas.

Lookup in Power Query

Power Query Lookup Sample File

To better demonstrate the functions, it’s better if we refer to a sample file:

Download Excel File

Power Query Lookup Sample File.xlsx

Screenshot

Merge and Expand

Table.NestedJoin/ExpandTableColumn

This process requires two steps (you may combine multiple steps into one, but they’re essentially the same two steps): Merge (Left Outer Table.NestedJoin) and Expand (Table.ExpandTableColumn).

M Code (Merge and Expand)

let
   Source = Excel.CurrentWorkbook(){[Name="Transactions"]}[Content],
   #"Changed Type" = Table.TransformColumnTypes(Source,{{"Date", type date}, {"Customer", type text}, {"Product", type text}, {"Quantity", Int64.Type}}),
   #"Merged Queries" = Table.NestedJoin(#"Changed Type", {"Product"}, Products, {"Product"}, "Products", JoinKind.LeftOuter),
   #"Expanded Products" = Table.ExpandTableColumn(#"Merged Queries", "Products", {"Price"}, {"Price"})
in
   #"Expanded Products"

 

One Step: Record.Field

Rationale

While the Merge and Expand option above is just 1 step longer than this, this option may come in handy especially if you’re dealing with longer M codes (i.e., larger number of steps). There are times you have to merge and expand a number of times in the same query. Just one step for each lookup will be useful as you audit your codes when you troubleshoot or introduce further enhancements.

M Code (Record.Field)

let
   Source = Excel.CurrentWorkbook(){[Name="Transactions"]}[Content],
   #"Changed Type" = Table.TransformColumnTypes(Source,{{"Date", type date}, {"Customer", type text}, {"Product", type text}, {"Quantity", Int64.Type}}),
   Added_Price = Table.AddColumn(#"Changed Type", "Price", each Record.Field(Products{[Product=[Product]]},"Price"), type number)
in
   Added_Price

The magic really happens with this formula:
Record.Field(Products{[Product=[Product]]},"Price")

Watch it on Youtube

Watch this ExcelIsFun on Youtube at 02:07 Timestamp

Alternative to Record.Field

Instead of Record.Field(Products{[Product=[Product]]},"Price"), we can use the shorter Products{[Product=[Product]]}[Price], so it reads:

let
    Source = Excel.CurrentWorkbook(){[Name="Transactions"]}[Content],
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"Date", type date}, {"Customer", type text}, {"Product", type text}, {"Quantity", Int64.Type}}),
    Added_Price = Table.AddColumn(#"Changed Type", "Price", each Products{[Product=[Product]]}[Price], type number)
in
    Added_Price

Limitation

As far as I know, this option cannot do multple lookup keys. Correct me in the comments below if I’m wrong.