If you want to automate your work in Excel, the combination of formulas VLOOKUP + IF + TODAY () is an extremely useful tool. With these 3 key formulas you can present all events that are important in a work process (e.g., costs monitoring, tasks tracking, delivery dates…).
The IF function is one of the most favoured functions in Excel. It allows you to make logical comparisons between a value and what you expect. You can use this function to define correct results in the reports or for conditional formatting, where we want to control if all required data are properly entered.
The TODAY formula returns the current date, updated continuously when a worksheet is changed or opened.
You can combine IF and TODAY to define the status of the tasks that are already completed on a specific date or still work in progress. With this combination of formulas, you can control the remaining days tor for work to be done or incurred cost.
VLOOKUP stands for ‘Vertical Lookup’. You can use VLOOKUP when you need to find things in a table or a range by row. For example, look up a price of a product by the ID number, or find an employee name based on their employee ID. Whit this function you can automate data entering in reports and avoid manual work and typos.
There are several additional formulas for data optimization, e.g. you can combine data from multiple cells into a single cell using the Ampersand symbol (&) or the CONCAT function. You could use these two formulas to add a prefix to multiple (or all) entries across a column. For example, the combination of these functions is useful for presenting KPIs on dashboards.
The usage of these important formulas is easy and for your work with Excel you would probably use them daily. You just need to learn, know how the functions work.
You can learn more about most useful functions in Projects 1 › course.