Compare two lists each with two columns, highlight differences. CAD vs ERP BOM compare.

comes up with errors whenever you move stuff around vertically or try and change the columns each are posted in.

changing the row should not impact VLOOKUP but moving the column obviously will: which column(s) get changed?

If the "Part Number" column in Sheet2 is changed then INDEX/MATCH is the better solution where you match the column title " PART Number" and/or "Qty"to do the match.

e.g

=INDEX(Sheet2!$A$3:$C$23,MATCH($B7,Sheet2!$A$3:$A$23,0),MATCH("QTY:",Sheet2!$A$2:$Z$2,0))

replace your current VLOOKUP (assumes "Patt Number" is colum A in Sheet2

OR

=INDEX(Sheet2!$A$3:$Z$23,MATCH(B8,INDEX(Sheet2!$A$3:$Z$23,,MATCH("PART NUMBER",Sheet2!$A$2:$Z$2,0)),0),MATCH("QTY:",Sheet2!$A$2:$Z$2,0))

which matches both the "Part Number" and "QTY:" columns in Sheet2 so you can amend these columns

The columns that are being changed are (EL) for Quantity and (EM) for part name on sheet 2 the rest being left the same on sheet 1 as of right now. And I thought it was weird that when I moved it vertically it made a difference but whenever I move 200-0079 and its quantity to the first row in the list on sheet 2 it highlights it black on sheet one.

Thank you for your help and also the length of both lists will vary depending on what model is being worked but I think the function can adjust with that automatically.