asked on # Excel - 3x way lookup (Formula help)

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;

Thanks

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

Microsoft ExcelSpreadsheetsMicrosoft Office

View this solution by signing up for a free trial.

Members can start a 7-Day free trial and enjoy unlimited access to the platform.

View this solution by signing up for a free trial.

Members can start a 7-Day free trial and enjoy unlimited access to the platform.

Either method will work

I must admit my thinking was along the lines of the first answer however the second is genius. That being said, this formula can be easily void if an array formula is not entered correctly (i.e. the outcome will be the direct opposite). As byundt stated, this

mustbe executed as an array to work properly.Many thanks