Lookup Table / (Excel 2003)

[braddy60]

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

[HansV]

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.

[braddy60]

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

[HansV]

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

[braddy60]

Hi Hans

I have attached the few rows from the actual Workbook.

Thanks

Braddy

[HansV]

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.

[braddy60]

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

[HansV]

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

[braddy60]

Hi Hans

Please see Attached.

Thanks for your continued patience.

Braddy

[HansV]

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

[braddy60]

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

[HansV]

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.

[sdckapr]

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",I F(AND(G2>0,H2>0),"Free/NSI","Keep")))

Steve

[braddy60]

Hi Hans

I will try to explain the precedence.

IF(C5=275,"Distr",IF(C5=265,"Distr",IF(C5="17Y","D istr",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

[braddy60]

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