The spreadsheet below contains details of sales of packets of nuts for the week. The formula in column H is showing an error. What is the problem?

Question

BlackTom AI · Accepted Answer

To tackle this question, we first restate what is being asked and what each option proposes about the error in the H column formula.
Option a: The Type column in the Prices table should be the leftmost column. This is a plausible cause because VLOOKUP (which the formula appears to use) requires the lookup value to be in the first (leftmost) column of the table_array. If the Type column is not the first column, the formula cannot find the matching key (Cashews, Walnuts, etc.) and will return #N/A or an incorrect result.
Option b: Absolute references should have been used. While absolute references (e.g., $A$4) can help when copying formulas, they do not fix a fundamental lookup-direction issue. The error shown is more about locating the correct row based on the leftmost column, not about the need to fix relative versus absolute references.
Option c: The Prices table should be sorted in order. Sorting is not a prerequisite for VLOOKUP to work in this context. VLOOKUP can return results for unsorted data provided you use an exact-match lookup (the fourth argument FALSE). Since the option mentions sorting as the fix, it misidentifies the root cause.
Option d: An HLOOKUP function should have been used. The screenshot shows a VLOOKUP formula (it searches vertically in the first column). HLOOKUP would be appropriate if data were arranged horizontally across columns. Here, the data appears vertical with the item names in a column, so HLOOKUP would not be appropriate and would not fix the issue.

In summary, the core problem is the lookup key (the Type values like Cashews) not being in the leftmost column of the lookup table. Since VLOOKUP relies on the first column to locate the key, moving Type to the leftmost column aligns the data correctly and allows the formula to retrieve the intended price. The other options describe fixes or conditions that either do not address or misaddress the actual lookup mechanism or data layout shown in the sheet.

Dashboard O1_2025 T1 Exam Excel and Word

The spreadsheet below contains details of sales of packets of nuts for the week. The formula in column H is showing an error. What is the problem?

View Explanation

Log in for full answers

Similar Questions

True or false? VLOOKUP stands for Horizontal Lookup.

From the following four VLOOKUP formulas, select the ones that would produce the same output (i.e., result) when entered in a blank cell in the same worksheet.

The lookup_value argument in the VLOOKUP function is the value used to search on in the last column of the table array or list.

To return (display) excellent from the VLookup table shown, the col_index_num in the IF function arguments should be

Once you have done the following:1. Add the data table provided ('mean time until failure and the standard deviation by department') ,2. Place it in the VLOOKUP Function arguments under ‘Table_array’3. Place B2 as the Lookup_value. Which department will you be checking?

More Practical Tools for Students Powered by AI Study Helper

Homework AI Solver

Stylized AI Paper Writer

Plagiarism Checker Assistant

Citation AI Academic Writing Tool

In-Class Translation Assistant

AI Note Generator

AI Quiz Answers

Past Exam Questions from University Test Bank

Smart Practice Assistant

Adaptive Practice

Making Your Study Simpler