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

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

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

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

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

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)*(REFERENCE!$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