If statements with different input cells and more than two outcomes - If ? Or?

[lelrich]

What I want to do is in the attached Excel worksheet. i provide data to a call centre. The data is exported from our asset management system.

The data fields output from the asset management system are \
Pno, Project and ProjectType. I use formulae to populate the subsequent fields.

All projects which are specific types. CCTV is usually allocated to a specific subcontractor P&D. One project is not a CCTV project but needs to be handled similarly - p2043j. It was easy when this project was to be allocated to the P&D rota, I just used a formulae with an OR to make it a ProjectNote CCTV type.

Now P4043J it has to be handled by another subcontractor and another project P2186C has also to be handled by a different subcontractor.

So I want to set up a formula to set conditions based on specific project numbers (Pno) and Project Types to output the correct result to the Project Note.

Do I use IFs nested or is there another way I can do this?


I attach the data. It's my first time on this forum so excuse me if I am not supplying enough information.

[TMS]

If you set up a little lookup table somewhere on the sheet, or on another sheet (ideally), you can use VLOOKUP to get the necessary details. This is probably more scaleable than Nested IFs with ANDs and ORs.

For example, I1 to K3:

Then D2: =IFERROR(VLOOKUP($A2,$I$2:$K$3,2,FALSE),IF($C2="CCTV",$C2,"normal"))
And E2: =IFERROR(VLOOKUP($A2,$I$2:$K$3,3,FALSE),IF($C2="CCTV","P and D Rota","Email only"))

copy both formulae down.


Regards, TMS

[bebo021999]

3 IFs for Project note:

Please Login or Register  to view this content.

2 IFs for Outofhourscall:

Please Login or Register  to view this content.

Hope this help!

[lelrich]

This is great, Quang! I only have one other formula to sort out. This is the last column for out of hours cover. Basically if ProjectNote CONTAINS the string "CCTV" then out of hours cover should be YES.