I cell conains then return 3 cells backwards exclude Duplicates formula needed

[menor59]

Hello all i hope your sunday is well

I have a table that in column E i have 3300, 2220, 1300, and 2000

what i need to get is...

iF E contains 3300 then REturn Range B once

ie.

B C D E
[TABLE="width: 646"]
<colgroup><col><col><col><col></colgroup><tbody>[TR]
[TD]BANGA[/TD]
[TD]0[/TD]
[TD]1[/TD]
[TD]3300[/TD]
[/TR]
[TR]
[TD]BANGA[/TD]
[TD]0[/TD]
[TD]2[/TD]
[TD]3300[/TD]
[/TR]
[TR]
[TD]BANGA[/TD]
[TD]0[/TD]
[TD]3[/TD]
[TD]3300[/TD]
[/TR]
[TR]
[TD]BARCEL[/TD]
[TD]0[/TD]
[TD]1[/TD]
[TD]3300[/TD]
[/TR]
[TR]
[TD]BARCEL[/TD]
[TD]0[/TD]
[TD]1[/TD]
[TD]3300[/TD]
[/TR]
[TR]
[TD]BARCEL[/TD]
[TD]0[/TD]
[TD]2[/TD]
[TD]3300
[/TD]
[/TR]
</tbody>[/TABLE]

So the Formula will return
[TABLE="width: 646"]
<colgroup><col><col><col><col></colgroup><tbody>[TR]
[TD]BANGA
BARCEL[/TD]
[/TR]
</tbody>[/TABLE]


Thoughts or comments?

 

[JustynaMK]

Hi,

Try using the Array formula (Ctrl+Shift+Enter) and drag it down:
=IFERROR(INDEX($B$1:$B$6,MATCH(0,COUNTIF($F$1:F1,IF($E$1:$E$6=3300,$B$1:$B$6,"")),0)),"")

Your formula needs to be pasted into cell F2 (2nd row) due to $F$1:F1 count. The whole structure is nicely explained in the following post:
https://exceljet.net/formula/extract-unique-items-from-a-list

Hope it helps!