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.


[TABLE="class: cms_table, width: 1274"]
<tbody>[TR]
[TD]SKU[/TD]
[TD]Description[/TD]
[TD]PK Line[/TD]
[TD]Ranking[/TD]
[TD][/TD]
[TD][/TD]
[TD]Answers Line 1[/TD]
[TD]Answers Line 2[/TD]
[TD]Answers Line 2[/TD]
[/TR]
[TR]
[TD]75069115[/TD]
[TD]Org Baby Kale 100%, 6X9 oz, BIL Clam, og[/TD]
[TD]1[/TD]
[TD]3[/TD]
[TD][/TD]
[TD][/TD]
[TD="align: right"]75069415[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]75069215[/TD]
[TD]Org Baby Kale 100%, 6X9 oz, SPEC Clam, og[/TD]
[TD]2[/TD]
[TD]2[/TD]
[TD][/TD]
[TD][/TD]
[TD="align: right"]75069315[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]75069415[/TD]
[TD]Org Baby Kale 100%, 6X9 oz, SPEC BIL Clam, og[/TD]
[TD]1[/TD]
[TD]1[/TD]
[TD][/TD]
[TD][/TD]
[TD="align: right"]75069115[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]75069315[/TD]
[TD]Org Baby Kale 100%, 6X9 oz, Clam, og[/TD]
[TD]1[/TD]
[TD]2[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]39000017[/TD]
[TD]Org Baby Spinach, 6X1 LB SPEC BIL Clam, og[/TD]
[TD]2[/TD]
[TD]2[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]39000006[/TD]
[TD]Org Baby Spinach, 6X1 LB SPEC Clam, og[/TD]
[TD]2[/TD]
[TD]1[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]39060315[/TD]
[TD]Org Baby Spinach, 6X1 LB BIL Clam, og[/TD]
[TD]3[/TD]
[TD]2[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]39061615[/TD]
[TD]Org Baby Spinach, 6X1 LB Clam, og[/TD]
[/TR]
</tbody>[/TABLE]

 

[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 !


[TABLE="width: 1274"]
<colgroup><col><col><col><col><col span="2"><col><col span="2"></colgroup><tbody>[TR]
[TD]SKU [/TD]
[TD]Description[/TD]
[TD]PK Line[/TD]
[TD]Ranking[/TD]
[TD][/TD]
[TD][/TD]
[TD]PK1[/TD]
[TD]Pk2[/TD]
[TD]Pk3[/TD]
[/TR]
[TR]
[TD]75069115[/TD]
[TD]Org Baby Kale 100%, 6X9 oz, BIL Clam, og[/TD]
[TD]PK1[/TD]
[TD]3[/TD]
[TD][/TD]
[TD][/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[/TR]
[TR]
[TD]75069215[/TD]
[TD]Org Baby Kale 100%, 6X9 oz, SPEC Clam, og[/TD]
[TD]Pk2[/TD]
[TD]2[/TD]
[TD][/TD]
[TD][/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[/TR]
[TR]
[TD]75069415[/TD]
[TD]Org Baby Kale 100%, 6X9 oz, SPEC BIL Clam, og[/TD]
[TD]Pk1[/TD]
[TD]1[/TD]
[TD][/TD]
[TD][/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[/TR]
[TR]
[TD]75069315[/TD]
[TD]Org Baby Kale 100%, 6X9 oz, Clam, og[/TD]
[TD]Pk1[/TD]
[TD]2[/TD]
[TD][/TD]
[TD][/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[/TR]
[TR]
[TD]39000017[/TD]
[TD]Org Baby Spinach, 6X1 LB SPEC BIL Clam, og[/TD]
[TD]Pk2[/TD]
[TD]2[/TD]
[TD][/TD]
[TD][/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[/TR]
[TR]
[TD]39000006[/TD]
[TD]Org Baby Spinach, 6X1 LB SPEC Clam, og[/TD]
[TD]Pk2[/TD]
[TD]1[/TD]
[TD][/TD]
[TD][/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[/TR]
[TR]
[TD]39060315[/TD]
[TD]Org Baby Spinach, 6X1 LB BIL Clam, og[/TD]
[TD]Pk3[/TD]
[TD]2[/TD]
[TD][/TD]
[TD][/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[/TR]
[TR]
[TD]39061615[/TD]
[TD]Org Baby Spinach, 6X1 LB Clam, og[/TD]
[TD]Pk3[/TD]
[TD]1[/TD]
[TD][/TD]
[TD][/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[/TR]
</tbody>[/TABLE]

 

[Marcelo Branco]

Try


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

A​

[/TD]
[TD="bgcolor: #DCE6F1"]

B​

[/TD]
[TD="bgcolor: #DCE6F1"]

C​

[/TD]
[TD="bgcolor: #DCE6F1"]

D​

[/TD]
[TD="bgcolor: #DCE6F1"]

E​

[/TD]
[TD="bgcolor: #DCE6F1"]

F​

[/TD]
[TD="bgcolor: #DCE6F1"]

G​

[/TD]
[TD="bgcolor: #DCE6F1"]

H​

[/TD]
[TD="bgcolor: #DCE6F1"]

I​

[/TD]
[/TR]
[TR]
[TD="bgcolor: #DCE6F1"]

1​

[/TD]
[TD]

SKU​

[/TD]
[TD]

Description​

[/TD]
[TD]

PK Line​

[/TD]
[TD]

Ranking​

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

PK1​

[/TD]
[TD]

PK2​

[/TD]
[TD]

PK3​

[/TD]
[/TR]
[TR]
[TD="bgcolor: #DCE6F1"]

2​

[/TD]
[TD]

75069115​

[/TD]
[TD]

Org Baby Kale 100%, 6X9 oz, BIL Clam, og​

[/TD]
[TD]

PK1​

[/TD]
[TD]

3​

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

75069415​

[/TD]
[TD]

39000006​

[/TD]
[TD]

39061615​

[/TD]
[/TR]
[TR]
[TD="bgcolor: #DCE6F1"]

3​

[/TD]
[TD]

75069215​

[/TD]
[TD]

Org Baby Kale 100%, 6X9 oz, SPEC Clam, og​

[/TD]
[TD]

Pk2​

[/TD]
[TD]

2​

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

75069315​

[/TD]
[TD]

75069215​

[/TD]
[TD]

39060315​

[/TD]
[/TR]
[TR]
[TD="bgcolor: #DCE6F1"]

4​

[/TD]
[TD]

75069415​

[/TD]
[TD]

Org Baby Kale 100%, 6X9 oz, SPEC BIL Clam, og​

[/TD]
[TD]

Pk1​

[/TD]
[TD]

1​

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

75069115​

[/TD]
[TD]

39000017​

[/TD]
[TD][/TD]
[/TR]
[TR]
[TD="bgcolor: #DCE6F1"]

5​

[/TD]
[TD]

75069315​

[/TD]
[TD]

Org Baby Kale 100%, 6X9 oz, Clam, og​

[/TD]
[TD]

Pk1​

[/TD]
[TD]

2​

[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD="bgcolor: #DCE6F1"]

6​

[/TD]
[TD]

39000017​

[/TD]
[TD]

Org Baby Spinach, 6X1 LB SPEC BIL Clam, og​

[/TD]
[TD]

Pk2​

[/TD]
[TD]

2​

[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD="bgcolor: #DCE6F1"]

7​

[/TD]
[TD]

39000006​

[/TD]
[TD]

Org Baby Spinach, 6X1 LB SPEC Clam, og​

[/TD]
[TD]

Pk2​

[/TD]
[TD]

1​

[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD="bgcolor: #DCE6F1"]

8​

[/TD]
[TD]

39060315​

[/TD]
[TD]

Org Baby Spinach, 6X1 LB BIL Clam, og​

[/TD]
[TD]

Pk3​

[/TD]
[TD]

2​

[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD="bgcolor: #DCE6F1"]

9​

[/TD]
[TD]

39061615​

[/TD]
[TD]

Org Baby Spinach, 6X1 LB Clam, og​

[/TD]
[TD]

Pk3​

[/TD]
[TD]

1​

[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD="bgcolor: #DCE6F1"]

10​

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


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.