ACCA Applied Knowledge · Management Accounting · Spreadsheets
A product table has product codes in A2:A5 (P1, P2, P3, P4) and unit costs in B2:B5 ($4, $7, $9, $12). Which formula returns the unit cost for product code P3 held in cell D1, ensuring an exact match is required?
The correct formula is =VLOOKUP(D1,A2:B5,2,FALSE). It looks down the first column for P3, returns the entry from the second column (the unit cost), and the FALSE argument requires an exact match rather than an approximate one.
- A=VLOOKUP(D1,A2:B5,2,TRUE)
- B=VLOOKUP(D1,A2:B5,1,FALSE)
- C=VLOOKUP(D1,A2:B5,2,FALSE)Correct
- D=HLOOKUP(D1,A2:B5,2,FALSE)
Explanation
VLOOKUP searches the first column of the range for the code and returns a value from the column number given, here column 2 holding the costs. FALSE forces an exact match. Column 1 would return the code itself, TRUE gives an approximate match, and HLOOKUP searches across rows rather than down columns.
Did you get it right without looking?
One question tells you little. A timed set on Spreadsheets shows your real accuracy, how long you take and where you lose marks.
More Spreadsheets questions
- Cells A1 to A5 contain the values 10, 20, 30, 40 and 50. Cell B1 contains the formula =SUMIF(A1:A5,">25") and cell B2 contains =AVERAGEIF(A1…
- A company records sales of 40, 55 and 75 units for three products in a table and wants a component bar chart in a spreadsheet showing each p…
- A cost accountant has monthly sales units in cells B2:B13. She wants the average monthly sales volume, ignoring any blank cells. Which formu…
- In a spreadsheet, the finance team of Harbor plc has a table of cost data with 20 rows. They want to display only the rows where the cost ce…
- A budget model has quarterly sales in B2:E2 (Q1 to Q4: 200, 240, 260, 300 units) and a unit price in cell G1 ($50). In B3 a formula =B2*G1 i…
- Which of the following is the main advantage of using a spreadsheet model for a cash budget rather than preparing it manually?