Excel - Master LOOKUP in Python with step-by-step tutorial | Excel Python XLOOKUP - Episode 2619

Microsoft Excel Tutorial: VLOOKUP in Python for Excel

How to do a VLOOKUP or XLOOKUP in Python.

Welcome to another video on Excel Python XLOOKUP! In this video, we will be exploring how to do an XLOOKUP or a VLOOKUP in Python, and it's remarkably easy. We will start with a simple VLOOKUP and then move on to something more complex, like not specifying the key field. We will also cover what to do when a customer is missing from the lookup table, which is essentially the IFERROR equivalent. Additionally, we will learn how to limit which fields are returned, something that we don't have to worry about with VLOOKUP. We will also address common issues such as mismatched headings and duplicated customers.

Before we dive into the code, let's talk about the comment indicator. The hash symbol allows you to comment a line and explain what the next line is. In this video, I have used this to demonstrate a problem and then provide the solution. This allows me to show the problem and then quickly switch to the solution without having to run the code again. Now, let's get started!

But what happens if a customer is missing from the lookup table? We will use the .fillna method to get rid of the #HUM! error and replace it with blanks. Additionally, we will learn how to remove unnecessary fields from the lookup table using the .drop method. And for those of you who are used to Excel's VLOOKUP, we will address the issue of duplicated customers and how to handle them in Python.

Thank you for watching this video on Excel Python XLOOKUP. If you enjoyed it, please don't forget to Like, Subscribe, and Ring the Bell. And as always, feel free to leave any questions or comments down below. See you next time for another netcast with MrExcel!

Along the way, you will see:
101: Doing a VLOOKUP
Not specifying the key field!
What if a customer is missing from lookup table? How to IFERROR()
Limiting which fields are returned.
What if headings don't match?
What if a customer is duplicated?
Lookup on two fields.

Table of Contents
(0:00) Python lookup overview
(0:25) Comment indicator in Python
(2:36) Leaving off the On field
(2:50) IFERROR when customer missing with .FillNA
(3:23) Limiting lookup table to needed fields
(4:04) Headings don't match left_on and right_on!
(4:50) .drop method to remove a column from Python
(5:12) If duplicate in lookup table
(6:00) drop_duplicates to remove duplicates
(7:06) What is the right way to show duplicates
(7:29) Lookup on two fields
(8:50) Building blocks in Python
(9:28) Nancy Faust

[FRANKWHITE]

We're opening a new chapter with this PY for excel ❤🎉😊 what a time to be alive 🙌🙏👏

[HusseinKorish]

The most important thing now ... is that "this excel file is a treasure for study and learn" .... thanks Mr.Excel

[ricos]

Love it. I am so used to this sort of thing from writing in SQL (and adodb in Excel VBA). I've seen your example of duplicates on many an occasion. There are two options, the first is to do exactly as you did and the second is to leave the duplicate in place with the incorrect value, but with conditional formatting to highlight.
Because what you've done isn't an xlookup, it's a table join and they're not the same thing (unfortunately). A table join requires a unique key, and your dataset didn't have one. That's because your second table isn't a list of customers (or accounts), it's a list of customers by region. The two are not interchangeable.

[DimEarly]

Very nice!

These days, if the lookup table has dupes I'd be tempted to use something like FILTER and TEXTJOIN to return both / all matches (so e.g. Access Analytic would should up as Consulting, Retail). Of course, you'll need to work with that column a little differently later if you do...

If you wanted to do the same thing in Python, I'm fairly sure (haven't tried yet!) that you could group the lookup table by customer and return all sectors so you could then use it for a merge.

[benrogers]

Joins always duplicate rows if multiple matches, be it SQL, PowerQuery, or Python. The expectation being that you know the granularity and modify it before joining whenever needed, like you did. I will sometimes do a group by instead of remove duplicates. This lets me aggregate (textjoin/concat) the adjacent text column rather than choose the first or last arbitrarily. Not always the right option, but an option. In PowerQuery this would look something like:
=Table.Group(Table, "Customer", {
{"Sectors", each Text.Combine(List.Sort([Sector]), ", "), type text},
{"Count", each Table.RowCount(_), Int64.Type}
})

[bassemyoussef]

I would use a unique lookup value/table. This may save a lot of work.
Also, I would write the name of the lookup column "customer" twice; (good practice) to ring a bill if someone changes the column's title.
One option is to create a function to replace extra spaces or to replace undesired titles with the standard header titles).
Another option is to try to create dynamic header names (create a function that determines the header and uses it), but it would be tedious, complicated, and have its own weak points.

[manojsrikanth]

I am eagerly waiting for the python option in my excel. Thank you..

[vebe]

Hi great videо. Have a question-can df table be defined from several sheets, having same column titles but located in two or more sheets? Thanks

[evilangel]

can python in excel do looping like
for..
do ...while
while ..
etc..

[AlThePal]

is it worth it to do this type of x lookup? I don't see the benefits

[iankr]

Hmmm... Python is completely new to me, and I'm slightly mystified about what the point of it is in this situation. If you're avoiding using Excel lookup functions (and Power Query) here, it seems to me that you're just using the Excel worksheet structure as a 'shell' for 'doing' Python? Which begs the question, where else can you 'do' Python?