Openpyxl - VLOOKUP Formula in an Entire Column in Excel Workbooks with Python | Data Automation

this video demonstrates how to apply the vlookup  function to an Excel workbook using the python   Library openpixel in this document you can see  five rows of food each color coded with the color   written out next to it in column D we see that  it contains the same data but it's organized in   a different way so let's start by looking at the  vlookup function and its arguments by entering it   directly into an Excel cell here is the vlookup  function in Excel with all of the arguments that   it accepts the lookup value that you are trying to  find in the reference table is the first argument   in our case for this cell it would be orange the  second argument is the table wherein that value   exists with the leftmost column of the table being  the value we are searching for that value that we   identified as the lookup value the table in this  case is columns A and B the third argument is the   column number within that reference table you want  to return and the final argument is if you would   like to return an exact match or not to find the  value for orange I would enter the lookup value   of D1 in the table A to B and I'm looking for the  second column index number 2 representing column   B and I want to return an exact match and that  is denoted with false as you can see right there and here we can see we have successfully returned  the value Orange now I will go ahead and delete   the contents of column e and I will use a for  Loop to show you how you can apply the vlookup   function to an entire column using python here I  start off by bringing in the workbook as WB I then   select the worksheet as ws and I will be saving  the document and the work in this cell below it I   will start off by bringing over the function that  we just saw but inputting it in a way with python   and here we can see that I am assigning cell  E1 of the document the same vlookup function   that I just demonstrated in Excel I'll show you  that this works by executing the cell quickly   saving it and opening up the  workbook to show the work and here you can see orange is now back in cell E1   let me go ahead and delete it again so that we  can apply that same function to the entire column   for this let me bring over another piece  of code that I have written in advance   this for Loop is using an iteration through a  range of numbers I start with the number one and   use the max row attribute of the worksheet  to designate when the for Loop should stop   I do add 1 to this last number as you can see  here because the range function does not include   the second number within the for Loop so if I  want to loop from 1 to 5 which represents the   range of cells that I do have I have to state  that we need to Loop in a Range one through six   using the max row attribute ensures that the  loop exits once there are no more rows of data   each iterated variable for this for Loop is  called rho I am selecting each row as they   iterate up one by one and making sure that my Vu  lookup value is also increasing by the same amount   so that the row and column e is going  to match the lookup value in column d   the rest of the vlookup function Remains  the Same as you can see in the above cell   let me go ahead and execute this I'll  save the document and check the workbook and here you can see that the vlookup  function has been accurately applied to   each row returning the correct color for each food   so as you can see it's actually quite easy to  apply the vlookup function using python all you   have to do is write out the vlookup function and  make sure that the vlookup value is searching for   the correct value as you either iterate through  a loop or place the function in a specific cell   thank you for watching please let me know in  the comments if this was a helpful video for   you let me know what other features of open  Pixel or python in general you would like to   see reviewed and do hit that subscribe button so  that you can know when the next video comes out

Channel: Data Automation

Views: 2,149

Keywords: Descript

Id: nAOoxlD45js

Length: 5min 3sec (303 seconds)

Published: Mon Apr 24 2023