I have created a Union report. I want Excel to lookup information from Sheet 1 in Sheet 3 then return the correct answer. My problem is that when I run the Alchemex report, it doesn't recognise the data in sheet 3. When I manually override the information in the lookup cell of sheet 3, it does the calculation correctly. How do I get Excel to recognise sheet 3 (which is part of the union report)?

Have you checked to ensure that the reference cell format (data type) used in you vlookup in sheet3 matches that of the the comparator cell format (data type) of sheet 1 and that there are no trailing blank spaces in one of them?

Scenario 1
e.g. If Sheet1 cell A1 = 123 is type Numeric and Sheet3 Cell C1 = 123 is type Text then the VLOOKUP will not "work". Both need to be the same format.

Scenario 2
e.g. If Sheet1 cell A1 = 'HELLO' and Sheet3 Cell C1 = 'HELLO ' (5 trailing blank spaces) then the VLOOKUP will not "work" - The solution would be to change the source expression "feeding" Sheet3 from "MYTABLE"."MYFIELD" to RTRIM("MYTABLE"."MYFIELD").