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

BANGA	0	1	3300
BANGA	0	2	3300
BANGA	0	3	3300
BARCEL	0	1	3300
BARCEL	0	1	3300
BARCEL	0	2	3300

<colgroup><col><col><col><col></colgroup><tbody>
</tbody>

So the Formula will return

BANGA BARCEL

<colgroup><col><col><col><col></colgroup><tbody>
</tbody>

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!