Hi,

Need some assistance trying to complete a multi-lookup formula (and avoiding the SUMPRODUCT function (it's slow and the dataset will be of large size)).

I've attached some sample data for reference. The columns may change position so using the VLOOKUP function with fixed columns won't suffice.

The requirement is the following;

Look up a certain project - let's use 'A3' (Project A)

If the project exists, then lookup 'D2' (Stage 3) intersection/corresponding value to see if the value is equal to 'Authorised'

If the project exists then lookup 'E2' (Artefact #1) intersection/corresponding value to see if the value is equal to 'Complete'

If all of the above, the test is satisfied so output the number '1', if false, output '0'

Thanks

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:

Open in new window

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