Lookup value from range for corresponding row where multiple criteria are met

Hi forum members,

Please can you advise on how to make the following look up functionality in Excel 2007 work:

From a range in column A I want to display the value from the row that has the satisfied corresponding criteria in other columns.
The following does work if each criteria array is in a single column:

Please Login or Register  to view this content.

The problem I have is that data setup has changed and now the 2nd criteria (Sheet2'!D2) can be present in any column of multi-column range (on Sheet1). However, adjusting my formula to that new range (by extending column reference) results in a #value error:

Please Login or Register  to view this content.

Obviously, I doing something wrong, or need a different approach, but can't figure out how.

Hoping you can help me.

Many thanks,

Mark

Working fine for me..

=INDEX(Sheet1!$A$1:$A$50,SUMPRODUCT((Sheet1!$AR$1:$AR$50="No")*(Sheet1!$BF$1:$BF$50=Sheet2!D2)*(Sheet1!$D$1:$D$50=Sheet2!F2),ROW(Sheet1!$A$1:$A$50)))

Try this...

=INDEX(Sheet1!$A$1:$A$50,SUMPRODUCT((Sheet1!$AR$1:$AR$50="No")*COUNTIF(Sheet1!$CE$1:$CN$50,Sheet2!D2)*(Sheet1!$D$1:$D$50=Sheet2!F2),ROW(Sheet1!$A$1:$A$50)))

#REF! will not only occur when the reference has got deleted. It will also occur when the mentioned range is not in the defined range reference

For example
=INDEX(A1:A4,5) will result #REF!

The reason is that the countif is getting the result as 2 since there is two matches for 15 in D32 cell and E50 cell. The exact match is falling in 50th row.. so 2x50 = 100

=SUMPRODUCT((Sheet1!$B$1:$B$50="No")*COUNTIF(Sheet1!$D$1:$M$50,Sheet2!D2)*(Sheet1!$C$1:$C$50=Sheet2!F2),ROW(Sheet1!$A$1:$A$50))

But in Index there is only 50 rows..

=INDEX(Sheet1!$A$1:$A$50

So you are getting #REF! error

So please ignore the method which I suggested to you since it makes no sense.

This seems to work:

=INDEX(Sheet1!A:A,SUMPRODUCT((Sheet1!$D$2:$M$60=Sheet2!D2)*(Sheet1!$B$2:$B$60="no")*(Sheet1!$C$2:$C$60=Sheet2!F2)*ROW(Sheet1!$B$2:$B$60)))

You had a typo in the new formula:

=INDEX(Sheet1!A:A,SUMPRODUCT((Sheet1!$CE$2:$CN$60=Sheet2!D2)*(Sheet1!$AN$2:$AN$60="no")*(Sheet1!$D$2:$D$60=Sheet2!F2)*ROW(Sheet1!$AN$2:$AN$60)))

Using SUMPRODUCT is OK if you can be sure you have only one row which matches the criteria - if you might have more than one row which satisfies the conditions (or no rows) then you might get odd results. Try this array formula to give you the column A value for the first instance

=INDEX(Sheet1!$A$2:$A$60,SMALL(IF((Sheet1!$CE$2:$CN$60=Sheet2!D2)*(Sheet1!$AN$2:$AN$60="no")*(Sheet1!D2:D60=Sheet2!F2),ROW(Sheet1!$A$2:$A$60)-ROW(Sheet1!$A$2)+1),1))

confirmed with CTRL+SHIFT+ENTER

If there are no matches you get #NUM! error

Originally Posted by JBeaucaire

You had a typo in the new formula:

=INDEX(Sheet1!A:A,SUMPRODUCT((Sheet1!$CE$2:$CN$60=Sheet2!D2)*(Sheet1!$AN$2:$AN$60="no")*(Sheet1!$D$2:$D$60=Sheet2!F2)*ROW(Sheet1!$AN$2:$AN$60)))

Thanks for spotting AND for the solution. Now it worked and it is like a very powerful aspirin - it instantly cured my headache!
Although, I still had to apply a workaround because the data in Sheet1!$CE$2:$CN$60 isn't recognized as a number as it is the outcome of a split-single-cell-delimit-function (substitute and replace), while Sheet2!D2 is in a number format.
Therefore, I created a new range to convert the content of columns CE-CN through =IF(CE2>0,((CE2+CE2)/2),"") etc...
The working result is now:

Please Login or Register  to view this content.

And it even works with named ranges.
If you happen to have a suggestion to overcome the formatting incompatibility, then great - otherwise the current set-up will do.
Will mark this one as solved now.

Originally Posted by daddylonglegs

Using SUMPRODUCT is OK if you can be sure you have only one row which matches the criteria - if you might have more than one row which satisfies the conditions (or no rows) then you might get odd results. Try this array formula to give you the column A value for the first instance

=INDEX(Sheet1!$A$2:$A$60,SMALL(IF((Sheet1!$CE$2:$CN$60=Sheet2!D2)*(Sheet1!$AN$2:$AN$60="no")*(Sheet1!D2:D60=Sheet2!F2),ROW(Sheet1!$A$2:$A$60)-ROW(Sheet1!$A$2)+1),1))

confirmed with CTRL+SHIFT+ENTER

If there are no matches you get #NUM! error

Thank you for the suggestion. As Sheet2!F2 is the outcome of {=MAX(IF(COUNTIF(Sheet2'!D2,'Sheet1'!$CE$1:$CN$50),IF(Sheet1'$AR$1:$AR$50="No", 'Sheet1'!$D$1:$D$50))} there will always be only one row matching the criteria.
However, I did test your proposed formula in my sample file which worked well, but then in my 'production' file, packed with data, the array formula provided some odd results (i.e. row entries above the desired row, which I could not easily correct by adjusting the row reference in the formula). Hence my preference to go with JBeaucaire's solution.

Many thanks to all for your time and knowledge - much appreciated!

Maybe: Sheet1!$CO$2:$CX$6000&""=Sheet2!D2


Or...whatever formula is IN CE:CN add a +0 to the end of the formula.

=YourFormula+0