IFERROR function breaking if a row has the same item in a different row

Question

Problem Table with Products

If I throw in an item that is identical one of the rows, it wont autofill the whole row.

Under the A column, I have the code CW01 which refers to the product Carcass White variant STD from a table with all the products. The way it works is, If I type that code, the Product Type, Product and Variant Column automatically fill in.

If I type that Code in another row it wont Autofill because the Code is already in a different row.

Currently I have a formula in that spills to the rows below for every item I pop in.

In Column A: it's a Data Validation Code

B: =IFERROR(FILTER(Table55[Categorie],ISNUMBER(MATCH(Table55[Code],A4:A20,0))),"")

C: =IFERROR(FILTER(Table55[Product],ISNUMBER(MATCH(Table55[Code],A4:A20,0))),"")

D: =IFERROR(FILTER(Table55[Variant],ISNUMBER(MATCH(Table55[Code],A4:A20,0))),"")

I: =IFERROR(FILTER(Table55[Price],ISNUMBER(MATCH(Table55[Product]&Table55[Variant],C4:C20&D4:D20,0)))*TOCOL(H4:H20,1)," ")

Answer 1

Use XLOOKUP(), i.e.

=XLOOKUP(TOCOL(A4:A20,1),Table55[Code],Table55[Categorie]," ")

in B4

=XLOOKUP(TOCOL(A4:A20,1),Table55[Code],Table55[Product]," ")

in C4 and

=XLOOKUP(TOCOL(A4:A20,1),Table55[Code],Table55[Variant]," ")

in D4

The formula in I4 should also be updated to

=IFERROR(XLOOKUP(TOCOL(A4:A20,1),Table55[Code],Table55[Price])*TOCOL('Form'!H4:H20,1)," ")