XLOOKUP instead of VLOOKUP
Type an item code and XLOOKUP returns its name, price or stock from any column, even one on the left. No column counting, and adding a column won't break the formula.
FILTER for data that updates itself
FILTER returns a whole sub-table by condition, for example one salesperson's orders this month. When the source changes, the filtered table follows, no need to filter again.
SUMIFS to add up by several conditions
Add up revenue by region and by month with a single formula. It is the base of most weekly reports.
EOMONTH and NETWORKDAYS for dates
EOMONTH returns the last day of the month to close a reporting period. NETWORKDAYS counts working days, skipping weekends and holidays.
- =EOMONTH(A2;0) for the last day of the month
- =NETWORKDAYS(A2;B2;Holidays) for the number of working days
Try it with the sample file
The sample file has a table of 1,000 orders to try each function on. To go on to self-updating reports, see the Excel course below.
Comments
No comments yet.
