Question

Using the worksheet, find out the error and its reason for the given 'VLOOKUP' syntax:

\[ \begin{array}{|c|c|c|c|} \hline \text{S. No.} & \text{Consumables} & \text{Price in FY 21-22} & \text{Price in FY 23-24} \\ \hline 1 & \text{Muskmelon} & 45 & 122 \\ 2 & \text{Watermelon} & 9 & 21 \\ 3 & \text{Squash} & 22 & 35 \\ 4 & \text{Gourd} & 47 & 68 \\ 5 & \text{Curd} & 49 & 66 \\ 6 & \text{Brisket} & 33 & 43 \\ 7 & \text{Poultry} & 88 & 96 \\ \hline \end{array} \]

(i) =VLOOKUP(B1, B4 : D6, 2, 0)
(ii) =SQRT(VLOOKUP(C2, C2 : D8, 2, 0) – 100)
(iii) =VLOOKUP(B5, B6 : D8, 1, 0)
(iv) =VLOOKUP(B3, B2 : D8, 5, 0)
(v) =VLOOKUP(B5, B3 : D8, 0, 0)
(vi) =VLOOKUP(B2, B2 : D7, 2, 0)/0

Hint:

- When using VLOOKUP, ensure that the lookup value is found in the first column of the selected range. - For SQRT, check if the value inside the function is non-negative, as square roots of negative numbers cause errors. - Ensure the column index number in VLOOKUP is valid and within the range of columns in the selected table array. - Always verify that the range and column index are compatible in VLOOKUP to avoid #REF! errors. - #VALUE! errors can occur in VLOOKUP when an invalid column index is used, such as 0. Use positive integers only. - A #DIV/0! error occurs when dividing by zero. Always check that your denominator is non-zero to prevent this error.

Answer 1

\[ \begin{array}{|c|c|c|} \hline \textbf{VLOOKUP Syntax} & \textbf{Error} & \textbf{Reason} \\ \hline \text{(i) =VLOOKUP(B1, B4:D6, 2, 0)} & \#N/A & \text{B1 is not found in the first column of B4:D6.} \\ \hline \text{(ii) =SQRT(VLOOKUP(C2, C2:D8, 2, 0) - 100)} & \#N/A & \text{C2 is not in the first column of C2:D8, causing an invalid result.} \\ \hline \text{(iii) =VLOOKUP(B5, B6:D8, 1, 0)} & \#VALUE! & \text{Column index 1 is invalid for the range B6:D8.} \\ \hline \text{(iv) =VLOOKUP(B3, B2:D8, 5, 0)} & \#REF! & \text{Column index 5 exceeds the columns in B2:D8.} \\ \hline \text{(v) =VLOOKUP(B5, B3:D8, 0, 0)} & \#VALUE! & \text{Column index 0 is invalid (must be at least 1).} \\ \hline \text{(vi) =VLOOKUP(B2, B2:D7, 2, 0)} & \text{No error} & \text{Correct syntax, returns value from the second column.} \\ \hline \end{array} \]

Explanation:

• The lookup value must be in the first column of the range.
• The column index must be within the range of the table array.
• Common errors include \#N/A, \#VALUE!, or \#REF! due to incorrect parameters