Shorten if formula

[Injinia]

Hi,


Is there a way(or course there must be) to shorten the if formula in the sample workbook attached?


https://www.dropbox.com/s/xq2bervsk8y0ea8/If_formula.xlsx


Cheers,

Injinia

 

[Faseeh]

Hi Injinia,


I would perfer a SUMPRODUCT() rather then IF():

=$A3+SUMPRODUCT(($I$4:$I$6=$B3)*($C$2:$E$2=J$3)*$J$4:$L$6)

Just one formula, drag to right and down!! (Almost Four times shorter!)


Regards,

Faseeh

 

[NARAYANK991]

Hi Injinia ,


Another option :


=$A3+VLOOKUP($B3,$I$4:$L$6,MATCH(C$2,$J$3:$L$3,0)+1,FALSE)


in cell C3. Copy across and downwards.


Narayan

 

[shrivallabha]

One more using INDEX / MATCH in C3:

=$A3+INDEX($I$3:$L$6,MATCH($B3,$I$3:$I$6,0),MATCH(C$2,$I$3:$L$3,0))

Copy down and across.

 

[Injinia]

Thanks ya'll!!


Regards,

Injinia