SOLVED

Excel formula Combination

%3CLINGO-SUB%20id%3D%22lingo-sub-3073567%22%20slang%3D%22en-US%22%3EExcel%20formula%20Combination%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-3073567%22%20slang%3D%22en-US%22%3E%3CP%3EHi%3C%2FP%3E%3CP%3EI%20would%20like%20to%20combine%20the%20below%205%20formulas%20in%201%20formula%2C%20how%20could%20i%20do%20it%3F%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CTABLE%20width%3D%22373%22%3E%3CTBODY%3E%3CTR%3E%3CTD%20width%3D%2272%22%3EItem%3C%2FTD%3E%3CTD%20width%3D%2279%22%3ENo.%20of%20date%3C%2FTD%3E%3CTD%20width%3D%22222%22%3EFormula%3C%2FTD%3E%3C%2FTR%3E%3CTR%3E%3CTD%3EA%3C%2FTD%3E%3CTD%3E14%3C%2FTD%3E%3CTD%3EIF(I10%3D%22A%22%2C14%2C0)%3C%2FTD%3E%3C%2FTR%3E%3CTR%3E%3CTD%3EZ%3C%2FTD%3E%3CTD%3E17%3C%2FTD%3E%3CTD%3EIF(I11%3D%22Z%22%2C17%2C0)%3C%2FTD%3E%3C%2FTR%3E%3CTR%3E%3CTD%3EJ%3C%2FTD%3E%3CTD%3E70%3C%2FTD%3E%3CTD%3EIF(I12%3D%22J%22%2C70%2C0)%3C%2FTD%3E%3C%2FTR%3E%3CTR%3E%3CTD%3EK%3C%2FTD%3E%3CTD%3E25%3C%2FTD%3E%3CTD%3EIF(I13%3D%22K%22%2C25%2C0)%3C%2FTD%3E%3C%2FTR%3E%3CTR%3E%3CTD%3EH%3C%2FTD%3E%3CTD%3E7%3C%2FTD%3E%3CTD%3EIF(I14%3D%22H%22%2C7%2C0)%3C%2FTD%3E%3C%2FTR%3E%3C%2FTBODY%3E%3C%2FTABLE%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3ETks!%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-LABS%20id%3D%22lingo-labs-3073567%22%20slang%3D%22en-US%22%3E%3CLINGO-LABEL%3EExcel%3C%2FLINGO-LABEL%3E%3CLINGO-LABEL%3EFormulas%20and%20Functions%3C%2FLINGO-LABEL%3E%3C%2FLINGO-LABS%3E
New Contributor

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!

5 Replies
Perhaps
=Switch(I10, "A", 14, "Z", 17, "J", 70, "K", 25, "H", 7, 0)
best response confirmed by Hans Vogelaar (MVP)
Solution
Maybe:

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

No switch at my excel, tks
It’s useful, tks!

@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]