Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 !! |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Countif Conditions - Use of conditions that vary by cell value | Excel Discussion (Misc queries) | |||
Formulae: Paste value formulae after doing an average operation | Excel Discussion (Misc queries) | |||
Formulae to use for more that 2 conditions criteria | Excel Discussion (Misc queries) | |||
shade cells based on conditions - i have more than 3 conditions | Excel Worksheet Functions | |||
Searching TEXT in formulae, rather than results of formulae | Excel Worksheet Functions |