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 lookup range (B4:D6) is incomplete. It must encompass the entire dataset, starting from the first row (e.g., B1:D7). 

2. Formula: \[ =SQRT(VLOOKUP(C2, C2 : D8, 2, 0) - 100) \] 
Reason: The lookup value C2 is present within the lookup range C2:D8, creating an invalid range for VLOOKUP. The lookup value should be in a different range than the specified lookup range. 

3. Formula: \[ =VLOOKUP(B5, B6 : D8, 1, 0) \] 
Reason: The specified column index (1) refers to a column that does not exist in the lookup range (B6:D8). The lookup range must include the column from which data is to be retrieved. 

4. Formula: \[ =VLOOKUP(B3, B2 : D5, 5, 0) \] 
Reason: The specified column index (5) exceeds the number of columns available in the lookup range (B2:D5). The column index must be within the bounds of the lookup range.

5. Formula: \[ =VLOOKUP(B5, B3 : D8, 0) \] 
Reason: The specified column index (0) is invalid. It must be a positive integer corresponding to a column within the lookup range. 

6. Formula: \[ =VLOOKUP(B2, B2 : D7, 2, 0) \] 
Reason: The lookup value (B2) is not found in the first column of the specified range. Ensure the lookup value exists in the first column of the range for successful retrieval.