Return cell value with 1st 2nd 3rd highest corresponding using multiple conditions and excluding values from range

[Chris78]

Hi there,
I'm struggling to return 1st, 2nd highest value from table using multiple conditions.
In my examle attached, I would want H4 to return 'Cow' instead of 'Pig' which is already in H3

In essence, I am trying to add a condition to avoid cells already present in range to be returned.
=INDEX(B:B,MATCH(1,($F$2=A:A)*(0=D:D)*(LARGE(FILTER(C:C,(A:A=$F$2)*(D:D=0)),$G4)=C:C),0))

 

[Georgiboy]

One method - will depend on your version of Excel though, if not then i have provided the data for others to use:

Book1
	A	B	C	D	E	F	G	H
1	Week	Search Phrase	Searches	AAR		Week		Blah Blah
2	2023 W15	bird	516	56		2023 W15	1	chicken
3	2023 W15	fly	654	25			2	pig
4	2023 W15	pig	456	0			3	cow
5	2023 W15	cow	456	0
6	2023 W15	bull	789	25
7	2023 W15	chicken	654	0
8	2023 W15	ant	951	85
9	2023 W15	bear	690	15
10	2024 W16	cow	536	0
11	2024 W16	france	485	41
12	2024 W16	england	452	56
13	2024 W16	sweeden	214	85
14	2024 W16	finland	356	0
15	2024 W16	chicken	315	52
16	2024 W16	spai	541	86
17	2024 W16	italy	358	0
Sheet1

Cell Formulas
Range	Formula
H2:H4	H2	=CHOOSECOLS(TAKE(SORT(FILTER(B2:C17,(D2:D17=0)*(A2:A17=$F$2)),2,-1),3),1)
Dynamic array formulas.

 

[Fluff]

What version of Excel are you using?

I suggest that you update your Account details (or click your user name at the top right of the forum) so helpers always know what Excel version(s) & platform(s) you are using as the best solution often varies by version. (Don’t forget to scroll down & ‘Save’)

@Georgiboy
You can shorten that slightly like

=TAKE(SORT(FILTER(B2:C17,(D2:D17=0)*(A2:A17=F2)),2,-1),3,1)

 

[Georgiboy]

Thanks @Fluff - I am always learning, I had forgot the fact that TAKE also has a column part... We are spoiled with all these new functions.