Wednesday, February 2, 2011

yogi_Return A Value Based On Entries In Other Multiple Cells

Yogi Anand, D.Eng, P.E.                                         Google Spreadsheet                            www.energyefficientbuild.com




reference:

Return a value in column D based on entries in columns A, B, and C


If Column A contains a value, "Quoted", 
if Column A an B both contain a value, "Booked" 
If column A, B & C all contain a value, "Invoiced" 
If all 3 columns are blank, the cell should be left blank

formula in cell D2

=ArrayFormula(if(len(A2:A)+len(B2:B)+Len(C2:C)=0,iferror(1/0),iferror(choose(mmult((row(A2:A)=transpose(row(A2:A)))*((A2:A<>"")+(B2:B<>"")+(C2:C<>"")),sign(row(A2:A))),"Quoted","Booked","Invoiced"))))

or

=ArrayFormula(substitute(transpose(split(concatenate(iferror(if(row(A2:A)=transpose(row(A2:A)),if(len(A2:A)+len(B2:B)+len(C2:C),choose(sign(len(A2:A))+sign(len(B2:B))+sign(len(C2:C)),"Quoted","Booked","Invoiced"),"~"),""))&char(9)),char(9))),"~",""))

these formulas should work in Excel as well by making 
adjustment for the Arrayformula
use CSE (Ctrl+Shift+Enter) instead of ArrayFormula wraparound