thanx newdoverman.

this is exactly what i want.

can you provide me the formula without clicking the cells or columns. eg. instead of showing table1ID, rather show like $A$2:$A$10.

you can provide me the formula for your own worksheet.

i tried it myself but its not working that way so thats y i ask.

one more thing if drag the last cell W10 further down will it automatically fill in the 4 digit codes (given that there is data entered in columns U and V beyond row10)