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
![Sample data]()
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
If the project exists then lookup 'E2' (Artefact #1) intersection/corresponding
If all of the above, the test is satisfied so output the number '1', if false, output '0'
Thanks
Microsoft ExcelSpreadsheetsMicrosoft Office
Last Comment
just4kix
SOLUTION
THIS SOLUTION ONLY AVAILABLE TO MEMBERS.
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.
ASKER CERTIFIED SOLUTION
THIS SOLUTION ONLY AVAILABLE TO MEMBERS.
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.
ASKER
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