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

Подписаться 155 тыс.

Просмотров 8 тыс.

50% 1

Microsoft Excel Tutorial: VLOOKUP in Python for Excel
How to do a VLOOKUP or XLOOKUP in Python.
To download the examples in this workbook: www.mrexcel.com/youtube/h6__f...
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!
We have a large data set on the left-hand side and a small lookup table on the right. Our goal is to get the sector field from the lookup table and add it to the data set. To do this, we will use the pd.merge function, which is similar to a database join. We will specify the left table, the lookup table, and the column that is in common between them. We can also choose the join type, which is similar to Power Query. In this example, we will use Left join. The result will be our original fields, along with the new field from the lookup table - Sector. And the best part? We don't even have to specify the "on" parameter because the fields have the same name.
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.
But what if we have two keys? In this case, we will use the left_on and right_on parameters to specify the fields that are in common between the two tables. And just like that, we have successfully joined two tables in Python using the pd.merge function. This is a great building block for more complex tasks, and I hope to eventually build a beautiful mansion of code using these building blocks.
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!
Buy Bill Jelen's latest Excel book: www.mrexcel.com/products/latest/
You can help my channel by clicking Like or commenting below: www.mrexcel.com/like-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
(1:00) VLOOKUP 101 in Python using pd.merge
(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:11) Final thoughts on pd.merge
(8:50) Building blocks in Python
(9:28) Nancy Faust
Join the MrExcel Message Board discussion about this video at www.mrexcel.com/board/threads...

Опубликовано:

14 июл 2024

Ссылка:

Скачать:

Готовим ссылку...

Добавить в:

Мой плейлист

Посмотреть позже

Комментарии : 19

@FRANKWHITE1996 10 месяцев назад

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

@oztellingtales5879 10 месяцев назад

🥳🥳🥳

@HusseinKorish 10 месяцев назад

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

@ricos1497 10 месяцев назад

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.

@ricos1497 10 месяцев назад

To add to the above. It's often very difficult to explain this data issue to an accountant, manager or other. They'll argue that it's perfectly possible that a customer can belong to multiple regions, and that's likely correct. That being the case, their customer revenue report, with added region will always be wrong unless you're splitting the revenue by region too. In one interpretation, you'll be assigning revenue twice, another is that you're assigning total revenue from one customer to a single region, thus overstating the region's revenue. The answer is to go back to the person asking for the report and ask what they want to happen. Perhaps the correct answer will be that your customer by region table is recalculated to be the customer by assigned region, where the assigned region is based on sales volumes, geographical location or other such measure. It also might be that you extract the data in your left hand table with more granular detail, that allows you to create a join on a different field that splits revenue by customer and region. Certainly just picking one is arbitrary (alphabetical, probably), but may also be sufficient.

@benrogers9092 10 месяцев назад

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} })

@DimEarly 10 месяцев назад

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.

@SyedMuzammilMahasanShahi 10 месяцев назад

Thanks Bill for this amazing video.

@bassemyoussef9358 10 месяцев назад

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.

@davidferrick 10 месяцев назад

Nice example. Thanks!

@manojsrikanth8663 10 месяцев назад

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

@suatmozgur 10 месяцев назад

It just arrived in my Excel today. Update your Office, perhaps your Office is in this week's update batch.

@oztellingtales5879 10 месяцев назад

Interesting 🤔

@vebe7109 10 месяцев назад

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

@evilangel4136 10 месяцев назад

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

@suatmozgur 10 месяцев назад

Yes, something like: nums = [1,2,3,4] total = 0 for i in nums: total+= i total Obviously, not necessary to calculate the sum of a column by using a loop, but just as a sample to loop through rows and calculate the total of the Value columns in a DataFrame (range): df = xl("A1:B4", headers=True) total = 0 for i in df.index: total+= df["Value"][i] total

@AlThePal78 10 месяцев назад

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

@wizardofaus8473 10 месяцев назад

All is revealed at the end of the video

@iankr 10 месяцев назад

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?