18.8 PwC and KPMG

M58. How would you use VLOOKUP or XLOOKUP to combine two datasets?

Reported for: PwC [S9] · also Mu Sigma [S7]

Identify a clean common key (e.g. Store ID), make sure it has the same type and no extra spaces in both tables, then add columns from the second table with =XLOOKUP([@[Store ID]],tblStores[Store ID],tblStores[City Manager],"Not found"). Check the count of "Not found". For many columns or recurring merges, use Power Query › Merge Queries (Left Outer).

M59. What is the difference between VLOOKUP and a PivotTable?

Reported for: KPMG [S10]

They do different jobs. VLOOKUP retrieves a related value for each row from another table (e.g. the city manager for each order). A PivotTable summarises many rows into totals, counts or averages by category (e.g. sales by city). Often you use VLOOKUP first to enrich the data and then a PivotTable to summarise it.

M60. Which formula do you use to find an average? How do you apply filters and sort data?

Reported for: KPMG [S10]

=AVERAGE(G2:G100); conditional averages with AVERAGEIF/AVERAGEIFS, e.g. average order value in Pune. Filters: Data › Filter (Ctrl + Shift + L) and choose criteria. Sort: Data › Sort for multi-level sorts, or the filter drop-down's Sort A to Z / Largest to Smallest. Always select the whole table so rows stay together.