The VLOOKUP function you've provided is designed to look up a value in the first column of a specified range and return a value from the same row in another column of that range.
Your VLOOKUP formula is structured as follows:
=VLOOKUP(D8, D3:E6, 2)
Breakdown of the Formula
- D8: This is the lookup value (21 in your case).
- D3:E6: This is the table array, which consists of the ranges D3 to D6 and E3 to E6.
- 2: This specifies that you want to return the value from the second column of the specified range (which is column E, since the range includes D and E).
Data Overview
The relevant data you have is:
D3: 10 E3: cat
D4: 20 E4: dog
D5: 30 E5: 12345
D6: 40 E6: 37
Explanation of the VLOOKUP Behavior
- The VLOOKUP function searches for the value in D8 (which is 21) in the first column of the specified table array (D3:D6).
- Since 21 is not present in the list of values (10, 20, 30, 40), VLOOKUP will not find an exact match.
Result
If VLOOKUP doesn't find an exact match for the lookup value, it returns an #N/A error.
If you wanted it to find the closest match, you can add an optional argument at the end with TRUE to allow for approximate matches:
=VLOOKUP(D8, D3:E6, 2, TRUE)
However, this will require your data in the first column to be sorted in ascending order for it to work correctly. In this case, for 21, it would return the value associated with the largest number less than or equal to 21, which is 20 ('dog') from E4.
In summary, using your original formula =VLOOKUP(D8, D3:E6, 2) will result in an #N/A error since 21 does not exist in your D column.