Vlookup

Website: https://support.microsoft.com/en-us/office/vlookup-function-0bbc8083-26fe-4963-8ab8-93a18ad188a1

A Vlookup Function says:
=VLOOKUP(What you want to look up, where you want to look for it, the column number in the range containing the value to return, return an Approximate or Exact match – indicated as 1/TRUE, or 0/FALSE)

My most common usage of vlookup was using it to ensure I did not pay invoices from last week. As a Finance & Accounting Specialist at Innocean, I created the Wienerschnitzel payment request every Wednesday. We use our system Mediaocean to pull the AP import file report in csv format that will show all invoices that were reconciled. However, since the report is pulled from Wednesday of last week to Wednesday today, then it is possible it will generate the same invoices from last week's payment request. 

In the example below, I used the vlookup and FALSE exact match to find out that invoice 897 and 105 was already paid in last week's payment request. Therefore, I removed invoice 897 and 105 from the csv file. From there, I templated onto a formulated AP import template, and imported into Advantage, our system we used to host all our open, unpaid, and paid vendor and client invoices.



Comments

Post a Comment

Popular posts from this blog

GETPIVOTDATA

Image
GETPIVOTDATA The GETPIVOTDATA function returns visible data from a PivotTable. Honestly, at its simplest, just use "=" and then click on a cell and then the whole syntax will autopopulate. That is how I returned the Billing Gross, Net, and Commission YTD. I think is helpful to use a data validation list. First, use "=" and reference a cell and then just change the item to reference the cell that has data validation. For example, there when you change the media type on the green cell F12, you can get its unbilled gross, net, and commission. Note that the data validation list dropdown may not appear unless you open the workbook on your Excel Desktop and open the workbook in Edit mode.  
Read more

Ranking of UC Schools from Top 110 Ranked National Universities of 2024

Link to GitHub:  https://github.com/sgng-hub/2024unviersityranking Link to Dataset: https://www.kaggle.com/datasets/raymondtoo/the-world-university-rankings-2016-2024 Dataset Official Source: https://www.timeshighereducation.com/world-university-rankings/2024/world-ranking This project is about writing SQL query to find the ranking of top 110 world universities that are part of the University of California System I included a link to Looker Studio visualization of the query     
Read more

Xlookup specific value in the middle of a string

EXCELIFSUN Credits goes to the YouTuber EXCELSIFUN. I was familiar with idea of using MID and XLOOKUP.  However, I liked how he pointed out by adding a zero, it can convert the text after using the MID function to a number so that we can use the xlookup. Also, if you highlight all the cells, then enter the formula in the first cell of the highlighted array, then Ctrl + Enter, you can autofill the formula throughout that whole array.
Read more