
VLOOKUP Error
VLOOKUP is an Excel function that’s used to find things and return them.
There can be a number of reasons why your VLOOKUP function is not working. Let’s work through some of them.
1. The lookup value isn’t to the left of the return value.
a. Organizing your data is important when doing a VLOOKUP, the lookup value needs to be to the left of the return value the function is looking for. If this is not the case the function will not work. If you are not able to move the values, use an XLOOKUP instead.
2. Extra values/spaces in the cell
a. To do an exact lookup the data must match, sometimes there are additional spaces in the cells.
b. Use the TRIM() function to remove trailing spaces from the data. Example = LOOKUP (TRIM(B2), TRIM(C2:H50),4,FALSE)
3. Data Type doesn’t match
a. Check the data you are looking up and ensure the data types are the same. i.e. Number, Date, Text…
b. Change the data type to match for your lookup function and return value.
Advance tip for converting dates: At times the data type conversion will not refresh; one way to overwrite a column is to highlight the column go to Data tab > Text to Column > Delimited> Next > Uncheck Delimiters (Tab) > Next> select Date> Finish
4. Data Range not valid
a. Check that the data range covers the accurate data and it’s absolute = LOOKUP (TRIM(B2), TRIM($C$2:$H$50),4,FALSE)
b. This is especially important when you copy down the data.
c. Ensure the return value is in the data range.
Overall recommendation is to use XLOOKUP it’s easier to navigate.
Create Interactive Tableau Dashboards with Dynamic Zone Visibility