CODEWITHSUNDEEP
if-statementgoogle-sheetsmatchmultiple-conditionsifs
Yana_ck
Yana_ck

Reputation: 1

Multiple IFS FUNCTION in Google Sheet

sample data

https://docs.google.com/spreadsheets/d/1Asd_7njduyx4ku9ZzqqBLmbLxXuZ2RBNlZvCQx12bFk/edit?usp=sharing

Referring to above sample data pictures & link, I need help make formula in Column E where the condition are as below :

  1. IF Column A = SAMPLE/DRAWING UPDATES, AND Column C = COMPLETED, search data in column B based on data in Column D, if found matches return as "Existing Data". If not found, "Updates required"

  2. IF Column A = SAMPLE/DRAWING UPDATES, AND Column C = IN PROGRESS/NOT YET STARTED, return as "Pending confirmation".

  3. IF Column A = SAMPLE/DRAWING UPDATES, AND Column C = CANCELLED/ON HOLD, return as "No action required".

I've tried below formula for No. 1, but seems like not works. I am new help. Please help. =IFS(AND(OR(A2:2="SAMPLE",A2:A="DRAWING UPDATES"),C2:C="COMPLETED"),INDEX(IF(LEN(B2:2), IF(ISNUMBER(MATCH(B2:B, D2:D, 0)), "Existing Data", "Updates required"),)))

Upvotes: 0

Views: 676

Answers (1)

FBrTeach
FBrTeach

Reputation: 38

From what I can tell, you are mixing comparing a whole range at a time versus a specific row at a time.

I'll offer two solutions that cover all the scenarios you've asked for.

Single Cell

=IF(OR(A2="SAMPLE",A2="DRAWING UPDATES"),
IFS(C2="COMPLETED",IF(IFNA(XMATCH(B2,D$2:D,0),FALSE),"Existing Data","Updates Required"),
OR(C2="IN PROGRESS",C2="NOT YET STARTED"),"Pending Confirmation",
OR(C2="CANCELLED",C2="ON HOLD"),"No action required"),
"")

You need to put this in the first cell in Column E, then use the fill handle and drag it down to the remaining cells.

Full Range

Alternatively, if you just want to put it in the column and have it apply without having to drag it down to every cell, then use the formula:

=LET(existing,D2:D,MAP(A2:A,B2:B,C2:C,LAMBDA(task,part,status,IF(OR(task="SAMPLE",task="DRAWING UPDATES"),
IFS(status="COMPLETED",IF(IFNA(XMATCH(part,existing,0),FALSE),"Existing Data","Updates Required"),
OR(status="IN PROGRESS",status="NOT YET STARTED"),"Pending Confirmation",
OR(status="CANCELLED",status="ON HOLD"),"No action required"),
""))))
  • A2:A is the Task Column.
  • B2:B is the Part No Column.
  • C2:C is the Status Column.
  • D2:D is the Existing Column.

Upvotes: 0

Related Questions