Ravindra BagaleCourses & study guides मराठी Track your progress

18. Interview Questions Asked in MNC Interviews

18.5 EY

By Last updated:

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.

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.

Ravindra Bagale's Tip

To the question "Which is better – VLOOKUP, INDEX-MATCH or XLOOKUP?", many students just say "XLOOKUP" and stop. Always answer with context: XLOOKUP if the team is on Microsoft 365, INDEX-MATCH if there are older versions. Interviewers like exactly this kind of balanced answer.

Loading practice tasks...

  1. Total nights (Easy): Add up Nights in D2:D11.
  2. Average rate per night (Easy): Average of E2:E11, rounded to 2 decimals.
  3. Rows in Standard (Easy): How many rows have Room type "Standard"?
  4. Largest nights (Easy): The largest value in D2:D11.
  5. Nights for Standard (Medium): Total of D2:D11 where B is "Standard".
  6. Standard in Bristol (Medium): Total of D2:D11 where B is "Standard" and C is "Bristol".
  7. Best in Standard (Medium): Largest E2:E11 value where B is "Standard".
  8. Value of row 2 (Medium): D2 times E2.
  9. Look up Aarav Shah (Medium): The Nights for "Aarav Shah" (use a lookup on column A).
  10. Total value (Hard): Sum of D times E for all rows.
  11. Share of Standard (Hard): Share of D2:D11 that is "Standard", rounded to 3 decimals.
  12. Rank of row 2 (Hard): Rank of D2 among D2:D11 (largest = 1).
  13. Above average? (Hard): "Above" if E2 is above the average of E2:E11, else "Below".

See all practice tasks in the Task Hub →

Worked example — ey

Why

Priya Deshmukh in Pune and Liam Murphy abroad both need a clear, repeatable way to use ey on a BrightLedger Finance sheet without breaking totals.

What

Ey is the Excel idea this page teaches. We will walk one tiny example end to end.

How

  1. Open a blank sheet. In A1 type a short label for ey.
  2. Enter a small made-up value for Priya Deshmukh using € where money appears.
  3. Add a second value for Liam Murphy using € so you see mixed currencies stay numeric.
  4. Apply the method from this lesson step by step (menu or formula as shown above).
  5. Check the result still works inside SUM or a simple check cell.

Formula / action

Follow the formula or ribbon steps shown in this lesson (keep values numeric).
Result: The demo cell shows the expected result for BrightLedger Finance's sample row.
Meaning: Once ey is correct on one row, copy the same pattern down the column.

Quick revision cards

Three standalone cards. Each one explains the idea with its own example. Scan the QR to open this lesson.

Revision card 1: What is Ey?
Card 1: What is Ey? · Download PNG
Revision card 2: Tiny example — Ey
Card 2: Tiny example — Ey · Download PNG
Revision card 3: Checklist — Ey
Card 3: Checklist — Ey · Download PNG