Lookup that will not duplicate already treieved code

R

ryansm05

Board Regular
Joined
Sep 14, 2016
Messages
148
Office Version
  1. 365
Platform
  1. Windows
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:

[TABLE="width: 48"]
<tbody>[TR]
[TD="width: 64, bgcolor: transparent, align: right"]10023899

[/TD]
[/TR]
[TR]
[TD="bgcolor: transparent, align: right"]10023925

[/TD]
[/TR]
</tbody>[/TABLE]



[TABLE="width: 246"]
<tbody>[TR]
[TD="width: 72, bgcolor: transparent"](A) EIN
[/TD]
[TD="width: 128, bgcolor: transparent, colspan: 2"](B)Pay_Amounts
[/TD]
[TD="width: 64, bgcolor: transparent"](C) OUC
[/TD]
[TD="width: 64, bgcolor: transparent"](D) TLC?
[/TD]
[/TR]
[TR]
[TD="bgcolor: transparent, align: right"]10023587
[/TD]
[TD="bgcolor: transparent, align: right"]3327.42
[/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"]NA
[/TD]
[TD="bgcolor: transparent"]Non-TLC
[/TD]
[/TR]
[TR]
[TD="bgcolor: transparent, align: right"]10023587

[/TD]
[TD="bgcolor: transparent, align: right"]-66.55
[/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"]NG
[/TD]
[TD="bgcolor: transparent"]Non-TLC
[/TD]
[/TR]
[TR]
[TD="bgcolor: transparent, align: right"]10023639

[/TD]
[TD="bgcolor: transparent, align: right"]-166.37
[/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"]NG
[/TD]
[TD="bgcolor: transparent"]TLC
[/TD]
[/TR]
[TR]
[TD="bgcolor: transparent, align: right"]10023639
[/TD]
[TD="bgcolor: transparent, align: right"]66.55
[/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"]NG

[/TD]
[TD="bgcolor: transparent"]TLC
[/TD]
[/TR]
[TR]
[TD="bgcolor: transparent, align: right"]10023639
[/TD]
[TD="bgcolor: transparent, align: right"]332.74
[/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"]NA
[/TD]
[TD="bgcolor: transparent"]TLC
[/TD]
[/TR]
[TR]
[TD="bgcolor: transparent, align: right"]10023639
[/TD]
[TD="bgcolor: transparent, align: right"]166.37
[/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"]NA
[/TD]
[TD="bgcolor: transparent"]TLC
[/TD]
[/TR]
[TR]
[TD="bgcolor: transparent, align: right"]10023639
[/TD]
[TD="bgcolor: transparent, align: right"]320.92
[/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"]NA
[/TD]
[TD="bgcolor: transparent"]TLC
[/TD]
[/TR]
[TR]
[TD="bgcolor: transparent, align: right"]10023639
[/TD]
[TD="bgcolor: transparent, align: right"]15.22
[/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"]NA
[/TD]
[TD="bgcolor: transparent"]TLC
[/TD]
[/TR]
[TR]
[TD="bgcolor: transparent, align: right"]10023639
[/TD]
[TD="bgcolor: transparent, align: right"]4007.5
[/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"]NK
[/TD]
[TD="bgcolor: transparent"]TLC
[/TD]
[/TR]
[TR]
[TD="bgcolor: transparent, align: right"]10023639
[/TD]
[TD="bgcolor: transparent, align: right"]-41.68
[/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"]NY
[/TD]
[TD="bgcolor: transparent"]TLC
[/TD]
[/TR]
[TR]
[TD="bgcolor: transparent, align: right"]10023639
[/TD]
[TD="bgcolor: transparent, align: right"]-5.92
[/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"]NY
[/TD]
[TD="bgcolor: transparent"]TLC
[/TD]
[/TR]
[TR]
[TD="bgcolor: transparent, align: right"]10023639
[/TD]
[TD="bgcolor: transparent, align: right"]-40.08
[/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"]NY
[/TD]
[TD="bgcolor: transparent"]TLC
[/TD]
[/TR]
[TR]
[TD="bgcolor: transparent, align: right"]10023639
[/TD]
[TD="bgcolor: transparent, align: right"]-200.38
[/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"]NK
[/TD]
[TD="bgcolor: transparent"]TLC
[/TD]
[/TR]
[TR]
[TD="bgcolor: transparent, align: right"]10023639
[/TD]
[TD="bgcolor: transparent, align: right"]40.08
[/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"]NK
[/TD]
[TD="bgcolor: transparent"]TLC
[/TD]
[/TR]
[TR]
[TD="bgcolor: transparent, align: right"]10023639
[/TD]
[TD="bgcolor: transparent, align: right"]400.75
[/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"]NK
[/TD]
[TD="bgcolor: transparent"]TLC
[/TD]
[/TR]
[TR]
[TD="bgcolor: transparent, align: right"]10023639
[/TD]
[TD="bgcolor: transparent, align: right"]200.38
[/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"]NK
[/TD]
[TD="bgcolor: transparent"]TLC
[/TD]
[/TR]
[TR]
[TD="bgcolor: transparent, align: right"]10023639
[/TD]
[TD="bgcolor: transparent, align: right"]468.98
[/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"]NK
[/TD]
[TD="bgcolor: transparent"]TLC
[/TD]
[/TR]
[TR]
[TD="bgcolor: transparent, align: right"]10023639
[/TD]
[TD="bgcolor: transparent, align: right"]400
[/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"]NK
[/TD]
[TD="bgcolor: transparent"]TLC
[/TD]
[/TR]
[TR]
[TD="bgcolor: transparent, align: right"]10023899
[/TD]
[TD="bgcolor: transparent, align: right"]20.59
[/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"]NK
[/TD]
[TD="bgcolor: transparent"]TLC
[/TD]
[/TR]
[TR]
[TD="bgcolor: transparent, align: right"]10023899
[/TD]
[TD="bgcolor: transparent, align: right"]-2
[/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"]NK
[/TD]
[TD="bgcolor: transparent"]Non-TLC
[/TD]
[/TR]
[TR]
[TD="bgcolor: transparent, align: right"]10023899
[/TD]
[TD="bgcolor: transparent, align: right"]2809.83
[/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"]NK
[/TD]
[TD="bgcolor: transparent"]TLC
[/TD]
[/TR]
[TR]
[TD="bgcolor: transparent, align: right"]10023899
[/TD]
[TD="bgcolor: transparent, align: right"]-140.49
[/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"]ND
[/TD]
[TD="bgcolor: transparent"]TLC
[/TD]
[/TR]
[TR]
[TD="bgcolor: transparent, align: right"]10023899
[/TD]
[TD="bgcolor: transparent, align: right"]280.98
[/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"]ND
[/TD]
[TD="bgcolor: transparent"]TLC
[/TD]
[/TR]
[TR]
[TD="bgcolor: transparent, align: right"]10023899
[/TD]
[TD="bgcolor: transparent, align: right"]140.49
[/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"]ND
[/TD]
[TD="bgcolor: transparent"]TLC
[/TD]
[/TR]
[TR]
[TD="bgcolor: transparent, align: right"]10023925
[/TD]
[TD="bgcolor: transparent, align: right"]268.72
[/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"]ND
[/TD]
[TD="bgcolor: transparent"]TLC
[/TD]
[/TR]
[TR]
[TD="bgcolor: transparent, align: right"]10023925
[/TD]
[TD="bgcolor: transparent, align: right"]13.33
[/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"]ND
[/TD]
[TD="bgcolor: transparent"]TLC
[/TD]
[/TR]
[TR]
[TD="bgcolor: transparent, align: right"]10023925
[/TD]
[TD="bgcolor: transparent, align: right"]-3.08
[/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"]ND
[/TD]
[TD="bgcolor: transparent"]Non-TLC
[/TD]
[/TR]
[TR]
[TD="bgcolor: transparent, align: right"]10023925
[/TD]
[TD="bgcolor: transparent, align: right"]1341.67
[/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"]ND
[/TD]
[TD="bgcolor: transparent"]TLC
[/TD]
[/TR]
[TR]
[TD="bgcolor: transparent, align: right"]10023925
[/TD]
[TD="bgcolor: transparent, align: right"]-24.97
[/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"]ND
[/TD]
[TD="bgcolor: transparent"]TLC
[/TD]
[/TR]
[TR]
[TD="bgcolor: transparent, align: right"]10023925
[/TD]
[TD="bgcolor: transparent, align: right"]-40.25
[/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"]ND
[/TD]
[TD="bgcolor: transparent"]TLC
[/TD]
[/TR]
[TR]
[TD="bgcolor: transparent, align: right"]10023925
[/TD]
[TD="bgcolor: transparent, align: right"]-67.08
[/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"]NZ
[/TD]
[TD="bgcolor: transparent"]TLC
[/TD]
[/TR]
</tbody>[/TABLE]

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

Thanks
Ryan
 
Last edited:

Excel Facts

Format cells as time
Select range and press Ctrl+Shift+2 to format cells as time. (Shift 2 is the @ sign).
Sort by date Sort by votes
I can't make any sense out of that at all! There are only two rows that match "NG" & "TLC"
[TABLE="class: cms_table, width: 246"]
<tbody>[TR]
[TD="bgcolor: transparent, align: right"]10023639
[/TD]
[TD="bgcolor: transparent, align: right"]-166.37[/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"]NG[/TD]
[TD="bgcolor: transparent"]TLC[/TD]
[/TR]
[TR]
[TD="bgcolor: transparent, align: right"]10023639[/TD]
[TD="bgcolor: transparent, align: right"]66.55[/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"]NG
[/TD]
[TD="bgcolor: transparent"]TLC[/TD]
[/TR]
</tbody>[/TABLE]
and yet the result is
[TABLE="class: cms_table, width: 48"]
<tbody>[TR]
[TD="width: 64, bgcolor: transparent, align: right"]10023899
[/TD]
[/TR]
[TR]
[TD="bgcolor: transparent, align: right"]10023925
[/TD]
[/TR]
</tbody>[/TABLE]
 
Upvote 0
Paul Ked said:
I can't make any sense out of that at all! There are only two rows that match "NG" & "TLC"
[TABLE="class: cms_table, width: 246"]
<tbody>[TR]
[TD="bgcolor: transparent, align: right"]10023639
[/TD]
[TD="bgcolor: transparent, align: right"]-166.37
[/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"]NG
[/TD]
[TD="bgcolor: transparent"]TLC
[/TD]
[/TR]
[TR]
[TD="bgcolor: transparent, align: right"]10023639
[/TD]
[TD="bgcolor: transparent, align: right"]66.55
[/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"]NG
[/TD]
[TD="bgcolor: transparent"]TLC
[/TD]
[/TR]
</tbody>[/TABLE]
and yet the result is
[TABLE="class: cms_table, width: 48"]
<tbody>[TR]
[TD="width: 64, bgcolor: transparent, align: right"]10023899

[/TD]
[/TR]
[TR]
[TD="bgcolor: transparent, align: right"]10023925

[/TD]
[/TR]
</tbody>[/TABLE]
Click to expand...

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
 
Upvote 0
You must log in or register to reply here.

Forum statistics

Threads
1,223,888
Messages
6,175,217
Members
452,619
Latest member
Shiv1198

Share this page

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top