Aug 14 2020 05:04 AM - edited Aug 14 2020 05:05 AM
Hi
I want to find the last data on a list using multible criterias. With single criterias this work well:
=LOOKUP(2;1/(Sheet2!I:I=F1);Sheet2!N:N)
For multible criterias I tried:
=LOOKUP(2;1/(AND(Sheet2!I:I=F1;Sheet2!H:H=E1));Sheet2!N:N)
but this does not work: #DIV/0!
How can this be done in an easy way without adding additional column?
Aug 14 2020 05:17 AM
Don't use AND; the function does not work term by term on the two arrays.
1/(Sheet2!I:I=F1)/(Sheet2!H:H=E1)
AND is normally replaced by a product, "*", but since you wish to generate an error, divide by each criterion.
Aug 14 2020 05:25 AM
@Peter Bartholomew Thanks. Works perfectly :)