Hello,

let's assume the table in the screenshot above has the range name ProjectList and starts in row 2. Let's also assume that the project you are looking up is specified in a cell with a range name ProjectName.

You can then use this formula:

The outer Iferror() function will return a 0 if the Match() functions inside cannot find the project name.

The inner IF() function returns a 1 only if both conditions for "Stage 3" and "Artefact #1" are true.

Index/Match will find the columns, even if the position of the columns is not fixed.

Using Index/Match is faster than SumProduct and will produce good results with large datasets.

cheers, teylyn

let's assume the table in the screenshot above has the range name ProjectList and starts in row 2. Let's also assume that the project you are looking up is specified in a cell with a range name ProjectName.

You can then use this formula:

```
=IFERROR(IF(AND(INDEX(ProjectList,MATCH(ProjectName,$A:$A,0)-1,MATCH("Stage 3",$2:$2,0))="Authorised",INDEX(ProjectList,MATCH(ProjectName,$A:$A,0)-1,MATCH("Artefact #1",$2:$2,0))="Complete"),1,0),0)
```

The outer Iferror() function will return a 0 if the Match() functions inside cannot find the project name.

The inner IF() function returns a 1 only if both conditions for "Stage 3" and "Artefact #1" are true.

Index/Match will find the columns, even if the position of the columns is not fixed.

Using Index/Match is faster than SumProduct and will produce good results with large datasets.

cheers, teylyn