Index & Match - Reporting Billing

Index & Match

"To summarize, INDEX gets a value at a given location in a range of cells based on numeric position. When the range is one-dimensional, you only need to supply a row number. When the range is two-dimensional, you'll need to supply both the row and column numbers."

"The MATCH function is designed for one purpose: find the position of an item in a range"

Now, using index and match together, I can find the billing numbers for particular client and month. Feel free to utilize the data validated dropdown to change client and month to find its respective billing numbers.

Comments

Post a Comment

Popular posts from this blog

Xlookup

Website: https://support.microsoft.com/en-us/office/xlookup-function-b7fd680e-6d10-43e6-84f9-88eae8bf5929 The XLOOKUP function searches a range or an array, and then returns the item corresponding to the first match it finds. If no match exists, then XLOOKUP can return the closest (approximate) match. =XLOOKUP(lookup_value, lookup_array, return_array, [if_not_found], [match_mode], [search_mode]) As a Finance & Accounting Specialist, I used the xlookup for each month's UC Davis Health billing. There are different ATB ("Authorization to Buy") numbers, estimate numbers, media types, and ATB names. Our goal is to to see how much we are billing for each ATB number to make sure we are in budget and to check if we are billing enough for each ATB, which we can be summarized using a pivot table of the full report data. Also, if "N/A" returns, then I know there is either a new ATB that I would request more info from the media team. However, as mentioned in its definit...
Read more

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