Question

Using the worksheet, find out the error and its reason for the given `VLOOKUP` syntax: \[ \begin{array}{|c|l|c|c|} \hline \textbf{S. No.} & \textbf{Consumables} & \textbf{Price in FY 21-22 (₹)} & \textbf{Price in FY 23-24 (₹)} \\ \hline 1 & \text{Pineapple} & 40 & 55 \\ \hline 2 & \text{Kiwi} & 34 & 45 \\ \hline 3 & \text{Jackfruit} & 50 & 62 \\ \hline 4 & \text{Blueberry} & 35 & 54 \\ \hline 5 & \text{Butter} & 50 & 56 \\ \hline 6 & \text{Buns} & 48 & 45 \\ \hline 7 & \text{Meat} & 36 & 48 \\ \hline \end{array} \] Worksheet data for VLOOKUP syntax

Hint:

The VLOOKUP function can be tricky when the reference range or column index is not set correctly. Always ensure the lookup value exists within the specified range, and the column index is valid.

Answer 1

1. Formula: \[ =VLOOKUP(B1, B4 : D6, 2, 0) \] 
Reason: The reference range used in this formula (B4:D6) does not include the entire data set. The range should start from the top row of the table (e.g., B1:D7) for proper referencing. 

2. Formula: \[ =SQRT(VLOOKUP(C2, C2 : D8, 2, 0) - 100) \] 
Reason: The VLOOKUP function is trying to search for the value in C2 within the range C2:D8, which results in an invalid range. The lookup value should exist within a different range. 

3. Formula: \[ =VLOOKUP(B5, B6 : D8, 1, 0) \] 
Reason: The 1st column index (A) does not exist in the lookup range (B6:D8). The first column should be in the lookup range for the formula to work properly. 

4. Formula: \[ =VLOOKUP(B3, B2 : D5, 5, 0) \] 
Reason: The column index (5) exceeds the available columns in the specified range (B2:D5). The column index should be less than or equal to the number of columns in the range. 

5. Formula: \[ =VLOOKUP(B5, B3 : D8, 0) \] 
Reason: The column index (0) is invalid. The column index must be a positive integer that corresponds to a column number within the given range. 

6. Formula: \[ =VLOOKUP(B2, B2 : D7, 2, 0) \] 
Reason: This error occurs if the lookup value (B2) does not exist in the first column of the specified range. Ensure the lookup value is available in the range for a correct lookup.