lookup table problem

[greg7468]

Hi all,

I had the following table with 1 machine to make a calculation. I was using multiple "if" + "and" statements, all was well but now I have to do the same with 2 machines which will exceeds the 7 if's limit.

Could you please help me. I am used to using vlookup but I am not used to having "if's" as well as "and's".

--col A---------col B----------col C---------col D
machine------tool-----------shift---------Result
----1------------1---------------1---------- + 10%
----1------------1---------------2---------- + 20%
----1------------1---------------3---------- + 30%
----1------------2--------------------------- + 40%
----1------------3--------------------------- + 50%
----2------------1---------------1---------- + 15%
----2------------1---------------2---------- + 25%
----2------------1---------------3--------- + 35%
----2------------2--------------------------- + 45%
----2------------3-------------------------- + 55%

How can I compile a formula to say that if I am looking at any combination listed I add a certain percentage to my data. i.e,

machine 1
tool 1
shift 1
I add 10% to the data

machine 2
tool 2
I add 45% to the data

I hope this is clear enough,

thanks for your help as always,

Greg.

[Gary Brown]

Could you not concatenate column A,B and C together to give you as follows:-

column D Column E

111 10%
112 20%
113 30%
12 40%
13 50%
211 15%
212 25%
213 35%
22 45%
23 55%

Then your formula would be vlookup(A1&B1&C1,D:E,2,false)

If A1 = 2, B1=1 and C1=1, then it would lookup 211 in the table in columns D:E and look in the second column for exact matches.



Gary

[greg7468]

Thank you Gary,

I never thought of doing that but it works great.

Cheers,

Greg.