Filtering and numbers changing.

Hi PF-Change Ryan,

 

I have placed a start to a solution for you in the sheet/tab called Help, which I have added to your file.  The problem that you are going to run into with VLOOKUP is that it will only return the first set of data that matches what is in column A.  For example, you have more than one entry for "Box Extender" in your data and it is unclear which price is supposed to be used.  Would you please provide more details for how the correct price is determined?

 

Adam

Thank you Adam for the assistance. I have many other problems I wish to find answers too but lack the knowledge of what is the right "question" to get my answers. Anyways...

My guess is that on the right side (Under PRODUCT tab), I did a MIN call, on the two items, of which I could then just use that as a means to determining the price between the two identical items. As you can see, I am tracking pricing from two different stores. I see what you did, which I was a bit afraid of what it may have to be (An array) which I am going to assume would take several hours to get nailed down.

On the right side on the product list, I need to some how conditional format it so it reflects the color of which it is pulling the MIN number from. Would that be accomplished by using an IF statement?

In order to get the minimum price each time, you can use SORT() to place the items in order based on item type and then price.  I have updated the formula in the Help sheet/tab to reflect this.  Is this what you are needing?

 

Adam

So I won't need to make a MIN call on the product sheet if I am using this array formula since it will pick the lowest price of the two items given. If that is the case, then that will conclude my dilemma in this one aspect.

I really appreciate your time in the matter. I will try to decipher what all the numbers mean in the formula to get a better grasp.

Adam, one last thing. How do I stop it from trying to auto populate the whole column that the formula is at? Its nifty but the work sheet of which you showed me how to accomplish this is just a small example of the hundreds of products I am trying to sort out. I think I have roughly figured out how this array thing works in the excel sense, but it doesn't want work if I have multiple items going down the list. I guess if I want to, I could just condense ALL the products together into a single tab, and just have the array do all the work for me. Excuse my rambling.

The error I get if I have multiple array calls in the same column is.

To set a specific range for the arrayformula, you need to adjust the ranges that are found in the formula.  Take this part of the formula:

        arrayformula( IF(A9:A="",, vlookup(A9:A

The range is A9:A.  This tells the sheet which cell you want to start with and end with.  The A9 is saying to start at cell A9 and the A is saying end at the bottom of the column.  If you want to only fill in rows 9 to 18, then you would use the range of A9:A18.

 

The error message you are seeing is telling you that the arrayformula is trying to use a cell that has either another formula in it or data typed into it.  To get rid of the error, you either need to delete the items in the cell or change the range of your arrayformula.

 

And to answer your question from earlier, the SORT that I added will make the formula find the lowest price each time the formula is run.