Forum Discussion

QueenieMak_qmsy's avatar
QueenieMak_qmsy
Copper Contributor
Jan 27, 2022
Solved

Excel formula Combination

Hi

I would like to combine the below 5 formulas in 1 formula, how could i do it?

 

ItemNo. of dateFormula
A14IF(I10="A",14,0)
Z17IF(I11="Z",17,0)
J70IF(I12="J",70,0)
K25IF(I13="K",25,0)
H7IF(I14="H",7,0)

 

Tks!

excel
Formulas and Functions
  • dewandi's avatar
    dewandi
    Jan 27, 2022
    Maybe:

    =IF(I10="A",14,IF(I10="Z",17,IF(I10="j",70,IF(I10="K",25,IF(I10="H",7,0)))))

5 Replies

  • PeterBartholomew1's avatar
    PeterBartholomew1
    Silver Contributor
    Jan 27, 2022

    QueenieMak_qmsy 

    It is often possible to replace nested IFs by a lookup

    = LOOKUP(target,
  {7,10,14,17,25,70;0,
  "A","Z","J","H","K"})

     [XLOOKUP is my favourite but I had to dump my old Excel to get that]

  • dewandi's avatar
    dewandi
    Copper Contributor
    Jan 27, 2022
    Maybe:

    =IF(I10="A",14,IF(I10="Z",17,IF(I10="j",70,IF(I10="K",25,IF(I10="H",7,0)))))

  • JMB17's avatar
    JMB17
    Bronze Contributor
    Jan 27, 2022
    Perhaps
    =Switch(I10, "A", 14, "Z", 17, "J", 70, "K", 25, "H", 7, 0)

Resources