Hlookup

[Guest]

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

 

[Don Guillett]

look in the help index for ISNA

 

[Domenic]

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!

 

[Guest]

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

 

[Bernard Liengme]

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

 

[Domenic]

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

 

[Guest]

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

 

[Guest]

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

 

[Domenic]

Notice that the lookup range for MATCH is...

Sheet1!$A$21:$AP$21

not

Sheet1!$A$21:$AP$41

Could this be the problem?

 

[Guest]

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

 

[Bernard Liengme]

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

 

[Domenic]

If you try...

=A1=F21

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