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: