how to use a IF(AND) formulae for more than seven conditions
 

I have 4 variables which give me 34 possible conditions.(A data with 4
columns and 34 rows).
The fifth column consists of an answer.
I need a formula, which gives me the answer if i enter my four variables.
I tried using IF(AND.....) but it can take only seven conditions whereas i
have 34 !!

how to use a IF(AND) formulae for more than seven conditions
 

Mohit wrote:
I have 4 variables which give me 34 possible conditions.(A data with 4
columns and 34 rows).
The fifth column consists of an answer.
I need a formula, which gives me the answer if i enter my four
variables. I tried using IF(AND.....) but it can take only seven
conditions whereas i have 34 !!


Till Excel 2003 you can have just 7 IF, while from Excel 2007 there would be
the possibilty to use more than 7.

I think you could use the VLOOKUP function, instead of IF.

--
Hope I helped you.

Thanks in advance for your feedback.

Ciao

Franz Verga from Italy

how to use a IF(AND) formulae for more than seven conditions
 

Hi,
First of all, congrats for the victory day before yesterday.
VLOOKUP function is only for one variable, i.e i can lookup for one variable
whereas i have 4.
Pls suggest

Thnx
Mohit

"Franz Verga" wrote:

Mohit wrote:
I have 4 variables which give me 34 possible conditions.(A data with 4
columns and 34 rows).
The fifth column consists of an answer.
I need a formula, which gives me the answer if i enter my four
variables. I tried using IF(AND.....) but it can take only seven
conditions whereas i have 34 !!


Till Excel 2003 you can have just 7 IF, while from Excel 2007 there would be
the possibilty to use more than 7.

I think you could use the VLOOKUP function, instead of IF.

--
Hope I helped you.

Thanks in advance for your feedback.

Ciao

Franz Verga from Italy

how to use a IF(AND) formulae for more than seven conditions
 

Maybe you could post some example of your data or upload an example file to
www.savefile.com


Mohit wrote:
Hi,
First of all, congrats for the victory day before yesterday.
VLOOKUP function is only for one variable, i.e i can lookup for one
variable whereas i have 4.
Pls suggest

Thnx
Mohit

"Franz Verga" wrote:

Mohit wrote:
I have 4 variables which give me 34 possible conditions.(A data
with 4 columns and 34 rows).
The fifth column consists of an answer.
I need a formula, which gives me the answer if i enter my four
variables. I tried using IF(AND.....) but it can take only seven
conditions whereas i have 34 !!


Till Excel 2003 you can have just 7 IF, while from Excel 2007 there
would be the possibilty to use more than 7.

I think you could use the VLOOKUP function, instead of IF.

--
Hope I helped you.

Thanks in advance for your feedback.

Ciao

Franz Verga from Italy

--
(I'm not sure of names of menus, options and commands, because
translating from the Italian version of Excel...)

Hope I helped you.

Thanks in advance for your feedback.

Ciao

Franz Verga from Italy

how to use a IF(AND) formulae for more than seven conditions
 

Hi,
I've uploaded the file
http://www.savefile.com/files/8465403
Two sheets: One is reference and the second is data
I want to enter the formula in G2 to G17 so that it can pick up values from
the "REFERENCE" sheet.

Regs
Mohit

"Franz Verga" wrote:

Maybe you could post some example of your data or upload an example file to
www.savefile.com


Mohit wrote:
Hi,
First of all, congrats for the victory day before yesterday.
VLOOKUP function is only for one variable, i.e i can lookup for one
variable whereas i have 4.
Pls suggest

Thnx
Mohit

"Franz Verga" wrote:

Mohit wrote:
I have 4 variables which give me 34 possible conditions.(A data
with 4 columns and 34 rows).
The fifth column consists of an answer.
I need a formula, which gives me the answer if i enter my four
variables. I tried using IF(AND.....) but it can take only seven
conditions whereas i have 34 !!


Till Excel 2003 you can have just 7 IF, while from Excel 2007 there
would be the possibilty to use more than 7.

I think you could use the VLOOKUP function, instead of IF.

--
Hope I helped you.

Thanks in advance for your feedback.

Ciao

Franz Verga from Italy

--
(I'm not sure of names of menus, options and commands, because
translating from the Italian version of Excel...)

Hope I helped you.

Thanks in advance for your feedback.

Ciao

Franz Verga from Italy

how to use a IF(AND) formulae for more than seven conditions
 

Hi Mohit,

I think you have two way to solve your problem.

The first one is to insert a first column in the REFERENCE sheet, copy the
formula =B2&C2&D2&E2 from A2 to A17 and then use the VLOOKUP formula to pick
up values to DATA sheet:

=VLOOKUP(DATA!B2&DATA!C2&DATA!D2&DATA!E2,REFERENCE !$A$2:$F$35,6,0)

The second way don't need any extra column and is the use of a SUMPRODUCT
function instead of VLOOKUP:

=SUMPRODUCT((REFERENCE!$A$2:$A$35=DATA!B2)*(REFERE NCE!$B$2:$B$35=DATA!C2)*(REFERENCE!$C$2:$C$35=DATA !D2)*(REFERENCE!$D$2:$D$35=DATA!E2)*(REFERENCE!$E$ 2:$E$35))

In both formulas I assume you want to pick up the cost value from REFERENCE
sheet.




Mohit wrote:
Hi,
I've uploaded the file
http://www.savefile.com/files/8465403
Two sheets: One is reference and the second is data
I want to enter the formula in G2 to G17 so that it can pick up
values from the "REFERENCE" sheet.

Regs
Mohit

"Franz Verga" wrote:

Maybe you could post some example of your data or upload an example
file to www.savefile.com


Mohit wrote:
Hi,
First of all, congrats for the victory day before yesterday.
VLOOKUP function is only for one variable, i.e i can lookup for one
variable whereas i have 4.
Pls suggest

Thnx
Mohit

"Franz Verga" wrote:

Mohit wrote:
I have 4 variables which give me 34 possible conditions.(A data
with 4 columns and 34 rows).
The fifth column consists of an answer.
I need a formula, which gives me the answer if i enter my four
variables. I tried using IF(AND.....) but it can take only seven
conditions whereas i have 34 !!


Till Excel 2003 you can have just 7 IF, while from Excel 2007 there
would be the possibilty to use more than 7.

I think you could use the VLOOKUP function, instead of IF.

--
Hope I helped you.

Thanks in advance for your feedback.

Ciao

Franz Verga from Italy

--
(I'm not sure of names of menus, options and commands, because
translating from the Italian version of Excel...)

Hope I helped you.

Thanks in advance for your feedback.

Ciao

Franz Verga from Italy

--
(I'm not sure of names of menus, options and commands, because
translating from the Italian version of Excel...)

Hope I helped you.

Thanks in advance for your feedback.

Ciao

Franz Verga from Italy