Best way to import data from different excel sheets

Hello, I am working on a costing platform for a restaurant. One of the important things I am working on is the ability to import current prices from vendor reports in excel. The tricky thing is that each vendor’s report is formatted differently with the two columns I am utilizing (lookup Item Number to return Price) being in different columns for each vendor.
So right now I have written into the Lookup formula to Switch by vendor and then look in the appropriate vendor table. When the vendor supplies an updated report, I am copying it over the previous table data.
I have built a sample doc here. Vendor Price Lookup Sample Doc
This technically works, but it seems very clunky and does not have the ability to add new vendors without rewriting the formula. So I am looking for suggestions that would allow me to add new vendors without having adjust the price lookup formula and also be more user friendly. Should I be looking into an automation such as Zapier to update a price list table based on spreadsheets stored in a cloud folder?
Any help is appreciated.

What I do is to have different views for each vendor. Then I arrange the columns in the way that I receive the spreadsheets. And you can then default vendor specific info as well. E.g. name.

2 Likes