I need to update vertical extract price file with the correct part and price break price found on the horizontal price sheet which contains four price breaks varying from 1,000 to 25,00 and some of the price sheet parts have less than four price breaks. Need result noted if no matching price break found.
I have 2 dozen customer price files containing up to 3000 rows of data to be uploaded to our ERP so I would need to duplicate the process for each customer file. I can provide small file attachment for example.
I have tried Xlookup, Index and Match not easily copied. The extract file may be the issue.
=XLOOKUP(J2&L2,'COL Z1'!$A$2:$A$23&INDEX('COL Z1'!$B$2:$L$23,0,MATCH(MINIFS('COL Z1'!$B$2:$L$2,'COL Z1'!$B$2:$L$2,">="&L2),'COL Z1'!$B$2:$L$2,0)),INDEX('COL Z1'!$B$2:$L$23,0,1+MATCH(MINIFS('COL Z1'!$B$2:$L$2,'COL Z1'!$B$2:$L$2,">="&L2),'COL Z1'!$B$2:$L$2,0)),"not found")
