#REF message when using MS- Excel lookup functions across sheets in a workbook
User receive a #REF error in cells where they have used a Vlookup across worksheets in their report.
When creating a Vlookup between worksheets, you will need to use an indirect formula when referencing the array from the other worksheet.
The formula would look like this: =VLOOKUP(lookup_value,INDIRECT("ref_text"),col_index_num,[range_lookup])
Our consultants also recommend using a MATCH function as well. We have a knowledgebase article on how to do this. You can access this KB through the following link:
Report Designer all versions