• Skip to main content
  • Skip to header right navigation
  • Skip to site footer

My Online Training Hub

Learn Dashboards, Excel, Power BI, Power Query, Power Pivot

  • Courses
  • Pricing
    • Free Courses
    • Power BI Course
    • Excel Power Query Course
    • Power Pivot and DAX Course
    • Excel Dashboard Course
    • Excel PivotTable Course – Quick Start
    • Advanced Excel Formulas Course
    • Excel Expert Advanced Excel Training
    • Excel Tables Course
    • Excel, Word, Outlook
    • Financial Modelling Course
    • Excel PivotTable Course
    • Excel for Customer Service Professionals
    • Multi-User Pricing
  • Resources
    • Free Downloads
    • Excel Functions Explained
    • Excel Formulas
    • Excel Add-ins
    • IF Function
      • Excel IF Statement Explained
      • Excel IF AND OR Functions
      • IF Formula Builder
    • Time & Dates in Excel
      • Excel Date & Time
      • Calculating Time in Excel
      • Excel Time Calculation Tricks
      • Excel Date and Time Formatting
    • Excel Keyboard Shortcuts
    • Excel Custom Number Format Guide
    • Pivot Tables Guide
    • VLOOKUP Guide
    • ALT Codes
    • Excel VBA & Macros
    • Excel User Forms
    • VBA String Functions
  • Members
    • Login
  • Blog
  • Excel Webinars
  • Excel Forum
    • Register as Forum Member

VLOOKUP in Power Query Using List Functions

You are here: Home / Power Query / VLOOKUP in Power Query Using List Functions
vlookup in power query using list functions
July 29, 2021 by Philip Treacy

If you've done lookups in Power Query to pull values from one table into another, you may have used a query merge to do this.

merging queries

Mynda has written previously on how to do an Exact Match in Power Query and an Approximate Match in Power Query

Here I'll be showing you how to use List Functions in Power Query to do both Exact and Approximate matches.

This approach requires a little more M coding but requires less steps and is my preferred method for doing lookups. But hey, I would think that, I'm a coder 🙂

Using List Functions

The key to doing this is remembering that table columns are lists, and as such you can use List Functions to manipulate the data in them.

Watch the Video

Subscribe YouTube

 

Download Sample Excel Workbook

Enter your email address below to download the sample workbook.

By submitting your email address you agree that we can email you our Excel newsletter.
Please enter a valid email address.

Excel Workbook. Note: This is a .xlsx file please ensure your browser doesn't change the file extension on download.

 

Exact Match

My sample data is a table of sales on different dates for different types of food and drink. This table is named data.

source data table 01

What I want to do is add a column showing the category for each item. These categories are stored in a separate lookup table named categories.

lookup table 01

I've loaded both tables into PQ and set categories to load as connection only because I don't need to create a new table from it.

So with both tables loaded into PQ, let's look at what we need to do.

The Food & Drink query needs to get the Category from the Categories table. By looking up the value in the Food & Drink[Product] column in the Categories[Product] column we can get the row number in Categories[Product] that matches.

Using that row number as an index on the Categories[Category] column will return the Category.

lookup product column

Let's do this step by step. Start by adding a Custom Column and calling it Category Index.

create custom column for category index

Using the List.PositionOf function, I want to look up in the Categories[Product] column, the value in this [Product] column.

The new column shows the position of each Product in the Categories table.

new category index column

Remember that Lists are indexed from 0, so Apple Juice is in Position 0 of the [Product] column in the Categories table.

Atlantic Salmon is position 65 etc.

Now that I have this index number I can use it to lookup the category. Create another Custom Column and call it Category.

reference to category column

What I need to do here is enter a reference to the value in the Categories[Category] column. This is done by specifying the Category Index value inside curly braces after the Table[Column] reference.

Click OK and this creates the Category column in the Food & Drink table.

new category column added to table

Let's do a litle tidying up, We don't need the category Index column now so delete it, and reorder the columns.

tidying up the table

Before I finish though, I can make things a little neater. I like reducing my steps and code where possible.

You can see in the Added Custom step that the code creates the Category Index column

edit first custom step

And the subsequent Added Custom1 step it uses the values from that Category Index column.

added a custom 1 step

You can take the code List.PositionOf( Categories[Product] , [Product] ) from the Added Custom step, and replace [Category Index] in the Added Custom1 step with it.

replace this code

with this code

This condenses the code from two steps into one and you end up with the same result.

As the Added Custom step is no longer needed, delete it. Also delete the Removed Columns step as all that is doing is deleting the Category Index column. But as the query is no longer creating that column, this step is not needed either. I don't actually need to create the Category Index column at all.

delete these steps

OK so the query is done, load to the sheet and that's our finished table.

Approximate Match

To do an approximate match I'm going to use this table of sales figures for various sales people and add a new column showing the bonus rate they'll get for those sales.

sales person source data

The idea being that you multiply the sales amount by the bonus rate to work out the bonus the sales person gets paid.

The bonus rates are stored in a separate table called excitingly, BonusRates.

bonus rates table

Make sure the table is sorted in ascending order on the Threshold value. It's important to note that the first row has $0 threshold and a rate of 0.

The reason for this will become clear as I explain how the lookup query works

The bonus rate is determined by the sales amount. If you sell $10,000 or more, but less than $20,000 then the rate is 0.1

If you sell $20,000 or more but less than $30,000 then the rate is 0.15, etc

Load both tables in Power Query and set the Bonus Rates lookup table to connection only.

What I need to do here is of course look up the sales amounts in the BonusRates table. But this time I'll use List.Select to create a list of all values in the BonusRates[Threshold] column less than or equal to the Sales Amount

The number of elements in this list will be used as the index to lookup up the bonus rate.

I'll use the first sales value of $17,606 as an example. There are 2 values in the Threshold column less than or equal to $17,606.

example bonus rate lookup

The List.Select function creates a list containing 0 and 10000. Then by counting the items in this list, I can use that number to return the 2nd item in the Rate column which is 0.1, or 10%

Let's look at the code. Open the Power Query editor and add a Custom Column called BonusRates and add this code

code for list select function

I'll explain what's going on here

list select code for lookup

The variable val contains the Sales value in the current row of this table. Remember that the code written here is run for every row in the Sales column.

List.Select creates a list containing all values in the BonusRates[Threshold] column, that are greater than or equal to the value in val.

The each _ is shorthand for the current item in the BonusRates[Threshold] column. It's saying compare val with each item in the BonusRates[Threshold] column

The result of this code is a column of lists.

column of lists

By clicking into a cell beside one of the lists, I can check what is in that list.

For the Sales value 9238 the BonusRates list contains just a 0, because the minimum sale amount to get a bonus is $10,000.

examine list items

If there wasn't a row in the lookup table with 0 threshold then the list for any sales value less than 10000 would be empty. When the query tried to lookup an index using an empty list it would generate an error. Having the 0 Threshold row means that the code will always create a non-empty list and avoid such errors.

Checking the list in row 4 for the Sales value 32455 shows that the list contains 4 items, because this sale amount crosses the $30,000 threshold.

check contents of another list

With this new column of lists I can now count the items in each list and lookup the bonus rate for the Sales amounts.

Add another custom column, call it Bonus Rate, with this code

bonus rate column lookup

What we need to do is lookup the value from the Rate column in the BonusRates table, and the number of items in the list created in the previous step is the index to that value.

Remember that lists are indexed from 0 so if list has 2 items then the 2nd item is at position 1. Therefore we have to subtract 1 from the count of items in the list

That's all I need so clicking OK and the new Bonus Rate column is created. I don't need the column containing the lists of bonus rates so I delete that leaving me with this final table.

final approximate match lookup table

I can now calculate the bonus amount in Power Query, or load this table to Excel and do it there.

vlookup in power query using list functions

More Vlookup Posts

VLOOKUP Multiple Values in Multiple Columns

VLOOKUP Multiple Values in Multiple Columns

Excel VLOOKUP Multiple Sheets

Excel VLOOKUP Multiple Sheets

Excel Factor 17 Lookup and Return Multiple Matches

Excel Factor 17 Lookup and Return Multiple Matches

Excel Factor 16 Dynamic Lookup

Excel Factor 16 Dynamic Lookup

VLOOKUP Multiple Criteria

VLOOKUP Multiple Criteria

Excel VLOOKUP Multiple Values

Excel VLOOKUP Multiple Values

Excel Wildcards in your SUMIF, COUNTIF and VLOOKUP

Excel Wildcards in your SUMIF, COUNTIF and VLOOKUP

Excel VLOOKUP with Dynamic Column Reference

Excel VLOOKUP with Dynamic Column Reference

Excel VLOOKUP with Dynamic Column Reference allows you to copy the formula across columns and have the column reference automatically update.
Excel VLOOKUP to the Left Using CHOOSE

Excel VLOOKUP to the Left Using CHOOSE

VLOOKUP to the left with this clever use of CHOOSE to trick Excel into looking up a column to the left.
hlookup function

Excel HLOOKUP Formulas Explained

How to use Excel HLOOKUP formula, including examples and workbook download.

More Power Query Posts

power query variables

Power Query Variables 3 Ways

Power Query Variables enable you to create parameters that can be used repeatedly and they’re easily updated as they’re stored in one place.
delete empty rows and columns using power query

Remove Blank Rows and Columns from Tables in Power Query

Delete blank rows and columns from tables using Power Query. Even rows/columns with spaces, empty strings or non-printing whitespace
extracting data from lists and records in power query

Extracting Data from Nested Lists and Records in Power Query

Learn how to extract data from lists and records in Power Query, including examples where these data structures are nested inside each other.
combine files with different column names in power query

Combine Files With Different Column Names in Power Query

Learn how to load data into Power Query when the column names in your data don't match up. Sampe files to download.
power query keyboard shortcuts

Power Query Keyboard Shortcuts to Save Time

Time saving keyboard shortcuts for Power Query that work in both Excel and Power BI. Download the free Shortcuts eBook
remove text between delimiters power query

Remove Text Between Delimiters – Power Query

Remove all occurrences of text between delimiters. There's no in-built Power Query function to do this, but this code does.
power query advanced editor tips

Tips for Using The Power Query Advanced Editor

Tips for using the Power Query Advanced Editor in Excel and Power BI. Watch the video to see these tips in action
pivot unknown variable number of rows to columns

Pivot an Unknown Number of Rows into Columns

How do you pivot rows to columns when you don't know how many rows you're dealing with? It's not as easy as you may think.
try otherwise power query iferror

IFERROR in Power Query Using TRY OTHERWISE

Using TRY..OTHERWISE in Power Query Replicates Excel's IFERROR So You Can Trap and Manage Errors In Your Queries.
easily compare multiple tables in power query using list functions

Easily Compare Multiple Tables in Power Query

Compare tables or lists in Power Query using List Functions. This method is great when dealing with 3 or more tables or lists.
Category: Power QueryTag: vlookup
Previous Post:Auto Refresh PivotTables
Next Post:Easily Compare Multiple Tables in Power Queryeasily compare multiple tables in power query using list functions

Reader Interactions

Comments

  1. DK

    September 13, 2022 at 6:09 am

    How list.PositionOf will work with multiple criteria?
    Thanks.

    Reply
    • Philip Treacy

      September 13, 2022 at 10:30 am

      Hi DK,

      List.Position of only gives the position of one thing in a list.

      If you want to search for the positions of multiple things you can use List.PositionOfAny

      Regards

      Phil

      Reply
  2. Joe

    August 11, 2022 at 3:34 am

    Tried to look for previous dates…

    Expression.Error: We cannot convert the value #date(2022, 1, 1) to type List.

    Reply
    • Mynda Treacy

      August 11, 2022 at 11:50 am

      Hi Joe, please post your question on our Excel forum where you can also upload a sample file and we can help you further.

      Reply
  3. Gayrat Vlasov

    July 11, 2022 at 7:29 pm

    Thank you so march!

    Earlier I’m use merging table, but you solution is better!

    I’m beginner and some confusion in let in operator, but I found it in doc:

    https://docs.microsoft.com/en-us/powerquery-m/m-spec-let

    Reply
    • Philip Treacy

      July 12, 2022 at 9:37 am

      You’re welcome Gayrat, glad this was useful for you.

      regards

      Phil

      Reply
  4. K.O.

    August 17, 2021 at 1:21 am

    Thanks for sharing. I really like this solution instead of merging and expanding tables to get lookup values. Much simpler and straightforward. However, when I applied this to project where the first table had 10,000+ rows and the second table had 1,000+ rows I had to kill Excel after the query didn’t return in twice the time that it took when using Merge/Expand.

    Reply
    • Philip Treacy

      August 17, 2021 at 7:55 pm

      Hi,

      Try using List.Buffer() on the lists before passing them into the other list functions. If you still have issues please start a topic on our forum and post your file so I can take a look.

      Regards

      Phil

      Reply
  5. فرشاد منصوریان

    August 8, 2021 at 3:20 am

    Hello . Please solve a problem in FiFo method with PowerQuery and put it on YouTube
    Thanks a lot

    Reply
  6. Danny

    August 2, 2021 at 8:48 pm

    Ingenious the approach for the approx. match…! thx
    Unfortunately M-language (also DAX language) solutions makes it rather complex for many users.

    Reply
    • Philip Treacy

      August 3, 2021 at 11:44 am

      Thanks Danny, if you take our Power Query or Power Pivot courses you’ll start to understand M and DAX.

      Regards

      Phil

      Reply

Leave a Reply Cancel reply

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

Current ye@r *

Leave this field empty

Sidebar

More results...

Shopping Cart

mynda treacy microsoft mvpHi, I'm Mynda Treacy and I run MOTH with my husband, Phil. Through our blog, webinars, YouTube channel and courses we hope we can help you learn Excel, Power Pivot and DAX, Power Query, Power BI, and Excel Dashboards.

Subscribe to Our Newsletter

Receive weekly tutorials on Excel, Power Query, Power Pivot, Power BI and More.

We respect your email privacy

Guides and Resources

  • Excel Keyboard Shortcuts
  • Excel Functions
  • Excel Formulas
  • Excel Custom Number Formatting
  • ALT Codes
  • Pivot Tables
  • VLOOKUP
  • VBA
  • Excel Userforms
  • Free Downloads

239 Excel Keyboard Shortcuts

Download Free PDF

Free Webinars

Excel Dashboards Webinar

Watch our free webinars and learn to create Interactive Dashboard Reports in Excel or Power BI

Click Here to Watch Now
  • Excel
  • Excel Charts
  • Excel Dashboard
  • Excel Formulas
  • Excel PivotTables
  • Excel Shortcuts
  • Excel VBA
  • General Tips
  • Online Training
  • Outlook
  • Power Apps
  • Power Automate
  • Power BI
  • Power Pivot
  • Power Query
 
  • About My Online Training Hub
  • Contact
  • Disclosure Statement
  • Frequently Asked Questions
  • Guarantee
  • Privacy Policy
  • Terms & Conditions
  • Testimonials
  • Become an Affiliate

Copyright © 2023 · My Online Training Hub · All Rights Reserved

Microsoft and the Microsoft Office logo are trademarks or registered trademarks of Microsoft Corporation in the United States and/or other countries. Product names, logos, brands, and other trademarks featured or referred to within this website are the property of their respective trademark holders.

Download A Free Copy of 100 Excel Tips & Tricks

excel tips and tricks ebook

We respect your privacy. We won’t spam you.

x