18. Interview Questions Asked in MNC Interviews
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.
Ravindra Bagale's Tip
Amazon's questions include topics like Solver, weighted average and Power Query, and many students have never even opened Solver. Run the Solver example from Module 11 yourself at least once – you should be able to say these three words confidently: objective, variable cells and constraints.
Ravindra Bagale's Tip – मराठी
Amazon च्या प्रश्नात Solver, weighted average आणि Power Query सारखे topics येतात, आणि बऱ्याच students नी Solver कधी उघडलेलाच नसतो. एकदा तरी Module 11 चा Solver example स्वतः चालवा – objective, variable cells आणि constraints हे तीन शब्द confidently बोलता आले पाहिजेत.
Ravindra Bagale's Tip – हिंदी
Amazon के सवालों में Solver, weighted average और Power Query जैसे topics आते हैं, और बहुत से students ने Solver कभी खोला ही नहीं होता. कम से कम एक बार Module 11 का Solver example खुद चलाओ – objective, variable cells और constraints ये तीन शब्द confidently बोलने आने चाहिए.
Loading practice tasks...
- Total quantity (Easy): Add up the "Quantity" column.
- Average unit price (Easy): Average of the Unit price ($) column, rounded to 2 decimals.
- Rows in Website (Easy): How many rows have Channel "Website"?
- Largest quantity (Easy): The largest value in the "Quantity" column.
- Quantity for Website (Medium): Total of the Quantity column where Channel is "Website".
- Website in Seattle (Medium): Total of the Quantity column where Channel is "Website" and City is "Seattle".
- Best in Website (Medium): Largest value in the "Unit price ($)" column where "Channel" is "Website".
- Value of row 2 (Medium): Multiply the first-row Quantity by the first-row Unit price ($).
- Look up Liam O'Brien (Medium): The Quantity for "Liam O'Brien" (use a lookup on the "Name" column).
- Total value (Hard): Sum of "Quantity" times "Unit price ($)" for all rows.
- Share of Website (Hard): Share of the "Quantity" column that is "Website", rounded to 3 decimals.
- How many rows are above the average "Unit price ($)" (Hard): Count the rows where "Unit price ($)" is above the average "Unit price ($)".
- Above average? (Hard): "Above" if the "Unit price ($)" value in the first row is above the average of the "Unit price ($)" column, else "Below".
Worked example — amazon
Why
Aarti Kulkarni in Pune and Carlos Rivera abroad both need a clear, repeatable way to use amazon on a Maple Bakery Co sheet without breaking totals.
What
Amazon is the Excel idea this page teaches. We will walk one tiny example end to end.
How
- Open a blank sheet. In A1 type a short label for amazon.
- Enter a small made-up value for Aarti Kulkarni using £ where money appears.
- Add a second value for Carlos Rivera using $ so you see mixed currencies stay numeric.
- Apply the method from this lesson step by step (menu or formula as shown above).
- 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).Meaning: Once amazon 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.
