HELP - Excel Index Match Countif two criteria

[Eugenio1991]

Good Day All ,
Need Help , Im trying to write a formula that will return sorted values base on two criteria, currently working with one . Example is below,

Data is from Sku to Ranking . Formula is written on the Answers line..
i need the formula to return the values per "PK" line base on the ranking given.

Current formula i use is "=IFERROR(INDEX($A$3:$A$250, MATCH(0, IF("Pk10Y"=$AF$3:$AF$250, COUNTIF($AJ$14:AJ22, $A$3:$A$250), ""), 0))," ") but only work for the Pk criteria and need to add the ranking.

any help if welcome .
Thank You.

SKU	Description	PK Line	Ranking			Answers Line 1	Answers Line 2	Answers Line 2
75069115	Org Baby Kale 100%, 6X9 oz, BIL Clam, og	1	3			75069415
75069215	Org Baby Kale 100%, 6X9 oz, SPEC Clam, og	2	2			75069315
75069415	Org Baby Kale 100%, 6X9 oz, SPEC BIL Clam, og	1	1			75069115
75069315	Org Baby Kale 100%, 6X9 oz, Clam, og	1	2
39000017	Org Baby Spinach, 6X1 LB SPEC BIL Clam, og	2	2
39000006	Org Baby Spinach, 6X1 LB SPEC Clam, og	2	1
39060315	Org Baby Spinach, 6X1 LB BIL Clam, og	3	2
39061615	Org Baby Spinach, 6X1 LB Clam, og

<tbody>
</tbody>

 

[Marcelo Branco]

I think your data have some problems
1. The last line is empty
Shouldn't it be: PK Line = 3 and Rank = 1?

2. There is a tie in the Ranking on lines 3 and 6.
Both lines contain PK Line 2 and Ranking 2
Is this correct?

M.

 

[Eugenio1991]

I think your data have some problems
1. The last line is empty
Shouldn't it be: PK Line = 3 and Rank = 1?

2. There is a tie in the Ranking on lines 3 and 6.
Both lines contain PK Line 2 and Ranking 2
Is this correct?

M.

1: The data is just and example that i made just to explain what i'm trying to do.

2: Yeah that's also just and Example just to be simplified.

I looking for the formula to give me each sku for each line with the condition of the rank.

like i said i have one working for the line but i'm unable to add the other function.

I've revised the data see below. thank you !

SKU	Description	PK Line	Ranking			PK1	Pk2	Pk3
75069115	Org Baby Kale 100%, 6X9 oz, BIL Clam, og	PK1	3
75069215	Org Baby Kale 100%, 6X9 oz, SPEC Clam, og	Pk2	2
75069415	Org Baby Kale 100%, 6X9 oz, SPEC BIL Clam, og	Pk1	1
75069315	Org Baby Kale 100%, 6X9 oz, Clam, og	Pk1	2
39000017	Org Baby Spinach, 6X1 LB SPEC BIL Clam, og	Pk2	2
39000006	Org Baby Spinach, 6X1 LB SPEC Clam, og	Pk2	1
39060315	Org Baby Spinach, 6X1 LB BIL Clam, og	Pk3	2
39061615	Org Baby Spinach, 6X1 LB Clam, og	Pk3	1

<colgroup><col><col><col><col><col span="2"><col><col span="2"></colgroup><tbody>
</tbody>

 

[Marcelo Branco]

Try

	A​	B​	C​	D​	E​	F​	G​	H​	I​
1​	SKU​	Description​	PK Line​	Ranking​			PK1​	PK2​	PK3​
2​	75069115​	Org Baby Kale 100%, 6X9 oz, BIL Clam, og​	PK1​	3​			75069415​	39000006​	39061615​
3​	75069215​	Org Baby Kale 100%, 6X9 oz, SPEC Clam, og​	Pk2​	2​			75069315​	75069215​	39060315​
4​	75069415​	Org Baby Kale 100%, 6X9 oz, SPEC BIL Clam, og​	Pk1​	1​			75069115​	39000017​
5​	75069315​	Org Baby Kale 100%, 6X9 oz, Clam, og​	Pk1​	2​
6​	39000017​	Org Baby Spinach, 6X1 LB SPEC BIL Clam, og​	Pk2​	2​
7​	39000006​	Org Baby Spinach, 6X1 LB SPEC Clam, og​	Pk2​	1​
8​	39060315​	Org Baby Spinach, 6X1 LB BIL Clam, og​	Pk3​	2​
9​	39061615​	Org Baby Spinach, 6X1 LB Clam, og​	Pk3​	1​
10​

<tbody>
</tbody>

Array formula in G2 copied across and down
=IFERROR(INDEX($A:$A,SMALL(IF(ISNA(MATCH($A$2:$A$9,G$1:G1,0)),IF($C$2:$C$9=G$1,IF($D$2:$D$9=MIN(IF(ISNA(MATCH($A$2:$A$9,G$1:G1,0)),IF($C$2:$C$9=G$1,$D$2:$D$9))),ROW($A$2:$A$9)))),1)),"")
confirmed with Ctrl+Shift+Enter, not just Enter

M.

 

[Eugenio1991]

thank you . This helps a lot.

 

[Marcelo Branco]

You are welcome. Glad to help

M.