HLOOKUPs returning an NA value
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 |
HLOOKUPs returning an NA value
Should your lookup range be constant?
=HLOOKUP(E$2,'FY09 Monthly'!$D$2:$BL$147,$A5,0) Regards, Paul -- wrote in message ... 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 |
HLOOKUPs returning an NA value
On May 16, 4:02*pm, "PCLIVE" wrote:
Should your lookup range be constant? =HLOOKUP(E$2,'FY09 Monthly'!$D$2:$BL$147,$A5,0) Regards, Paul -- wrote in message ... 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- Hide quoted text - - Show quoted text - Hi Paul, Yes, I need to drag it across 30 columns and 130 rows |
HLOOKUPs returning an NA value
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 -- wrote in message ... On May 16, 4:02 pm, "PCLIVE" wrote: Should your lookup range be constant? =HLOOKUP(E$2,'FY09 Monthly'!$D$2:$BL$147,$A5,0) Regards, Paul -- wrote in message ... 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- Hide quoted text - - Show quoted text - Hi Paul, Yes, I need to drag it across 30 columns and 130 rows |
HLOOKUPs returning an NA value
On May 16, 10:51*am, wrote:
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 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. |
All times are GMT +1. The time now is 03:39 PM. |
Powered by vBulletin® Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
ExcelBanter.com