named ranges in VLOOKUP

INDIRECT won't work with Dynamic Named Ranges

Martin yours works because your Named Ranges are not Dynamic
(ie they are hardwired ranges)

You may be able to make the range dynamic within the formula itself (ie not using DNR) but we'd need to see an example.

Using Martin's file as an example... dispensing with Named Ranges (ie we assume they will be Dynamic and thus won't work), then you could try an approach such as:

Sheet1!A1: =VLOOKUP(C11,INDIRECT("'"&E4&"'!A1:B"&MATCH(REPT("Z",255),INDIRECT("'"&E4&"'!B:B"))),2,0)