INDEX / MATCH with Boolean LKP_value & AND LKP

I require a INDEX/MATCH which returning a value from a TRUE Match criteria. I have used an AND for the LKP_Array as I have 2 Boolean tests. I have checked and it should be returning the value in yellow '2017 - KP1'

CodePudding user response：

If you have the newest version of Excel you can just use FILTER.

=FILTER('KP Cohorts'!$E$36:$E$4000, 
(('KP Cohorts'!$B$36:$B$4000=Sheet1!B33)*
('KP Cohorts'!$CD$36:$CD$4000>0)))

If not, I believe you can do something like the below (untested, since I don't have older versions):

=INDEX('KP Cohorts'!$E$36:$E$4000, 
MATCH(2,
--('KP Cohorts'!$B$36:$B$4000=Sheet1!B33) --('KP Cohorts'!$CD$36:$CD$4000>0)),1)