Question

Using the worksheet below, find out the error and its reason for the given ‘VLOOKUP’ syntax: % Table for worksheet \[ \begin{array}{|c|c|c|c|c|} S. No. & Product & Q1 Sales (\( \text{\rupee} \)) & Q2 Sales (\( \text{\rupee} \)) & Total Half Yearly Sales (\( \text{\rupee} \))
1 & A & 10000 & 15000 & 25000
2 & B & 40000 & 35000 & 75000
3 & C & 25000 & 30000 & 55000
4 & D & 30000 & 50000 & 80000
5 & E & 45000 & 34000 & 79000
6 & F & 12000 & 30000 & 42000
7 & G & 38000 & 56000 & 94000
\end{array} \] % VLOOKUP Syntax (i) = VLOOKUP(B1, B2 : E8, 2, 0)
(ii) = SQRT(VLOOKUP(B5, B8 : E8, 2, 0) - 100000)
(iii) = VLOOKUP(A2, A2 : A8, 2, 0)
(iv) = VLOOKUP(B2, B3 : E4, 5, 0)
(v) = VLOOKUP(B2, A2 : E8, 0, 0)
(vi) = VLOOKUP(B2, B2 : E8, 2, 0)/0

Hint:

\textbf{Quick Tip:} Ensure the lookup value is in the first column of the table array. Column index numbers must match the structure of the table array. Avoid dividing by zero or referencing incorrect ranges.

Answer 1

\begin{center} \renewcommand{\arraystretch}{1.5} \begin{tabular}{|p{3cm}|p{7cm}|p{7cm}|} VLOOKUP Syntax & Error & Reason
(i) \texttt{=VLOOKUP(B1, B2:E8, 2, 0)} & Error: \texttt{\#N/A} & The lookup value \texttt{B1} is not present in the first column of the range \texttt{B2:E8}. The first column must contain the lookup value.
(ii) \texttt{=SQRT(VLOOKUP(B5, B8:E8, 2, 0) - 100000)} & Error: \texttt{\#N/A} & The lookup value \texttt{B5} is not found in the first column of the range \texttt{B8:E8}. The range must include the lookup value.
(iii) \texttt{=VLOOKUP(A2, A2:A8, 2, 0)} & Error: \texttt{\#VALUE!} & The column index (\texttt{2}) exceeds the number of columns in the range \texttt{A2:A8}, which has only one column.
(iv) \texttt{=VLOOKUP(B2, B3:E4, 5, 0)} & Error: \texttt{\#REF!} & The column index (\texttt{5}) is invalid as the range \texttt{B3:E4} has only 2 columns.
(v) \texttt{=VLOOKUP(B2, A2:E8, 0, 0)} & Error: \texttt{\#VALUE!} & The column index (\texttt{0}) is invalid. It must be a positive integer greater than or equal to \texttt{1}.
(vi) \texttt{=VLOOKUP(B2, B2:E8, 2, 0)/0} & Error: Division by zero & The formula attempts to divide the result by zero, which is undefined.
\end{tabular} \end{center}