18.5 EY

M35. Which Excel functions do you use most often for data analysis?

Reported for: EY [S6] · also Mu Sigma [S7], Wipro [S14]

SUMIFS/COUNTIFS/AVERAGEIFS for conditional aggregation; XLOOKUP or INDEX-MATCH for lookups; IF/IFS with AND/OR for logic; TEXT, TRIM, LEFT/MID and TEXTBEFORE/TEXTAFTER for cleaning; DATE, EOMONTH and NETWORKDAYS for dates; UNIQUE/FILTER/SORT in Microsoft 365; IFERROR/IFNA; plus PivotTables. I would name the ones I actually used in my project and give one example.

M36. VLOOKUP vs INDEX-MATCH vs XLOOKUP – which is better and why?

Reported for: EY [S6] · also Amazon [S8]

XLOOKUP (Microsoft 365 / Excel 2021+) is the most flexible: separate lookup/return ranges, left lookups, exact match by default, built-in "not found" value, search from last, and multiple return columns. INDEX-MATCH has most of these advantages and works in every version. VLOOKUP is the simplest but most fragile. "Better" depends on who uses the file: XLOOKUP in Microsoft 365 teams, INDEX-MATCH when older versions must open the workbook.

M37. Show how you would analyse sales or profit trends with a PivotTable.

Reported for: EY [S6]

Order Date in Rows grouped by Months (and Years), Sales and Profit in Values, a second Sales field shown as % Difference From previous month, a City slicer, and a PivotChart line. For our data, grouping by day in October 2026 shows the Diwali week spike clearly.