Hlookup() Failed with name range

[majidsiddique]

Hi all,
i using a formula Hlookup(C5,VL,A7,FALSE) is not working. can anybody tell me why? i use this sheet2 d8 cell. VL is the range of sheet1 d4 to s65. i want to change date c5 in sheet2 data pick AUTO from sheet1 as mention in sheet2 C8.
Sheet1,

sheet2,

 

[BlueHornet]

Re: Hlooup() Failed with name range

If I understand your question correctly, you're using a formula on Sheet2 to attempt a HLOOKUP on a range in Sheet1. Is that correct?

If the VL range is defined as a "Worksheet" named range, then you'd have to qualify it in your formula by referring to it as Sheet1!VL. Alternatively, if you define it at the Workbook level, that qualification will not be required. Since you're getting an error (and assuming no other error), then I deduce that you have the name defined at the Worksheet level, so you'll need to qualify it in the formula.

Try HLOOKUP(C5,Sheet1!VL,A7,FALSE)

 

[majidsiddique]

Re: Hlooup() Failed with name range

Hi BlueHornet,
i got it why this formula is not working. actually i did not mention ref no A7. this was blank i am trying to get result without enter the no. when you put number then it will work OK, thank to you to consider and reply.
Thanks