Question

From the given ‘VLOOKUP’ syntax, find out the error and its reason using the worksheet: \[ \begin{array}{|c|c|c|c|c|c|} \hline \textbf{S. No.} & \textbf{Name} & \textbf{Maths} & \textbf{English} & \textbf{Science} & \textbf{Total} \\ \hline 1 & \text{Vipul} & 38 & 58 & 66 & 162 \\ 2 & \text{Ram} & 88 & 92 & 74 & 254 \\ 3 & \text{Kiara} & 57 & 77 & 91 & 225 \\ 4 & \text{Kian} & 82 & 56 & 45 & 183 \\ 5 & \text{Kabir} & 75 & 51 & 57 & 183 \\ 6 & \text{Yuvaan} & 89 & 75 & 51 & 215 \\ 7 & \text{Vishnu} & 89 & 78 & 66 & 233 \\ 8 & \text{Neha} & 70 & 58 & 84 & 212 \\ \hline \end{array} \] VLOOKUP Syntax:
(i) = VLOOKUP(B5, C3:F10, 2, 0)
(ii) = SQRT(VLOOKUP(B3, B3:F10, 2, 0) - 100)
(iii) = VLOOKUP(B2, B3:F10, 5, 0)
(iv) = VLOOKUP(B3, B3:B10, 2, 0)
(v) = VLOOKUP(B6, B3:F10, 0, 0)
(vi) = VLOOKUP(B6, B3:F10, 2, 0)/0

Hint:

bf{Quick Tip:} Ensure the lookup value is in the first column of the table array. Use a valid column index that corresponds to the structure of the table array. Avoid dividing by zero or referencing incorrect ranges in formulas.

Answer 1

Using the worksheet below, find out the error and its reason for the given ‘VLOOKUP’ syntax: \[ \begin{array}{|c|c|c|} \hline \textbf{VLOOKUP Syntax} & \textbf{Error} & \textbf{Reason} \\ \hline \texttt{=VLOOKUP(B5, C3:F10, 2, 0)} & \texttt{\#N/A} & The lookup value \texttt{B5} (Kabir) is not present in the first column of the range \texttt{C3:F10}. The first column of the range must contain the lookup value. \\ \hline \texttt{=SQRT(VLOOKUP(B3, B3:F10, 2, 0) - 100)} & \texttt{\#N/A} & The lookup value \texttt{B3} (Kiara) is not in the first column of the range \texttt{B3:F10}, causing the VLOOKUP to fail. \\ \hline \texttt{=VLOOKUP(B2, B3:F10, 5, 0)} & \texttt{\#REF!} & The column index (\texttt{5}) is invalid as the range \texttt{B3:F10} contains only 4 columns. \\ \hline \texttt{=VLOOKUP(B3, B3:B10, 2, 0)} & \texttt{\#VALUE!} & The column index (\texttt{2}) exceeds the number of columns in the range \texttt{B3:B10}, which has only 1 column. \\ \hline \texttt{=VLOOKUP(B6, B3:F10, 0, 0)} & \texttt{\#VALUE!} & The column index (\texttt{0}) is invalid. It must be a positive integer greater than or equal to 1. \\ \hline \texttt{=VLOOKUP(B6, B3:F10, 2, 0)/0} & \textbf{Error: Division by zero} & The formula attempts to divide the result of \texttt{VLOOKUP} by zero, which is undefined. \\ \hline \end{array} \]