Selected Answer
Please try this formula.
=IFERROR(IF(SUBTOTAL(103,INDIRECT(ADDRESS(MATCH($A2,Sheet2!$A$1:$A5,0),1,1,1,"Sheet2"))),VLOOKUP($A2,Sheet2!$A$2:$B$8,2,FALSE),""),"")
For better readability and easier management I recommend that you create two named ranges on Sheet2 as follows. Extend the end beyond the immediate need but avoid including all 1.4 million rows.
- CodeColumn ($A$1:$A$5)
This range is used by the MATCH function. It must start in row 1.
- CodeList ($A$2:$B$5)
This is the range VLOOKUP uses.
With the named ranges set the above formula would look like this.
=IFERROR(IF(SUBTOTAL(103,INDIRECT(ADDRESS(MATCH($A2,CodeColumn,0),1,1,1,"Sheet2"))),VLOOKUP($A2,CodeList,2,FALSE),""),"")
At the heart of this formula is the SUBTOTAL(103 function which returns 0 if the counted cell is invisible, otherwise 1.
Finally, here is an improved version of the above, created on second thought.
=IFERROR(IF(SUBTOTAL(103,INDEX(CodeColumn,MATCH($A2,CodeColumn,0))),VLOOKUP($A2,CodeList,2,FALSE),""),"")
This function does the same thing but avoids the rather cumbersome INDIRECT(ADDRESS replacing it with the Reference type of the INDEX function. In fact this type is intended for precisely this use and there is no need to circumvent it. I'm leaving the original here for its instructive value. You should prefer to deploythe improved final version in your own workbook.