Edgar has attended:
Excel Intermediate course
Complex logical function
Hi there,
I am trying to create a function that does following:
First Part:
Checks column A with IFERROR looking up for data in Sheet2 and if the data is missing show "Statement".
Second Part:
Column C with results from first part should also compare data with column B and only bring specific data for instance rows that consist of word "Correction" in column B therefore instead of showing statement in column C it will automatically show "Correction".
Third Part:
If the Second part is possible to combine with first then is it possible to add more criterias that should be picked up from column B and shown in clumn C instead of "Statement".
Thanks
RE: Complex logical function
Hi Edgar
Thanks for your question!
This is definitely possible, it requires using multiple nested IFs, the AND function, and the ISERROR function.
The AND function allows you to test multiple criteria, and will only show TRUE if all the tests return true.
The ISERROR function will show TRUE if a cell returns an error, FALSE if not.
Below is an example formula you could use:
=IF(AND(ISERROR(A1),B1="Correction"),"Correction",IF(ISERROR(A1),"Statement",A1))
Here we are saying:
If column A is showing an error AND column B says Correction, then show Correction.
If not - perform a test to see if Column A has an error, then show "Statement", if not just show column A as normal.
If you had more criteria to test against in column B then you can just nest further IF and AND functions, eg
IF(AND(ISERROR(A1),B1="Your example"),"Your example"
I hope this answers your problems, let me know if you have any trouble with it.
Kind Regards,
Sarah
Excel Trainer
