Lookup in regular Excel function
Table of Contents
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:
- Ability to return the value of the column to the left of your lookup array (left-most column of the table array)
- 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

Screenshot of the Excel File
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
Limitation
As far as I know, this option cannot do multple lookup keys. Correct me in the comments below if I’m wrong.
Leave a Reply