HLOOKUPs returning an NA value

[nadia.younus]

Hi,

I have two HLOOKUP formulae and both are returning an NA value:

a) =HLOOKUP(E$2,'FY09 Monthly'!D2:BL147,$A5,0)

and

b) HLOOKUP(E$2,TestNameRange,$A5,0)

The first one (a) works but when I copy it to the next cell, I get an
NA error:

=HLOOKUP(E$2,'FY09 Monthly'!D3:BL147,$A5,0)

In the second one (b), I have given a range a name and that doesn't
work either. Does any one have any ideas?

Thanks,

Nadia

 

[PCLIVE]

Should your lookup range be constant?

=HLOOKUP(E$2,'FY09 Monthly'!$D$2:$BL$147,$A5,0)

Regards,
Paul

 

[nadia.younus]

Should your lookup range be constant?

=HLOOKUP(E$2,'FY09 Monthly'!$D$2:$BL$147,$A5,0)

Regards,
Paul

--











- Show quoted text -

Hi Paul,

Yes, I need to drag it across 30 columns and 130 rows

 

[PCLIVE]

The reason for my question is that your formula (first one) did not have it
as being constant.

Base on your formula, I'm assuming the lookup address (E$2) should increment
its column as you drag to the right but NOT its row number as its dragged
down, correct?
And $A5, which will contain the result row number, will not increment as you
drag to the right but the row referenced will increment as it is dragged
down, correct?

As long as the lookup value is found in the range, then it should be ok.
But like I said before, the lookup range wasn't constant in your first
formula.

Regards,
Paul


--

Should your lookup range be constant?

=HLOOKUP(E$2,'FY09 Monthly'!$D$2:$BL$147,$A5,0)

Regards,
Paul

--











- Show quoted text -

Hi Paul,

Yes, I need to drag it across 30 columns and 130 rows

 

[JW]

Hi,

I have two HLOOKUP formulae and both are returning an NA value:

a) =HLOOKUP(E$2,'FY09 Monthly'!D2:BL147,$A5,0)

and

b) HLOOKUP(E$2,TestNameRange,$A5,0)

The first one (a) works but when I copy it to the next cell, I get an
NA error:

=HLOOKUP(E$2,'FY09 Monthly'!D3:BL147,$A5,0)

In the second one (b), I have given a range a name and that doesn't
work either. Does any one have any ideas?

Thanks,

Nadia

If you are doing a direct copy of your formula into the next cell, it
would be
=HLOOKUP(E$2,'FY09 Monthly'!D3:BL148,$A6,0)

Could this be causing your problem? It is hard for me to diagnose
your issue without actually seeing the spreadsheet. Feel free to send
it to me if you can't get it sorted.