Help with filtering

Hi All!

I have a moderate level of knowledge of excel.

I have been unable to complete this task successfully, and I'm sure the answer is out there. I've tried using chatgpt, but nothing has worked as of yet.

I have a large dataset in one sheet that spans columns A-W and about 6000 rows. Bascially the sheet is tracking specific transactions and in column D there are 750 account numbers. The account numbers can appear multiple times and I do not want to remove the duplicates.

I have identified about 50 accounts of interest that I have entered on sheet2.

I am looking for the easiest (lol) way to have excel identify where the numbers found on sheet2 appear in column d on sheet1. I've tried conditional formatting, and it hasn't worked yet. Honestly, I have tried a number of things.

Ultimately, I want to grab all data (columns a-w) associated with all 50 accounts every time they appear.

Any help would be amazing.

Thanks!

sample workbook.xlsx

Here is the file with a few lines of data. I had to masque some info, but hopefully this is helpful.

With regard to VBA, yes, I've used it before, with direction from ChatGPT, and was successful with it.

And where is that formula going?

I think I found some reason why they ideas i've tried have not worked. I don't know what's causing it though.

So now on the main sheet, I have copied one of the account numbers, did control F pasted the account number in, and it said there were no matches. Any suggestions why?