Hlookup

Thread view:

Enable EMail Alerts

Start New Thread

Thread rating:

Duplatt - 07 Nov 2007 17:29 GMT

I have this formula
=hlookup($a$1,sheet1!$a$21:$ap$41,1.false)
It works fine but, returns #NA if $a$1 not found.
How can I make it return "out of range"
Thank you
Duane Platt

Reply to this Message

Don Guillett - 07 Nov 2007 17:40 GMT

look in the help index for ISNA

Signature

Don Guillett
Microsoft MVP Excel
SalesAid Software
dguillett1@austin.rr.com

>I have this formula
> =hlookup($a$1,sheet1!$a$21:$ap$41,1.false)
> It works fine but, returns #NA if $a$1 not found.
> How can I make it return "out of range"
> Thank you
> Duane Platt

Reply to this Message

Domenic - 07 Nov 2007 17:45 GMT

Try...

=IF($A$1<>"",IF(ISNUMBER(MATCH($A$1,Sheet1!$A$21:$AP$21,0)),HLOOKUP($A$1,
Sheet1!$A$21:$AP$41,1,FALSE),"Out of Range"),"")

However, for this example, since HLOOKUP is not returning a value from
one of the rows below the first one in the lookup table, the following
should suffice...

=IF($A$1<>"",IF(ISNUMBER(MATCH($A$1,Sheet1!$A$21:$AP$41,0)),$A$1,"Out of
Range"),"")

Hope this helps!

> I have this formula
> =hlookup($a$1,sheet1!$a$21:$ap$41,1.false)
> It works fine but, returns #NA if $a$1 not found.
> How can I make it return "out of range"
> Thank you
> Duane Platt

Reply to this Message

Duplatt - 07 Nov 2007 18:51 GMT

Thank you Domenic.
Tried the formula. returns "out of range" regardless of what numbers are in
$a$1.
$a$1 is 2007 -- current year, on sheet 2
Sheet1!$a$21:$ap$41 are years from 2002 to 2042
would appreciate any help
Duane

> Try...
>
[quoted text clipped - 16 lines]
> > Thank you
> > Duane Platt

Reply to this Message

Domenic - 07 Nov 2007 19:24 GMT

Maybe there's a mismatch in the data type between the lookup value in A1
and the lookup range in A21:AP21. Let's say A1 contains 2007 and F21
contains 2007, what does the following return?

=A1=F21

> Thank you Domenic.
> Tried the formula. returns "out of range" regardless of what numbers are in
[quoted text clipped - 3 lines]
> would appreciate any help
> Duane

Reply to this Message

Duplatt - 07 Nov 2007 20:18 GMT

Domenic
I think the data type was a mismatch. The ISNUMBER(MATCH works fine now
The ISNUNBER - MATCH - HLOOKUP still returns "out of range"
I do need the HLOOKUP feature for other data on the page.
Appreciate any help
Duane

> Maybe there's a mismatch in the data type between the lookup value in A1
> and the lookup range in A21:AP21. Let's say A1 contains 2007 and F21
[quoted text clipped - 9 lines]
> > would appreciate any help
> > Duane

Reply to this Message

Domenic - 07 Nov 2007 20:44 GMT

Notice that the lookup range for MATCH is...

Sheet1!$A$21:$AP$21

not

Sheet1!$A$21:$AP$41

Could this be the problem?

> Domenic
> I think the data type was a mismatch. The ISNUMBER(MATCH works fine now
> The ISNUNBER - MATCH - HLOOKUP still returns "out of range"
> I do need the HLOOKUP feature for other data on the page.
> Appreciate any help
> Duane

Reply to this Message

Duplatt - 07 Nov 2007 23:56 GMT

Typo on my part
I have reconstrusted the scene on another sheet and still get the same result
The ISNUMBER(MATCH works fine
The ISNUMBER(MATCH - HLOOKUP only produces "Out of Range"
I really do appreciate any help.
Thanks - Duane

> Notice that the lookup range for MATCH is...
>
[quoted text clipped - 12 lines]
> > Appreciate any help
> > Duane

Reply to this Message

Domenic - 09 Nov 2007 15:05 GMT

If you try...

=A1=F21

...where A1 contains the lookup value and F21 contains the supposedly
matching value, what does it return?

> Typo on my part
> I have reconstrusted the scene on another sheet and still get the same result
> The ISNUMBER(MATCH works fine
> The ISNUMBER(MATCH - HLOOKUP only produces "Out of Range"
> I really do appreciate any help.
> Thanks - Duane

Reply to this Message

Bernard Liengme - 07 Nov 2007 19:13 GMT

Did you read Don' G's advice?
=IF(ISNA(hlookup($a$1,sheet1!$a$21:$ap$41,1,false),"",hlookup($a$1,sheet1!$a$21:$ap$41,1,false))
This returns a 'blank' in place of #N/A when $A$1 is not found.
best wishes

Signature

Bernard V Liengme
Microsoft Excel MVP
www.stfx.ca/people/bliengme
remove caps from email

>I have this formula
> =hlookup($a$1,sheet1!$a$21:$ap$41,1.false)
> It works fine but, returns #NA if $a$1 not found.
> How can I make it return "out of range"
> Thank you
> Duane Platt

Reply to this Message

Duplatt - 07 Nov 2007 20:24 GMT

Bernard
Yes, I did see Don' G,s note
I'm too new at this to understand how to put various funtions together
I tried your suggestion , I get "error flag" I have read & reread & retyped.
Still can't make it work
Any help appreciated - Duane

> Did you read Don' G's advice?
> =IF(ISNA(hlookup($a$1,sheet1!$a$21:$ap$41,1,false),"",hlookup($a$1,sheet1!$a$21:$ap$41,1,false))
[quoted text clipped - 6 lines]
> > Thank you
> > Duane Platt

Reply to this Message

Bernard Liengme - 08 Nov 2007 00:00 GMT

What "error flag" ?
You say that you HLOOKUP works expect when there is no match
Use copy and paste to get
=IF(ISNA(your-formula), " ", your-formula)
or try
=IF(ISERROR(your-formula), " ", your-formula)

Are you using English-language version of Excel - if not use the correct
delimiter (e.g use ; rather than , between arguments)
If this fails send me a sample file (to my private email, not to the
newsgroup)
best wishes

Signature

Bernard V Liengme
Microsoft Excel MVP
www.stfx.ca/people/bliengme
remove caps from email

> Bernard
> Yes, I did see Don' G,s note
[quoted text clipped - 14 lines]
>> > Thank you
>> > Duane Platt

Reply to this Message