Solved

Excel - 3x way lookup (Formula help)

Posted on 2013-11-23
4
484 Views
Last Modified: 2013-11-24
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

Sample data
0
Comment
Question by:just4kix
  • 2
4 Comments
 
LVL 50

Assisted Solution

by:Ingeborg Hawighorst
Ingeborg Hawighorst earned 250 total points
ID: 39671948
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:

=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)

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
0
 
LVL 81

Accepted Solution

by:
byundt earned 250 total points
ID: 39672229
A somewhat shorter array-entered formula would be:
=1-ISNA(MATCH(1,1/((Table1[Project_Name]=ProjectName)*(Table1[Stage 3]="Authorised")*(Table1[Artefact '#1]="Complete")),0))

The above formula assumes that your table is named Table1 and that the name of the project you are evaluating is in a named range ProjectName. This formula uses structured references, so you can move the columns as long as you don't change the header labels.

The MATCH function is looking for all three criteria to be satisfied on the same row. If so, each Boolean expression returns TRUE. When used in arithmetic operations, the TRUE is converted to a 1. So 1/(Boolean expressions) would equal 1 and the MATCH would succeed.

The ISNA function returns TRUE if the MATCH can't find a match for all three criteria. This is converted to a 1 by using it in an arithmetic expression, so the formula returns 0. If a match is found, ISNA returns FALSE and (1-FALSE) = 1

To array-enter a formula: click in the formula bar, hold Control and Shift keys down, hit Enter, release all three keys. Excel should respond by adding curly braces { } surrounding your formula.
3CriteriaQ28302253.xlsx
0
 

Author Comment

by:just4kix
ID: 39672478
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
0
 

Author Closing Comment

by:just4kix
ID: 39672480
Either method will work
0

Featured Post

Is Your Active Directory as Secure as You Think?

More than 75% of all records are compromised because of the loss or theft of a privileged credential. Experts have been exploring Active Directory infrastructure to identify key threats and establish best practices for keeping data safe. Attend this month’s webinar to learn more.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Suggested Solutions

Title # Comments Views Activity
Search for a value in Column? 5 21
VBA Array, write each column's start position into an array 17 32
Excel case statements 3 24
TSQL previous 5 23
A little background as to how I came to I design this code: Around 5 years ago I designed an add-in that formatted Excel files to a corporate standard, applying different cell colours and font type depending on whether the cells contained inputs,…
This article descibes how to create a connection between Excel and SAP and how to move data from Excel to SAP or the other way around.
This Micro Tutorial demonstrates how to create Excel charts: column, area, line, bar, and scatter charts. Formatting tips are provided as well.
This Micro Tutorial will demonstrate how to create pivot charts out of a data set. I also added a drop-down menu which allows to choose from different categories in the data set and the chart will automatically update.

914 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

19 Experts available now in Live!

Get 1:1 Help Now