Hello,
My goal is to pull the correct number in Column B if A:A matches D1 but exclude duplicates in Column B. D1 has Data validation and will change on users requests. The correct results are listed in Column E.
I tried using
{=INDEX($B$2:$B$13,MATCH(0,COUNTIF($F$1:F1,$B$2:$B$13),0))} but not sure how to add another Match to D1.
I also thought I could use the Frequency function but couldn’t build it correctly.
{=IF(SUM(IF(FREQUENCY(IF($A$2:$A$13=$D$1,$B$2:$B$13),$B$2:$B$13)>0,1))>=COLUMNS($F$1:F$1),INDEX($B$2:$B$13,SMALL(IF(FREQUENCY(IF($A$2:$A$13=$D$1,$B$2:$B$13),$B$2:$B$13),ROW($A$2:$A$13)-ROW($A$2)+1),COLUMNS($F$1:F$1))),"")}
Any help is appreciated.
Excel 2010
My goal is to pull the correct number in Column B if A:A matches D1 but exclude duplicates in Column B. D1 has Data validation and will change on users requests. The correct results are listed in Column E.
I tried using
{=INDEX($B$2:$B$13,MATCH(0,COUNTIF($F$1:F1,$B$2:$B$13),0))} but not sure how to add another Match to D1.
I also thought I could use the Frequency function but couldn’t build it correctly.
{=IF(SUM(IF(FREQUENCY(IF($A$2:$A$13=$D$1,$B$2:$B$13),$B$2:$B$13)>0,1))>=COLUMNS($F$1:F$1),INDEX($B$2:$B$13,SMALL(IF(FREQUENCY(IF($A$2:$A$13=$D$1,$B$2:$B$13),$B$2:$B$13),ROW($A$2:$A$13)-ROW($A$2)+1),COLUMNS($F$1:F$1))),"")}
Any help is appreciated.
| Excel Workbook | |||||||
|---|---|---|---|---|---|---|---|
| A | B | C | D | E | |||
| 1 | Reg | Dist | MidS | ||||
| 2 | MidS | 1 | 1 | ||||
| 3 | MidS | 1 | 2 | ||||
| 4 | MidS | 2 | #N/A | ||||
| 5 | MidS | 2 | #N/A | ||||
| 6 | North | 3 | #N/A | ||||
| 7 | North | 66 | |||||
| 8 | North | 66 | |||||
| 9 | North | 7 | |||||
| 10 | East | 4 | |||||
| 11 | East | 4 | |||||
| 12 | East | 4 | |||||
| 13 | East | 5 | |||||
Sheet3 | |||||||
