Lookup Table / (Excel 2003)

Topic

New Reply

Hi

Hans kindly showed me how to create a lookup table, I am trying to re-create it in the attached workbook rows 5,6,7 work then I get a #ref error.

Many Thanks

Braddy

Reply | Quote

Replies

In G5 to G7, VLOOKUP(A6,status,2,0) evaluates to “distr” which is the name of a defined range (see Insert | Name | Define). Hence INDIRECT(VLOOKUP(A6,status,2,0)) results in a reference to the “distr” range.
In G8, however, VLOOKUP(A6,status,2,0) evaluates to “Dump Acc” which is NOT the name of a defined range. Hence INDIRECT(VLOOKUP(A6,status,2,0)) results in a #REF error.

I don’t understand what you want to accomplish with this formula. so I cannot suggest an improvement.

Reply | Quote

Hi Hans

What I am trying to achieve is the following.

If Mkt Sector = 275 or 265 or 17Y, or 56 then Status should = Distr, If Key Account3 = 17675, or another 13 numbers then status should = Dump Acc, If QS>0 and NIV = 0 then Status should = Free/NSI, else Keep.

I hope you can understand this

Thanks

Braddy

Reply | Quote

What are Mkt Sector, Key Account3, QS and NIV? There is no mention of them anywhere in the spreadsheet…

Reply | Quote

Hi Hans

I have attached the few rows from the actual Workbook.

Thanks

Braddy

Reply | Quote

Sorry, I still don’t understand. In row 3 of the workbook, Key Accounts 3 is 17675, so according to your description, Status should be “Dump Acc”, but in the same row, Mkt Sector is 275, so Status should be “Distr”. I’m confused.

Reply | Quote

Hi Hans

Thats because they were entered manually this is why I would like the code, I am looking for to automate them.

I have attached again with nothing in the status.

Thanks

Braddy

Reply | Quote

Still confused. Your description of what the status should be is not clear.

Reply | Quote

Hi Hans

Please see Attached.

Thanks for your continued patience.

Braddy

Reply | Quote

Should the second and third IF in your description be ELSE IF ?

Reply | Quote

Hi Hans

I am not really sure but it sounds like it should because the all the ifs are required in each row ???

Regards

Braddy

Reply | Quote

Unless you can give a clear and unambiguous description of the precedence of the various rules, it is impossible to come up with a solution.

Reply | Quote

Hi Hans

I will try to explain the precedence.

IF(C5=275,”Distr”,IF(C5=265,”Distr”,IF(C5=”17Y”,”Distr”,IF(C5=56,”Distr”,IF(and(G5>0 H5=0,”Free/NSI”,”Keep”))))))

Keep is if none of the IF’s apply

I have left out the Key Account3 because there are to many Variables

Sorry to try your patience

Many Thanks

Braddy

Reply | Quote

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

Reply | Quote

Hi Rory

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

Grateful Thanks

Braddy

Reply | Quote

Honestly, I don’t know. I used 50 as a test just now without problems but there might be issues if the formula gets too long.

Reply | Quote

Hi Rory

I will test it to destruction

Thanks

Braddy

Reply | Quote

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

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

or even:
=IF(isnumber(Match(A2,,0)),”Dump Acc”,IF(isnumber(match(d2,,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

Reply | Quote

Hi Steve

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

Braddy

Reply | Quote

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

Reply | Quote

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

Reply | Quote

How about this. First check is in COl A, if not a match then chec in D if not A or D, then check the G.H combo, if none then use the else:

=IF(OR(A2=17675,A2=17933,A2=18244),”Dump Acc”,IF(OR(D2=275,D2=265,D2=”17Y”,D2=56),”Distr”,IF(AND(G2>0,H2>0),”Free/NSI”,”Keep”)))

Steve

Reply | Quote

Hi Steve

That works just fine, I wonder if I could impose on you to take out the the IF(OR(A2=17675,A2=17933,A2=18244),”Dump Acc, as there will be a lot more numbers added to this

and I will have to use a seperate column for this.

Many Thanks

Braddy

Reply | Quote

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.

Reply | Quote