Excel Advanced - Advanced Excel Tasks
Advanced Excel Tasks
Combine arrays, lookups, logical tests, workbook links, consolidation and navigation tools to solve multi-step spreadsheet tasks reliably.
Learning outcomes
Download the practice workbook
The embedded data includes five transactions, product and employee lookup tables, a horizontal quarterly-rate table and four regional sheets with identical layouts.
1. Array calculations
Calculate total Revenue in one formula
On Transactions, Quantity is in D2:D6 and the retrieved Unit Price will be in G2:G6.
=SUM(D2:D6*G2:G6)- Microsoft 365: confirm with Enter.
- Older Excel: confirm with Ctrl+Shift+Enter; Excel may display braces around the formula.
- Do not type the braces manually.
Expected total: RM6,366.00.
Use the compatible SUMPRODUCT alternative
=SUMPRODUCT(D2:D6,G2:G6)SUMPRODUCT handles corresponding arrays without Ctrl+Shift+Enter and is often the safest mixed-version option.
2. Exact-match vertical lookups
Retrieve Unit Price with VLOOKUP
In G2, search the Product ID from B2 in the Products table and return its fourth column.
=IFERROR(VLOOKUP(B2,Products!$A$2:$E$6,4,FALSE),"Not found")- Lock the lookup table with absolute references.
- Use FALSE for exact matching.
- Fill the formula down to G6.
Retrieved prices: RM125, RM720, RM480, RM820 and RM838.
Retrieve employee Rating
=IFERROR(VLOOKUP(C2,Employees!$A$2:$E$6,4,FALSE),0)The lookup returns the fourth column—Rating—from the Employees table. IFERROR prevents a missing ID from breaking downstream logic, but investigate unexpected zeros.
3. Modern and flexible lookups
Return Product Name with INDEX/MATCH
=IFERROR(INDEX(Products!$B$2:$B$6,MATCH(B2,Products!$A$2:$A$6,0)),"Not found")MATCH finds the row position; INDEX returns the value from the Product Name column. The return column can sit left or right of the lookup column.
Microsoft 365 XLOOKUP equivalent
=XLOOKUP(B2,Products!$A$2:$A$6,Products!$B$2:$B$6,"Not found")XLOOKUP uses separate lookup and return arrays and defaults to exact matching.
Use HLOOKUP for a horizontal table
On Bonus Matrix, quarter names run across the first row and bonus rates across the second.
=HLOOKUP("Q3",'Bonus Matrix'!$A$1:$E$2,2,FALSE)Expected Q3 rate: 4%. HLOOKUP searches across the top row; VLOOKUP searches down the first column.
4. Nested logical classification
Classify each transaction
After calculating Revenue in H and retrieving Rating in I, enter:
=IF(AND(H2>=1000,I2>=4),"Priority",IF(OR(H2>=750,I2>=4.5),"Review","Standard"))Excel tests Priority first. Only records that fail the first test proceed to Review, then Standard.
| Result | Rule |
|---|---|
| Priority | Revenue ≥ RM1,000 AND Rating ≥ 4 |
| Review | Revenue ≥ RM750 OR Rating ≥ 4.5 |
| Standard | Neither rule is satisfied |
5. Links and three-dimensional references
Link cells across sheets and workbooks
- Same workbook:
='Central'!B5 - Another workbook:
='[Quarterly Sales.xlsx]Summary'!$B$5 - Adjacent sheets with the same layout:
=SUM(Central:Eastern!B5)
Create a separate quarterly workbook, save it, then link its total into the working file. Close and reopen both files to inspect the external-link behaviour.
6. Consolidate regional sheets
Consolidate by position or category
- Confirm Central, Northern, Southern and Eastern use identical Month, Sales and Profit layouts.
- Create a sheet named Consolidated.
- Choose Data → Consolidate and select Sum.
- Add each regional range.
- For identical positions, leave labels unticked; for category consolidation, use Top row and Left column labels.
- Optionally create links to source data.
Expected totals: Sales RM175,400 and Profit RM52,400.
Scalable Microsoft 365 alternative
Power Query Append is usually more maintainable when files or periods grow. Import each table, append the queries and load the combined result.
7. Hyperlinks and collaboration
Create navigation and choose a sharing method
- On Start Here, insert a link to cell A1 on Transactions.
- Add links to Products, Employees and Consolidated.
- Add a web link to
https://support.microsoft.com/excel. - Edit display text so each link describes its destination.
- Compare legacy Shared Workbook/Track Changes with modern OneDrive or SharePoint co-authoring, Show Changes and Version History.
Troubleshooting
Check the key, spaces, data type, locked range and FALSE exact-match argument.
Use VLOOKUP or INDEX/MATCH in an older Excel version.
Legacy Excel may require Ctrl+Shift+Enter; use SUMPRODUCT for broad compatibility.
Confirm source ranges, labels and layouts before consolidating.
Knowledge check
1. Why use FALSE in VLOOKUP?
FALSE requires an exact key match. Approximate matching can return the wrong record when the first column is not properly sorted.
2. Why can INDEX/MATCH be more flexible?
The lookup and return ranges are independent, so the return column need not be to the right of the key.
3. What does IFERROR solve—and what can it hide?
It replaces errors with a controlled result, but may conceal bad keys or damaged ranges if used without checking the cause.
4. When is consolidation by position appropriate?
When every source sheet has the same layout and corresponding values occupy the same cells.
Completion checklist
- Five Revenue values: RM750 / RM2,160 / RM960 / RM820 / RM1,676
- Total Revenue: RM6,366
- Q3 bonus rate: 4%
- Consolidated Sales: RM175,400
- Consolidated Profit: RM52,400
- Navigation links: Transactions, Products, Employees and Consolidated
Next lesson: rearrange the same dataset into analytical views using PivotTables, slicers and timelines.