HLOOKUPs returning an NA value

Thread view:

Enable EMail Alerts

Start New Thread

Thread rating:

nadia.younus@googlemail.com - 16 May 2008 15:51 GMT

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

Reply to this Message

PCLIVE - 16 May 2008 16:02 GMT

Should your lookup range be constant?

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

Regards,
Paul

> Hi,
>
[quoted text clipped - 17 lines]
>
> Nadia

Reply to this Message

nadia.younus@googlemail.com - 16 May 2008 16:09 GMT

> Should your lookup range be constant?
>
[quoted text clipped - 32 lines]
>
> - Show quoted text -

Hi Paul,

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

Reply to this Message

PCLIVE - 16 May 2008 16:27 GMT

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

On May 16, 4:02 pm, "PCLIVE" <pclive(RemoveThis)@cox.net> wrote:

> Should your lookup range be constant?
>
[quoted text clipped - 32 lines]
>
> - Show quoted text -

Hi Paul,

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

Reply to this Message

JW - 16 May 2008 16:31 GMT

On May 16, 10:51 am, nadia.you...@googlemail.com wrote:

> Hi,
>
[quoted text clipped - 17 lines]
>
> 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.

Reply to this Message