PART D — EXCEL QUICK REFERENCE
Excel proficiency is also tested. Key topics:
| Topic | Key Functions |
|---|---|
| Lookup Functions | VLOOKUP, HLOOKUP, INDEX-MATCH (preferred), XLOOKUP |
| Pivot Tables | Group data, summarize, filter, add calculated fields |
| Conditional Logic | IF, IFS, COUNTIF, SUMIF, AVERAGEIF |
| Text Functions | CONCATENATE/CONCAT, LEFT, RIGHT, MID, TRIM, PROPER |
| Date Functions | DATEDIF, YEAR, MONTH, EOMONTH, NETWORKDAYS |
| Data Validation | Dropdown lists, input restrictions |
| Conditional Formatting | Highlight cells based on rules, data bars, color scales |
| Power Query | Import, transform, and clean data from multiple sources |
Commonly Asked:​
Q: What is the difference between VLOOKUP and INDEX-MATCH?
| Aspect | VLOOKUP | INDEX-MATCH |
|---|---|---|
| Direction | Looks right only | Any direction |
| Flexibility | Fixed column index | Dynamic column reference |
| Performance | Slower on large data | Faster |
| Column insertion | Breaks if columns shift | Not affected |
=VLOOKUP(A2, Sheet2!A:D, 4, FALSE)
=INDEX(Sheet2!D:D, MATCH(A2, Sheet2!A:A, 0))