Lookup that will not duplicate already treieved code

[ryansm05]

Hi,

I need a formula that will look down column D and match 'TLC' and column C for 'NG' - returning the first EIN match from column A.

I will need to drag down the formula and return the next EIN match from the above criteria, but avoiding any duplicates that have already been returned above.

The result for 'TLC' and 'NG' should be:

10023899

10023925

<tbody>
</tbody>

(A) EIN	(B)Pay_Amounts	(C) OUC	(D) TLC?
10023587	3327.42		NA	Non-TLC
10023587	-66.55		NG	Non-TLC
10023639	-166.37		NG	TLC
10023639	66.55		NG	TLC
10023639	332.74		NA	TLC
10023639	166.37		NA	TLC
10023639	320.92		NA	TLC
10023639	15.22		NA	TLC
10023639	4007.5		NK	TLC
10023639	-41.68		NY	TLC
10023639	-5.92		NY	TLC
10023639	-40.08		NY	TLC
10023639	-200.38		NK	TLC
10023639	40.08		NK	TLC
10023639	400.75		NK	TLC
10023639	200.38		NK	TLC
10023639	468.98		NK	TLC
10023639	400		NK	TLC
10023899	20.59		NK	TLC
10023899	-2		NK	Non-TLC
10023899	2809.83		NK	TLC
10023899	-140.49		ND	TLC
10023899	280.98		ND	TLC
10023899	140.49		ND	TLC
10023925	268.72		ND	TLC
10023925	13.33		ND	TLC
10023925	-3.08		ND	Non-TLC
10023925	1341.67		ND	TLC
10023925	-24.97		ND	TLC
10023925	-40.25		ND	TLC
10023925	-67.08		NZ	TLC

<tbody>
</tbody>

Apologies for not attaching the file - company restrictions prevent me from doing this.

Thanks
Ryan

 

[Paul Ked]

I can't make any sense out of that at all! There are only two rows that match "NG" & "TLC"

10023639	-166.37		NG	TLC
10023639	66.55		NG	TLC

<tbody>
</tbody>

and yet the result is

10023899

10023925

<tbody>
</tbody>

 

[ryansm05]

I can't make any sense out of that at all! There are only two rows that match "NG" & "TLC"

10023639	-166.37		NG	TLC
10023639	66.55		NG	TLC

<tbody>
</tbody>

and yet the result is

10023899

10023925

<tbody>
</tbody>

Apologies I was having real issues with constructing my message and it seems not be what I intended. You are correct in that the only match under those conditions is 10023639.

If you are familiar with a formula that would return 10023639 assuming everything else in my message made sense - that would be much appreciated.

For furher carity, even if there were 10 matches of 'NG' and 'TLC' against 10023639 I would still only expect one result from the EIN column. Likewise, if there were 12 matches of 'NG' and 'TLC' with 3 EIN's occurring 4 times each - again I would hope the formula only returns the 3 EIN's (and does not duplicate them to a total of 12).

Thanks in advance
Ryan