At my job, I work with an Excel spreadsheet that has a few columns with customer information, most importantly their state, and then another 10+ columns representing each warehouse we have, with the current available inventory of the product the customer is trying to purchase. There is one column between these two groupings that contains an index/match formula (I think, I’d have to check again tomorrow) that effectively goes through the totals of the warehouses and then returns the warehouse with the highest inventory. This process has worked without fail for a long time, but it still requires that someone manually look over each line to see if there are better options, like if the formula chooses the Florida warehouse for the customer that lives in Oregon, but there is plenty of stock in the California warehouse, so while the formula chooses Florida, common sense chooses California. My question is if there is a way to, for each state, designate a sort of priority to go through with the warehouses? For example, if a customer orders something from California, is there a way to make excel look through our warehouses in a certain order, like first the California warehouse, then the Texas warehouse, then the Georgia warehouse, and so on and so forth, and then return the inventory of the first viable warehouse? Or perhaps is there another method I’m not thinking of with which to approach this issue?
What It Does vs What I Would Like
I’m just trying to make my job a little easier and quicker. Any help greatly appreciated.
