Named Range in a Hlookup using Vlookup

[LouisT]

Hi Everyone, I'm trying to pull a Named Range using Vlookup into a Hlookup to return a text value. I've tested the individual parts and they work fine but when i put them together I get "#VALUE" as a result.

The formula is: =HLOOKUP(E$1,(VLOOKUP($B2,'Emp Lists'!$B:$F,5,FALSE)),($D2+1),FALSE) and the bolded text is the part of the formula that is returning the named range when i just use that it returns the proper value however it fails when it is nested into the HLookup.

For information: E1 = Date, B2 - Employee number value, D2 - numerical lookup for row to use

Hope this helps and someone knows the solution.

Thanks
Louis

 

[Special-K99]

VLOOKUP returns a value, perhaps text.
If the result of the VLOOKUP is text that reflects a range, e.g. A1:Z1 you need to put it in an INDIRECT()

 

[LouisT]

VLOOKUP returns a value, perhaps text.
If the result of the VLOOKUP is text that reflects a range, e.g. A1:Z1 you need to put it in an INDIRECT()

Thanks Special-K99, do you mean like this: =HLOOKUP(E$1,INDIRECT((VLOOKUP($B2,'Emp Lists'!$B:$F,5,FALSE))),($D2+1),FALSE)

Update: Nevermind, that worked PERFECTLY! Thank you very much!