Lookup error

[skonduru]

Hi all, I am trying to write a formula into a cell for lookup function. I am using the following line of code to write a formula to cell P3 but i am getting an error.

Please Login or Register  to view this content.

where From_Set, From_Str and so on are defined name ranges.

Any help will be greatly appreciated.

Regards,

Kond.

[NBVC]

maybe the formulas should be:

=LOOKUP(2,1/((From_Str=$B3)/(From_Set=$G3)/(Temperature=P$2)),(Mid_Span_Sag))

and

=LOOKUP(2,1/((From_Str=$B4)/(From_Set=$G4)/(Temperature=P$2)),(Horiz_Tension))

also you are missing a closing quotation (") after the second formula....

[skonduru]

Thanks NVBC. That worked.

Also, I have a quick question. I am copying the above formulas in to say 15000 rows which is taking very long time to calculate the formulae. Is there an effective way to reduce the calculation time?

Regards,

Kond.

[NBVC]

It's hard to say... those are really array formulas, and using 30,000 of them is enough to slow you down... on top of that the named range sizes will slow you down in proportion to number of rows in them....

If you have unique matches, and the "result vector" values are numeric, you might be able to use SUMIFS instead.. which is a lot faster.

e.g.

=LOOKUP(2,1/(From_Str=$B3)/(From_Set=$G3)/(Temperature=P$2),(Mid_Span_Sag)

becomes

=SUMIFS(Mid_Span_Sag,From_Str,$B3,From_Set,$G3,Temperature,P$2)

[skonduru]

Using sumif statements increased the speed little bit. Thanks.

Also, how can i use the array formula in excel formula, for example say i have the below function

=MAX(IF((From_Str_Sag_Tension_Report=$B3)*(From_Set_Sag_Tension_Report=$G3),Initial_Cond_Max_Tens_lbs))

where usually i press Ctrl+Shift+Enter after i enter the formula in a cell. How can i simulate that in VBA?

Kond.

[NBVC]

replace .Formula with .FormulaArray

[skonduru]

Please Login or Register  to view this content.

Above is my final code after. Can you suggest me if there is a way increasing the efficiency of the code?

Thanks a lot.

Kond.

[NBVC]

Unfortunately, VBA isn't really my expertise, and so I can't properly suggest code efficiency fixes... but there are many here that are proficient and can probably help.

as far as formulas go.. reducing the range sizes of your named ranges (if you are not using Dynamic Named Ranges) helps, also, if you can add a column that concatenates the 3 named ranges together, then you can reduce number of checks reduce the SUMIFS to a SUMIF and the INDEX/MATCH to a simpler non-array INDEX/MATCH...

[skonduru]

Hi all,

Can anyone help me with increasing the efficiency of the above code.

Thanks,
Sathish.