Thread: Lookup Table / (Excel 2003)

1. Re: Lookup Table / (Excel 2003)

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.

2. Re: Lookup Table / (Excel 2003)

Hi Rory

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

Grateful Thanks

3. Re: Lookup Table / (Excel 2003)

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.

4. Re: Lookup Table / (Excel 2003)

Hi Rory

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

Thanks

5. Re: Lookup Table / (Excel 2003)

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.

6. Re: Lookup Table / (Excel 2003)

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

7. Re: Lookup Table / (Excel 2003)

Hi Steve

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

8. Re: Lookup Table / (Excel 2003)

> 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.

9. Re: Lookup Table / (Excel 2003)

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

Page 2 of 2 First 12

Posting Permissions

• You may not post new threads
• You may not post replies
• You may not post attachments
• You may not edit your posts
•