LOOKUP with multiple matches, and how to use criteria to return single value

[MarkWilberforce]

Hello. I am a first time poster here.

I am used to using lookups, but stuck with moving to a situation where I have multiple matches and want to apply a criteria to return a single value I am interested in.

The data below show some inspection data for care home quality, which include some very old reports mixed in. I want to find, for each care home, the most recent inspection result (whether they were compliant or not)

Image of data is here: https://ibb.co/gCH8vw

So, for example, I will set up a new sheet with just one entry for each care home. For care home #1 , I would like a lookup formula to return cell D6 (the most recent inspection outcome). For care home #2 , it would be D8. And so on.

I would like to know the best formula-based approach for solving this. There will be some other IF statements I add later using other variables too, so I don't want to find a workaround that sorts the data.

Any help very gratefully received.

Thank you

Mark

 

[Special-K99]

in E2
=INDEX(D$2:D$12,MATCH(MIN(IF(A$2:A$12=A2,C$2:C$12)),C$2:C$12,0))
Array formula, use Ctrl-Shift-Enter

and copy down column E

 

[MARZIOTULLIO]

	A​	B​	C​	D​	E​	F​	G​	H​
1​	ID	NAME	Days since inspection	Ispection outcome
2​	1​	Care home name	436​	Compliant		ID	Compliant	Non compliant
3​	1​	Care home name	416​	Compliant		1​	36​	111​
4​	1​	Care home name	215​	Non compliant		2​	100​	46​
5​	1​	Care home name	111​	Non compliant
6​	1​	Care home name	36​	Compliant
7​	2​	Care home name	419​	Compliant
8​	2​	Anotrhe Care home name	46​	Non compliant
9​	2​	Anotrhe Care home name	413​	Compliant
10​	2​	Anotrhe Care home name	232​	Non compliant
11​	2​	Anotrhe Care home name	154​	Non compliant
12​	2​	Anotrhe Care home name	100​	Compliant

<tbody>
</tbody>

g3=MIN(IF($A$2:$A$12=$F3,IF($D$2:$D$12=G$2,$C$2:$C$12))) control+shift+enter copy across and down

 

[MarkWilberforce]

in E2
=INDEX(D$2:D$12,MATCH(MIN(IF(A$2:A$12=A2,C$2:C$12)),C$2:C$12,0))
Array formula, use Ctrl-Shift-Enter

and copy down column E

Perfect - thank you indeed - saved me a LOT of time with this