Complex logical function

Resolved · Medium Priority · Version 2016

Mon 18 Dec 2017, 09:45 Delegate Edgar said...

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

Wed 20 Dec 2017, 11:11 Trainer Sarah said...

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

Wed 27 Dec 2017: Automatically marked as resolved.

Training courses

	98.7 % recommend us
	More live stats...

Training information:

Excel tip:

Bracketed negative numbers

Often Excel users wish to display negative numbers in colour red and bracketed

Intstructions
Step1. Select Format > Cells menu options. Within Numbers tabsheet, select Category = Custom.
Step 2. Select a type such as #,##0;[Red]-#,##0;; that specifies a colour in square brackets.
Step 3. Amend as follows; #,##0;[Red](#,##0;;

Notes: Excel formatting featues are of the form
"Positive; Negative;Zero;Text" separated by semicolon.

View all Excel hints and tips