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 & H & 15000 & 30000 & 45000
2 & I & 18000 & 32000 & 50000
3 & J & 20000 & 29000 & 49000
4 & K & 23000 & 26000 & 49000
5 & L & 20000 & 30000 & 50000
6 & M & 24000 & 28000 & 52000
7 & N & 24000 & 28000 & 52000
\end{array} \] % Question details (i) = VLOOKUP (B1, B2 : E8, 2, 0)
(ii) = SQRT (VLOOKUP (B5, B8 : E8, 2, 0) – 10000)
(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 range. Avoid dividing by zero or using invalid ranges in formulas.

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} ("S. No.") is not found in the first column of the specified range \texttt{B2:E8}. The first column of the range must contain the lookup value.
(ii) \texttt{=SQRT(VLOOKUP(B5, B8:E8, 2, 0) - 10000)} & Error: \texttt{\#N/A} & The range \texttt{B8:E8} does not contain \texttt{B5} in the first column. Additionally, attempting to subtract from or process an invalid lookup result causes this error.
(iii) \texttt{=VLOOKUP(A2, A2:A8, 2, 0)} & Error: \texttt{\#VALUE!} & The column index number (\texttt{2}) is invalid because the range \texttt{A2:A8} has only one column. The column index must be within the range of columns provided.
(iv) \texttt{=VLOOKUP(B2, B3:E4, 5, 0)} & Error: \texttt{\#REF!} & The column index number (\texttt{5}) exceeds the number of columns in the table array \texttt{B3:E4}, which only has 2 columns.
(v) \texttt{=VLOOKUP(B2, A2:E8, 0, 0)} & Error: \texttt{\#VALUE!} & The column index number (\texttt{0}) is invalid because 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 VLOOKUP result by zero, which is mathematically undefined and results in an error.
\end{tabular} \end{center}