Lookup Table / (Excel 2003)

[rory]

Braddy,
You can write that formula as:
<code>=IF(OR(C5={56,265,275,"17Y"}),"Distr",IF(AND (G5>0,H5=0),"Free/NSI","Keep"))</code>
which may be easier to maintain.

[braddy60]

Hi Rory

Yeah that works nicely, Is there a limit to how many variables you can put between the curly brackets?

Grateful Thanks

Braddy

[rory]

Honestly, I don't know. <img src=/S/shrug.gif border=0 alt=shrug width=39 height=15> I used 50 as a test just now without problems but there might be issues if the formula gets too long.

[braddy60]

Hi Rory

I will test it to destruction <img src=/S/evilgrin.gif border=0 alt=evilgrin width=15 height=15>

Thanks

Braddy

[rory]

I would say that if you get anywhere near a limit (or indeed 50), you should probably be looking at lookup tables, which will be easier to maintain.

[sdckapr]

An easier one to maintain, mibht be to use a Match for the first and second.
something like:

=IF(isnumber(Match(A2,<theLookuprange>,0)),"Dump Acc",IF(OR(D2=275,D2=265,D2="17Y",D2=56),"Distr",I F(AND(G2>0,H2>0),"Free/NSI","Keep")))

or even:
=IF(isnumber(Match(A2,<theLookuprange>,0)),"Dump Acc",IF(isnumber(match(d2,<otherlookup>,0)),"Distr ",IF(AND(G2>0,H2>0),"Free/NSI","Keep")))

The names in brackets could be names or ranges as desired for looking up. They could be dynamic ranges that grow as more items are added...

Steve

[braddy60]

Hi Steve

That looks great I will give it whirl, Thanks again.

Braddy

[Aladin Akyurek]

> The names in brackets could be names or ranges as desired for looking up. They could be dynamic ranges that grow as more items are added...

Since he is on Excel 2003, he can better convert the lookup tables into lists with Data|List|Create List, which makes defining ranges by means of dynamic formulas superfluous.

[sdckapr]

Thanks for the info.

I do not have XL2003 (I only obtained XL2002 last month). Most of my answers are more "general" since they will work with XL97.

Steve