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;

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

SOLUTION

Ingeborg Hawighorst (Microsoft MVP / EE MVE)

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

ASKER CERTIFIED SOLUTION

byundt

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

just4kix

ASKER

Both tremendous answers.

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

just4kix

ASKER

Either method will work