| Home |
| Search |
| Today's Posts |
|
#1
Posted to microsoft.public.excel.newusers
|
|||
|
|||
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 |
|
#2
Posted to microsoft.public.excel.newusers
|
|||
|
|||
|
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 |
|
#3
Posted to microsoft.public.excel.newusers
|
|||
|
|||
|
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 |
|
#4
Posted to microsoft.public.excel.newusers
|
|||
|
|||
|
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 |
|
#5
Posted to microsoft.public.excel.newusers
|
|||
|
|||
|
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 |
|
#6
Posted to microsoft.public.excel.newusers
|
|||
|
|||
|
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 |
|
#7
Posted to microsoft.public.excel.newusers
|
|||
|
|||
|
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 |
|
#8
Posted to microsoft.public.excel.newusers
|
|||
|
|||
|
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 |
|
#9
Posted to microsoft.public.excel.newusers
|
|||
|
|||
|
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 |
|
#10
Posted to microsoft.public.excel.newusers
|
|||
|
|||
|
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 |
|
#11
Posted to microsoft.public.excel.newusers
|
|||
|
|||
|
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 |
|
#12
Posted to microsoft.public.excel.newusers
|
|||
|
|||
|
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 |
| Reply |
| Thread Tools | Search this Thread |
| Display Modes | |
|
|
Similar Threads
|
||||
| Thread | Forum | |||
| V+HLookup? | Excel Discussion (Misc queries) | |||
| Hlookup? | Excel Worksheet Functions | |||
| Hlookup for row 3 and 4 | Excel Worksheet Functions | |||
| Hlookup? | Excel Worksheet Functions | |||
| HLookup ??? | Excel Discussion (Misc queries) | |||
Linear Mode
