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]

[TABLE="class: grid"]
<tbody>[TR]
[TD][/TD]
[TD]

A​

[/TD]
[TD]

B​

[/TD]
[TD]

C​

[/TD]
[TD]

D​

[/TD]
[TD]

E​

[/TD]
[TD]

F​

[/TD]
[TD]

G​

[/TD]
[TD]

H​

[/TD]
[/TR]
[TR]
[TD]

1​

[/TD]
[TD]ID[/TD]
[TD]NAME[/TD]
[TD]Days since inspection[/TD]
[TD]Ispection outcome[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]

2​

[/TD]
[TD]

1​

[/TD]
[TD]Care home name[/TD]
[TD]

436​

[/TD]
[TD]Compliant[/TD]
[TD][/TD]
[TD]ID[/TD]
[TD]Compliant[/TD]
[TD]Non compliant[/TD]
[/TR]
[TR]
[TD]

3​

[/TD]
[TD]

1​

[/TD]
[TD]Care home name[/TD]
[TD]

416​

[/TD]
[TD]Compliant[/TD]
[TD][/TD]
[TD]

1​

[/TD]
[TD]

36​

[/TD]
[TD]

111​

[/TD]
[/TR]
[TR]
[TD]

4​

[/TD]
[TD]

1​

[/TD]
[TD]Care home name[/TD]
[TD]

215​

[/TD]
[TD]Non compliant[/TD]
[TD][/TD]
[TD]

2​

[/TD]
[TD]

100​

[/TD]
[TD]

46​

[/TD]
[/TR]
[TR]
[TD]

5​

[/TD]
[TD]

1​

[/TD]
[TD]Care home name[/TD]
[TD]

111​

[/TD]
[TD]Non compliant[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]

6​

[/TD]
[TD]

1​

[/TD]
[TD]Care home name[/TD]
[TD]

36​

[/TD]
[TD]Compliant[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]

7​

[/TD]
[TD]

2​

[/TD]
[TD]Care home name[/TD]
[TD]

419​

[/TD]
[TD]Compliant[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]

8​

[/TD]
[TD]

2​

[/TD]
[TD]Anotrhe Care home name[/TD]
[TD]

46​

[/TD]
[TD]Non compliant[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]

9​

[/TD]
[TD]

2​

[/TD]
[TD]Anotrhe Care home name[/TD]
[TD]

413​

[/TD]
[TD]Compliant[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]

10​

[/TD]
[TD]

2​

[/TD]
[TD]Anotrhe Care home name[/TD]
[TD]

232​

[/TD]
[TD]Non compliant[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]

11​

[/TD]
[TD]

2​

[/TD]
[TD]Anotrhe Care home name[/TD]
[TD]

154​

[/TD]
[TD]Non compliant[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]

12​

[/TD]
[TD]

2​

[/TD]
[TD]Anotrhe Care home name[/TD]
[TD]

100​

[/TD]
[TD]Compliant[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]

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