Functions in programming

A

ashoulds

Does anyone know how many functions excel can handle in a single cell? I
have a pretty long formula that searches three tables for values and need to
add three more variables that will search three separate tables.
 
A

ashoulds

Here is a copy of the code.I need to add code that will use values for b6= 2,
3, 4 and the arrays for the tables in the vlookupfunction will change as
well. Just wondering if it would actually work.


=(IF(AND(J8=1,B6=1),IF(ISNA(VLOOKUP(E19,'Calculations
2'!B3:C64,2,0)),INDEX('Calculations
2'!B3:C64,MATCH(Calculations!E19,'Calculations
2'!B3:B64,1)+1,2),VLOOKUP(E19,'Calculations
2'!B2:C64,2,0)),IF(AND(J8=2,B6=1),IF(ISNA(VLOOKUP(E19,'Calculations
2'!I3:J64,2,0)),INDEX('Calculations
2'!I3:J64,MATCH(Calculations!E19,'Calculations
2'!I3:I64,1)+1,2),VLOOKUP(E19,'Calculations
2'!I3:J64,2,FALSE)),IF(AND(J8=3,B6=1),IF(ISNA(VLOOKUP(E19,'Calculations
2'!Q3:R64,2,0)),INDEX('Calculations
2'!Q3:R64,MATCH(Calculations!E19,'Calculations
2'!Q3:Q64,1)+1,2),VLOOKUP(E19,'Calculations 2'!Q3:R64,2,0))))))

I need to add code that will use values for b6= 2, 3, 4 and the arrays for
the tables in the vlookupfunction will change as well. Just wondering i
 
J

John C

May I make a recommendation, first, create a table elsewhere, that will
determine the 'table' that you will be utilizing (B3:C64, I3:J64, Q3:R64,
etc..)
Also, I noticed 2 discrepancies in your formula as given. All your table
references are in the above mentioned format, except you have one reference
to B2:C64, and another reference to Q3:Q64; I am assuming these are errors.
I created another worksheet, called Tables, and in A1:B10, I typed the
following:
11 B3:C64
21 I3:J64
31 Q3:R64
etc..., down as far as needed. Also assuming that J8 & B6 are single digits,
the values 11, 21, 31, are essentially your J8 & B6 values just combined.
Once that is done, you should be able to do a formula like this, note this
was tested only partially, but I think you can see where I am going with it:

=IF(COUNTIF(Tables!$A$1:$A$10,--(J8&B6))=0,"",IF(ISNA(VLOOKUP(E19,INDIRECT("'Calculations2'!"&VLOOKUP(--(J8&B6),Tables!$A$1:$B$10,2,FALSE)),2,0)),INDEX(INDIRECT("'Calculations2'!"&VLOOKUP(--(J8&B6),Tables!$A$1:$B$10,2,FALSE)),MATCH(Calculations!E19,INDIRECT("'Calculations2'!"&VLOOKUP(--(J8&B6),Tables!$A$1:$B$10,2,FALSE)),1)+1,2),VLOOKUP(E19,INDIRECT("'Calculations2'!"&VLOOKUP(--(J8&B6),Tables!$A$1:$B$10,2,FALSE)),2,0)))

You can obviously adjust the reference to Tables!$A$1:$B$10 as needed
depending on where you setup the table at.

Hope this helps.
 
R

Ron Rosenfeld

Does anyone know how many functions excel can handle in a single cell? I
have a pretty long formula that searches three tables for values and need to
add three more variables that will search three separate tables.
Click to expand...

Check HELP for your Excel under Specifications.

There are nesting limits (different for different versions), and limits on the
length (number of characters) of a formula. I don't believe there are limits
as to the number of functions.
--ron
 
Top