CODEWITHSUNDEEP
exceloptimizationexcel-formula
Sankalp Srivastava
Sankalp Srivastava

Reputation: 3

Reusing of formula driven result in a if statement in excel

=IFERROR(IF((INDEX(named range1,MATCH(named range2&A1,named range3,0)))<>"",INDEX(named range1,MATCH(named range2&A1,named range3,0)),"-"),"")

Here in this formula I am trying to vertical lookup a value using index-match and checking it within a if statement for blank value.If it is not blank I am using the non-blank value to set it in a given cell.

How can I optimise my formula to reduce performance overhead in excel. I don't want to use vba for this by storing the result in a variable

Upvotes: 0

Views: 1267

Answers (1)

teylyn
teylyn

Reputation: 35915

The most time consuming bit is the Match(), so avoiding a duplication of the same Match is key. You can

  • place the Index/Match in a helper cell and then use the formula

    =IFERROR(IF(B1<>"",B1,"-"),"")

This way the Index/Match will be calculated only once.

  • place the Match into a named formula. If you keep your wits about you, named formulas can work with relative cell references. Select the cell where you want the formula to go, then create a named range "NamedRange4" with the formula 

    =MATCH(named range2&A1,named range3,0)

Then use this formula in the selected cell:

=IFERROR(IF((INDEX(named range1,NamedRange4))<>"",INDEX(named range1,NamedRange4),"-"),"")

The Match will be calculated only once and the result stored in the named range. With relative cell referencing of NamedRange4, the IfError formula can be used in other cells with correct results.

Upvotes: 1

Related Questions