Amalgamate 2x Formulas

andihawes08

Dear Forum

i have 2 formulas, which both work in independant columns. Id like to amalgamate with a further IF command:

1.

=IF([Document Full Name]@row = "", "", IF(Supplier@row = "SPL: Civils", INDEX({KO1A - BIM CAD QA - PLG Powerlines Task ID}, MATCH([Document Full Name]@row, {KO1A - BIM CAD QA - PLG Powerlines Full Name}, 0))))

2.

=IF([Document Full Name]@row = "", "", IF(Supplier@row = "AMCO: Route Clearance", INDEX({KO1A - BIM CAD QA - AMR Amco Task ID}, MATCH([Document Full Name]@row, {KO1A - BIM CAD QA - AMR AMCO Full Name}, 0))))

i thought below, which is putting Formula 2 butthis does work?>!!?

=IF([Document Full Name]@row = "", "", IF(Supplier@row = "SPL: Civils", INDEX({KO1A - BIM CAD QA - PLG Powerlines Task ID}, MATCH([Document Full Name]@row, {KO1A - BIM CAD QA - PLG Powerlines Full Name}, 0))),IF([Document Full Name]@row = "", "", IF(Supplier@row = "AMCO: Route Clearance", INDEX({KO1A - BIM CAD QA - AMR Amco Task ID}, MATCH([Document Full Name]@row, {KO1A - BIM CAD QA - AMR AMCO Full Name}, 0))))

andihawes08

i cant edit my original post, but the last comment shoudl read

"i thought below, which is appending Formula 2 to Formula 1, but this does not work?>!!?"

Mark Cronk

Hi @andihawes08 ,

Try this:

=IF([Document Full Name]@row = "", "", IF(Supplier@row = "SPL: Civils", INDEX({KO1A - BIM CAD QA - PLG Powerlines Task ID}, MATCH([Document Full Name]@row, {KO1A - BIM CAD QA - PLG Powerlines Full Name}), IF(Supplier@row = "AMCO: Route Clearance", INDEX({KO1A - BIM CAD QA - AMR Amco Task ID}, MATCH([Document Full Name]@row, {KO1A - BIM CAD QA - AMR AMCO Full Name}), 0)

You're doing 3 bested IFs. If the full name is blank it returns blank, if the supplier = SPL:Civils you're doing an index-match, if the supplier = AMCO: Route Clearance you're doing a different index-match, if none of those are true you return a 0.

Work?

Mark