Here are some common problems to consider. They may be the reasons why your VLOOKUP isn't working:
1. Data is not formatted as the same type. For example, one column might be the date, whereas the corresponding column’s data in the other table is text. They usually need to be the same for the VLOOKUP to work.
2. If the data in the second table is not in the first column then you won’t be able to use a
VLOOKUP. Try an XLOOKUP or INDEX/MATCH instead!
3. There may be spaces at the end of some of the cells in one of both of the matching columns. You can delete them using the TRIM formula.
4. If the second table of data is not on the same sheet or even the same workbook, you can
still do a VLOOKUP. Just point the table array at the location of the second set of data.
5. I’m getting some N/A errors - this could be correct. N/A will be displayed where there isn't a corresponding value in the second table.
6. I’m getting too many N/A errors. If the entire column is showing N/A, then that could mean
the formula has been created incorrectly. However, do a spot check and see if a value should
have been returned.
7. If you are getting incorrect results or lots of N/A errors, then check whether you have used TRUE or FALSE as the final element of the VLOOKUP. Generally, FALSE is used where the matching values are text, or numbers which are being used as an identifier, for example, a serial number or reference number. TRUE is used where you are matching numeric values such as brands of numbers.
Still stuck? Don't worry - Paramount Training is at your service! Contact us to book a half day training session dedicated to VLOOKUP, or a full day if you’d like to cover additional topics too.
Not sure which course to take? Find out more about our Microsoft Excel Training services today.