Using IF function with VLOOKUP for VBA

[stemhel]

I currently have an excel field using a formula and data validation. The formula is an IF statement that looks for any text from one cell(ie equipment part number) and then applies a VLOOKUP to find the name of the equipment. Based on the output of the VLOOKUP there is a data validation for a list to select either New or Used + Equipment type. All this is happening inside a table and I am having issues with the IF/VLOOKUP formula not auto-copying to a new row when new information is added. The data validation copies down to the new table row but not the formula. I am wanting to create a macro that applies to the entire Column that executes the formula function.

example of the formula
=IF(J2="","",VLOOKUP(J2,Parts!$A:$B,2,FALSE))

 

[Yongle]

The formula does copy down inside a table

Use this notation (PartNo is the header in column J)

=IF([@PartNo]="","",VLOOKUP([@PartNo],Parts!A:B,2,0))

Excel 2016 (Windows) 32 bit

	A​	B​	C​	D​	E​	F​	G​	H​	I​	J​	K​	L​
1​	A	B	C	D	E	F	G	H	I	PartNo	Type	formula in column K
2​	20/07/2019​	54​	70​	30​	81​	17​	51​	31​	15​	Part001	a	=IF([@PartNo]="","",VLOOKUP([@PartNo],Parts!A:B,2,0))
3​	20/07/2019​	10​	42​	16​	71​	92​	27​	88​	72​	Part002	b	=IF([@PartNo]="","",VLOOKUP([@PartNo],Parts!A:B,2,0))

Sheet: Sheet1