I have the following IF-ISBLANK--IFERROR--VLOOKUP formula:
=IF(ISBLANK(F218),"",IFERROR(VLOOKUP(F218,'Categor y
Lookup'!$A$1:$B$983,2,FALSE),"New description to be categorized")) (I'm
using XL 2007, hence the IFERROR function...)

I want to replace the table array in the VLOOKUP with a dynamic named range.
I have a dynamic named range, LookupRange3, which uses the formula
=OFFSET($A$1,0,0,COUNTA($A:$A),1) in the Refers To field, which formula I
retrieved from http://www.ozgrid.com/Excel/DynamicRanges.htm

So now my formula looks like this:
=IF(ISBLANK(F218),"",IFERROR(VLOOKUP(F218,LookupRa nge3,2,FALSE),"New
description to be categorized"))

Unfortunately, this returns "New description to be categorized" rather than
the appropriate lookup value. Ideas?
--
Brevity is the soul of wit.

That dynamic range is only 1 column wide, so the lookup will always error,
it should be

=OFFSET($A$1,0,0,COUNTA($A:$A),2)

--
---
HTH

Bob

(change the xxxx to gmail if mailing direct)


"Dave F" wrote in message
...
I have the following IF-ISBLANK--IFERROR--VLOOKUP formula:
=IF(ISBLANK(F218),"",IFERROR(VLOOKUP(F218,'Categor y
Lookup'!$A$1:$B$983,2,FALSE),"New description to be categorized")) (I'm
using XL 2007, hence the IFERROR function...)

I want to replace the table array in the VLOOKUP with a dynamic named
range.
I have a dynamic named range, LookupRange3, which uses the formula
=OFFSET($A$1,0,0,COUNTA($A:$A),1) in the Refers To field, which formula I
retrieved from http://www.ozgrid.com/Excel/DynamicRanges.htm

So now my formula looks like this:
=IF(ISBLANK(F218),"",IFERROR(VLOOKUP(F218,LookupRa nge3,2,FALSE),"New
description to be categorized"))

Unfortunately, this returns "New description to be categorized" rather
than
the appropriate lookup value. Ideas?
--
Brevity is the soul of wit.

OK I feel like an idiot....turns out I did NOT have the Refers to Formula in
the named range. Once I put it in this worked perfectly.

Pls disregard.
--
Brevity is the soul of wit.


"Dave F" wrote:

I have the following IF-ISBLANK--IFERROR--VLOOKUP formula:
=IF(ISBLANK(F218),"",IFERROR(VLOOKUP(F218,'Categor y
Lookup'!$A$1:$B$983,2,FALSE),"New description to be categorized")) (I'm
using XL 2007, hence the IFERROR function...)

I want to replace the table array in the VLOOKUP with a dynamic named range.
I have a dynamic named range, LookupRange3, which uses the formula
=OFFSET($A$1,0,0,COUNTA($A:$A),1) in the Refers To field, which formula I
retrieved from http://www.ozgrid.com/Excel/DynamicRanges.htm

So now my formula looks like this:
=IF(ISBLANK(F218),"",IFERROR(VLOOKUP(F218,LookupRa nge3,2,FALSE),"New
description to be categorized"))

Unfortunately, this returns "New description to be categorized" rather than
the appropriate lookup value. Ideas?
--
Brevity is the soul of wit.

Yes, thanks, I figured that out last night.
--
Brevity is the soul of wit.


"Bob Phillips" wrote:

That dynamic range is only 1 column wide, so the lookup will always error,
it should be

=OFFSET($A$1,0,0,COUNTA($A:$A),2)

--
---
HTH

Bob

(change the xxxx to gmail if mailing direct)


"Dave F" wrote in message
...
I have the following IF-ISBLANK--IFERROR--VLOOKUP formula:
=IF(ISBLANK(F218),"",IFERROR(VLOOKUP(F218,'Categor y
Lookup'!$A$1:$B$983,2,FALSE),"New description to be categorized")) (I'm
using XL 2007, hence the IFERROR function...)

I want to replace the table array in the VLOOKUP with a dynamic named
range.
I have a dynamic named range, LookupRange3, which uses the formula
=OFFSET($A$1,0,0,COUNTA($A:$A),1) in the Refers To field, which formula I
retrieved from http://www.ozgrid.com/Excel/DynamicRanges.htm

So now my formula looks like this:
=IF(ISBLANK(F218),"",IFERROR(VLOOKUP(F218,LookupRa nge3,2,FALSE),"New
description to be categorized"))

Unfortunately, this returns "New description to be categorized" rather
than
the appropriate lookup value. Ideas?
--
Brevity is the soul of wit.