Populating table with data from another table in the same workbook based on criteria (VBA)

Can some one help me with the VBA coding? I am trying to figure out how to populate a table with the data from another table on the different sheet based on criteria entered in the cell. I want to be able enter company ID into cell B2 (Please see Example attached) on Sheet 1 and have Table 1 (Sheet 1) populated with Employee ID's (cells starting A7 through how many there are employee ID's in the Table 2 on the Sheet 2). I want this command to be executed after I type in company ID and press enter. For example, if I enter ABC into cell B2 and press enter I would like to see EE ID 001736727 in the Cell A7, 690608470 in the cell A8 and 113506877 in the cell A9. I have up to 2000 ID's in the real table. Can some one please help me with this?

Alan, thank you for the UDF function. I have to enter MLOOKUP formula into each cell. I have several thousands of cells with formulas. This makes it really slow to update. I currently have a different excel formula in the cell that works great but takes over 2 minutes to update. I was looking for the VBA code that will allow me avoid using formulas in the spreadsheet and save time on updating the data. If anyone knows how to do this with the VBA please help.

Hi alextol,
try this

Please Login or Register  to view this content.

Nilem, thank you! This does exactly what I was looking for! I was wondering if you could help me modify your code a little bit. I want to be able to pull information not for one column only (Employee ID) but for several different columns with information pertaining to the same employee. Please see attached example Table 2, Sheet 2. When the Company ID is entered on the Sheet 1, ABC. I want to see all EE's ID's (like the code already does) plus Employee City and Premium. I want to skip Employee State on purpose (Table 2 in huge and I only need specific columns from it). The result should look like Table 1 on the Sheet 1. Can you please help me with that? I'll be much obliged.

Hi alextol,
try again

Please Login or Register  to view this content.

PS в Хабаровске не все знают русский язык?

Spasibo za code! Seichas poprobuy evo. U menya net russkova shrifta.

I don't know much about VBA. Can you please explain to me this line of code: Range("A7:C7", Cells(Rows.Count, 1).End(xlUp)(2, 1)).ClearContents I understand that it clears the content so new content can be loaded. I just need to know what "count, 1" row mean and "(2, 1)" coordinates (I assume, row and column) do.

Cells(Rows.Count, 1) this is the last cell on the sheet in the first column (A1048576 cell)
Cells(Rows.Count, 1).End(xlUp) it goes to the first filled cell in column A (analogue Ctrl+UpArrow)
InitialCell(2, 1) is a cell in the second row and the first column relative to the initial cell. If you do not have the data, but only the title, this statement: Cells(Rows.Count, 1).End(xlUp).ClearContents will clear your headline. To avoid this, we use the offset down one line.
So that the final version: Range("A7:C7", Cells(Rows.Count, 1).End(xlUp)(2, 1)).ClearContents

Sorry I didn't reply. I wasn't around computer last couple of weeks. Thank you so much for your help!