IF formula with multiple conditions using INDEX/MATCH

bump......

As I mentioned previously, I create a sample workbook and would like to upload it but have tried both Firefox and IE and am not able to upload my sample workbook.

That is why I set out a few sample tables.

Not trying to be difficult. Would like to upload if I could, but I can't seem to make it work.

Originally Posted by FDibbins

how big is the file you are trying to upload?

File size is 11kb - tiny.

If it is too big, try removing any info that is not directly related to your question

What is in the sample workbook is exactly what I have reproduced and explained in post #4 in this thread. I am not able to post exactly what is in my actual workbook (privacy issues), so I created a sample with a subset of the tables that exist in the actual workbook and changed the data as this is not relevant to the question.

if the info needed is from a formula, value the answers.

Not quite sure what you mean.

as a last attempt, try and compress/zip the file.

Have tried uploading a zip file. File upload manager just hangs.

Thank you vlady.

I have adapted the array formula in your worksheet and have come up with a solution that works.

As I can't upload, I will reproduce the problem/solution below for the benefit of others and as I have a couple of related questions.

Sample tables from entry #4 in this thread are set out below:

	A	B	C	D	E	F	G	H	I	J	K
1	Site	Status		Site	Date	Event		Site	Inspection		Criteria
2	1	OK		1	01/01/2013	Inspection		1	Completed		OK
3	2	Pending		2	03/01/2013	Inspection		2	Not required		Good
4	3	Poor		3	01/01/2013	Inspection		3	Completed		Poor
5	4	Very poor		5	00/01/1900	Inspection		4	Required		Very poor
6	5	Good						5	Pending
7	6	Assess						6	Not required

The array formula in cell I2:I7 is:

=IF(COUNTIF($K$2:$K$5,INDEX($B$2:$B$7,MATCH(TRUE,(H2=$A$2:$A$7),0)))>=1,IF(COUNTIFS($D$2:$D$5,H2,$F$2:$F$5,$I$1)=0,"Required",IF(COUNTIFS($D$2:$D$5,H2,$F$2:$F$5,$I$1,$E$2:$E$5,"=0")=1,"Pending","Completed")),"Not required")

The 3 tables are described below:

- Tracker (A1:B7): Tracks properties/attributes (eg in this case, just Status) about sites number 1 to n.

- Events (D1:F5): Tracks events that are recorded in relation to particular sites (eg in this case, whether an inspection has been completed at a given site; an entry with a date = 0 means an inspection is pending, a date > 0 represents the date the inspection was completed).

- Audit (H1:I7): Determines if events have been added/recorded correctly in the Events table as follows:

* If the status of a site is OK, Good, Poor or Very poor (ie the criteria in K2:K5), this suggests an inspection has been completed. A corresponding entry should be included in the Events table.
* If an entry has not yet been added, the formula returns "Required".
* If an entry has been added, and the date = 0 (ie 00/01/1900), the formula returns "Pending".
* If an entry has been added, and the date > 0 (ie a date is given), the formula returns "Completed".

The Audit identifies those sites for which Event records have not been, but should be added.

Related questions:
- Is it possible to use table column and row names as references in the array formula?
- If desired, is it possible to hard-code the criteria in the array formula?

Originally Posted by vlady

Your E5 is not 0
1/0/1900 -> this is 0 -> formatted as *3/14/2001

Just to clarify, I use dd/mm/yyyy date format.

Originally Posted by vlady

Related question
are you talking about named ranges?

I am mainly thinking about the case of converting the data ranges to excel tables and using table referencing in the array formula. I have found, for example, that I can substitute the reference Tracker[Status] in place of $B$2:$B$7 in the above formula, and similar names for the other range references in the formula as follows:

=IF(COUNTIF($K$2:$K$5,INDEX(Tracker[Status],MATCH(TRUE,(H2=Tracker[Site]),0)))>=1,IF(COUNTIFS(Events[Site],H2,Events[Event],Audit[[#Headers],[Inspection]])=0,"Required",IF(COUNTIFS(Events[Site],H2,Events[Event],Audit[[#Headers],[Inspection]],Events[Date],"=0")=1,"Pending","Completed")),"Not required")

What I am still unclear about is whether I can hard-code the criteria into the array formula. For example, instead of having a separate range ($K$2:$K$5) that lists the various criteria, is it possible to somehow substitute the range $K$2:$K$5 in the formula with the actual criteria values (in this case, OK, Good, Poor, Very poor)?

From my last post:

Is it possible to somehow substitute the range $K$2:$K$5 in the formula with the actual criteria values (in this case, OK, Good, Poor, Very poor)?

Have learned that what I was referring to is an array constant. I have tried to modify the formula to include an array constant - eg {OK;Good;Poor;Very poor} - but receive an error "The formula you typed contains an error."

The article at Microsoft KB214286 gives a reason for this behavior and confirms this is not possible when inserting an array constant into a SUMIF(), COUNTIF(), or COUNTBLANK() function.

The article suggests 2 alternative workarounds:

Method 1
To use the constants in a range reference, use a formula similar to the following:
=SUMIF(A1:A2,2,B1:B2)

Method 2
To use the SUM(IF()) function to enter the array, use a formula similar to the following:
=SUM(IF({1,2}=2,{1,1}))

Does anyone know how I could adapt workaround method 2 for use as an alternative to the COUNTIF() function?