18.7 Amazon

M50. Use VLOOKUP or INDEX-MATCH to pull information from another sheet.

Reported for: Amazon [S8]

=VLOOKUP(F2,Stores!$A$2:$F$100,5,FALSE) or =INDEX(Stores!$E$2:$E$100,MATCH(F2,Stores!$A$2:$A$100,0)). With Tables the reference is simply tblStores[City Manager], which works from any sheet. Lock ranges with $ before copying.

M51. Write a formula for a weighted average when the weights are in another column.

Reported for: Amazon [S8]

=SUMPRODUCT(Values,Weights)/SUM(Weights), e.g. delivery minutes weighted by quantity =SUMPRODUCT(H2:H11,F2:F11)/SUM(F2:F11), which gives 11.88 on our mini dataset versus a simple average that ignores order size.

M52. How would you use Solver to optimise a product mix for maximum profit?

Reported for: Amazon [S8]

Enable the Solver add-in. Set the objective cell (total profit = SUMPRODUCT of quantities and unit profit) to Max, choose the quantity cells as variable cells, add constraints (capacity, budget, minimum quantities, integer), pick Simplex LP for linear problems and Solve. In Module 11 the van-loading example gives 35 milk and 25 fruit crates for ₹12,800 profit.

M53. Create and format PivotTables, group by dates or ranges, and chart the results.

Reported for: Amazon [S8] · also MathCo [S15]

Right-click a date in the pivot › Group › Months/Quarters/Years (or Days with a number of days = 7 for weeks); right-click a numeric row field › Group › Starting at 0, By 100 for amount bands. Apply Tabular layout and number formats, then PivotTable Analyze › PivotChart.

M54. From raw data, show the top 5 products by sales and chart them.

Reported for: Amazon [S8]

Pivot with Product in Rows and Sum of Amount in Values, Value Filters › Top 10 › 5 Items, sort descending, then insert a bar PivotChart (bars read well with long product names).

M55. Find total revenue for a specific month and region.

Reported for: Amazon [S8]

=SUMIFS(tblOrders[Amount],tblOrders[City],"Nashik",tblOrders[Order Date],">="&DATE(2026,3,1),tblOrders[Order Date],"<="&EOMONTH(DATE(2026,3,1),0)). Referencing input cells instead of hard-coded values makes it reusable.

M56. How would you highlight outliers or duplicates?

Reported for: Amazon [S8]

Duplicates: Conditional Formatting › Duplicate Values or a COUNTIF rule. Outliers: a formula rule using the IQR limits, e.g. =OR($G2<$L$1,$G2>$L$2) where L1/L2 hold Q1 − 1.5×IQR and Q3 + 1.5×IQR, or Top/Bottom rules for a quick look. In our cleaning example BLK-4004 (₹12,990) is flagged for verification.

M57. Can you explain macro basics or simple recorded macros for efficiency?

Reported for: Amazon [S8]

Developer › Record Macro › perform the steps › Stop Recording; run it with a shortcut, a button or Alt + F8; save as .xlsm. Use relative references when the macro should work from the active cell. For robust automation, edit the recorded code to remove Select statements and find the last row dynamically.