XLOOKUP for Project Integration Management 🔍 2 Minutes!!! Budget Variance & Resource Variance EXCEL
In this video of #engineeringmanagementacademy #XLOOKUP is tutored as a #projectmanagementtutorial by #DrMehrdadArashpour
🔔 Subscribe: https://bit.ly/EngineeringManagementA...
❎ Excel workbook to follow along: https://bit.ly/XLOOKUP_Project_Integr...
💯 Other Excel Tutorials: https://bit.ly/PM_Excel_Academy
⌛ TIMESTAMPS
0:00 - Introduction to XLOOKUP Function in Excel
0:06 - Excel’s Dynamic Template, Finding Project Budget Variance using XLOOKUP Function, Analyzing Project Resource Variance using XLOOKUP, & Analyzing Charts & Fine Tuning Match Mode & Search Mode Arguments of the Function
0:23 - Step 1 (Finding Project Budget Variance using XLOOKUP Function)
1:06 - Step 2 (Analyzing Project Resource Variance using XLOOKUP)
1:36 - Step 3 (Tuning Match Mode & Search Mode Arguments of the Function)
2:06 - Concluding Remarks
❓ Request Next Tutorial Video: https://forms.gle/Frz9U9imCouofdoD8
Did you know XLOOKUP in Excel can be used to Integrate schedule, budget, & resource data in Project Management? In this video, We’ll create & share a dynamic Template in Excel with 3 easy steps: Finding Project Budget Variance using XLOOKUP Function, Analyzing Project Resource Variance using XLOOKUP, & Tuning Match Mode & Search Mode Arguments of the Function. You can follow along by getting the Excel workbook via the provided link in the description below. https://bit.ly/XLOOKUP_Project_Integr...
Step 1 is Finding Project Budget Variances using XLOOKUP
Our case study focuses on a construction project & We have five project tasks, each with duration, budget, and resource requirement. We also track actual budget & resource usage to identify any deviations from project plans. To calculate the variance between the planned budget & actual spending for Electrical work, we use XLOOKUP in Microsoft 365. First, we specify what to look up & task name is our option. Second, we determine where to look for it & the array of task names is the place. Remember to press F4 for absolute cell referencing. Third, we define the range to return & it is the planned budget array. Finally we deduct actual expenditure to find the budget variance for electrical works. We can duplicate the formula for other project activities.
Step 2 is Analyzing Project Resource Variances using XLOOKUP
To calculate the variance between the planned resource quantity & actual resource usage for Electrical work, we use the XLOOKUP function. First, we should specify to look up the task name. Second, we determine to look into the array of task names. Remember to press F4 for absolute cell referencing. Third, we define the range to return & it is the planned resource array. Finally, we deduct actual resource usage to find the resource variance for electrical works. We can duplicate the formula for other project activities of foundation or slab, framing, roofing & site preparation.
Step 3 is Tuning Match Mode & Search Mode Arguments
Fine-tuning how target values are found & matched in XLOOKUP is possible. For example, a match Mode of 0 refers to Exact Match to the target value & if no match is found, it returns an error. This is the default state in XLOOKUP. Match mode of 2 or Wildcard Match Allows for partial matches using wildcards such as * & ?. The Search Mode argument controls the direction in which Excel searches the data. For example, mode of 1 Searches from the first value to the last, which is the default.
And here it is, an easy & short implementation of XLOOKUP using only 3 simple steps in Microsoft Excel.
If you found this video useful, please consider subscribing. I hope to have your comments & see you in the next one.
