Hlookup
 

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

Hlookup
 

look in the help index for ISNA

--
Don Guillett
Microsoft MVP Excel
SalesAid Software

"Duplatt" wrote in message
...
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

Hlookup
 

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!

In article ,
Duplatt wrote:

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

Hlookup
 

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

"Domenic" wrote:

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!

In article ,
Duplatt wrote:

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

Hlookup
 

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
--
Bernard V Liengme
Microsoft Excel MVP
www.stfx.ca/people/bliengme
remove caps from email

"Duplatt" wrote in message
...
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

Hlookup
 

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

In article ,
Duplatt wrote:

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

Hlookup
 

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

"Domenic" wrote:

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

In article ,
Duplatt wrote:

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

Hlookup
 

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

"Bernard Liengme" wrote:

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
--
Bernard V Liengme
Microsoft Excel MVP
www.stfx.ca/people/bliengme
remove caps from email

"Duplatt" wrote in message
...
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

Hlookup
 

Notice that the lookup range for MATCH is...

Sheet1!$A$21:$AP$21

not

Sheet1!$A$21:$AP$41

Could this be the problem?

In article ,
Duplatt wrote:

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

Hlookup
 

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

"Domenic" wrote:

Notice that the lookup range for MATCH is...

Sheet1!$A$21:$AP$21

not

Sheet1!$A$21:$AP$41

Could this be the problem?

In article ,
Duplatt wrote:

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

Hlookup
 

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
--
Bernard V Liengme
Microsoft Excel MVP
www.stfx.ca/people/bliengme
remove caps from email

"Duplatt" wrote in message
...
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

"Bernard Liengme" wrote:

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
--
Bernard V Liengme
Microsoft Excel MVP
www.stfx.ca/people/bliengme
remove caps from email

"Duplatt" wrote in message
...
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

Hlookup
 

If you try...

=A1=F21

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

In article ,
Duplatt wrote:

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