How to tell which row to chose if there are duplicates-CodePudding

I have a table below.

The Check column has a formula: =IF(AND(COUNTIF([Item],[@Item])=2,[Type]="123"),1,0). What it does is if there are duplicates in the Item column, AND if the Type column is 123, it returns 1, else 0.

I want to extract values from the Price column. However sometimes data has duplicates, and I want to get a value with Type=123 (the Red rectangle).

I created the Check column to flag which row to select, and I tried to add it as a condition. But I do not want to use Power Query for this, and use only formulas. How do I solve this?

CodePudding user response：

You can do it without check column. Try-

=IF(COUNTIF([Item],[@Item])>1,INDEX([Price],MATCH(1,([Item]=[@Item])*([Type]=123),0)),INDEX([Price],MATCH([@Item],[Item],0)))

CodePudding user response：

If you have Office 365, replace Table5 with your table name in the following:

=FILTER(Table5,(Table5[Type]=123)*(Table5[Check]=1),"")