Lookup and report different cells.

[EddieD]

Hi all,
I am having a bit of a challenge figuring out how to approach this.

Here's my challenge:

1. I have a SKU that I sell, but making up that SKU are multiple boxes - all which have different codes.
2. I can produce an inventory report on the box codes, but my system doesn't calculate the inventory on the SKU code.
3. I have created a table of associations, where each box code, has a column before it that references that particular SKU.
4. If the box quantities, aren't perfectly balanced, I cannot sell every box - so i want to take the least of all of the boxes.
5. I want to report the SKU, and the lowest quantity of the boxes that make up that SKU.

How would you approach it?
thx

 

[Legacy 143009]

Are you looking something like this:

=MIN(IF(A6:A15=A23,C6:C15))

It is an array formula so you have to press Ctr+Shft+Entr after you input to the formula bar.
You should have curly brackets at the begging and the end of formula.

 

[EddieD]

Are you looking something like this:

=MIN(IF(A6:A15=A23,C6:C15))

It is an array formula so you have to press Ctr+Shft+Entr after you input to the formula bar.
You should have curly brackets at the begging and the end of formula.

Thank you for your response.

I will have up to 200 of these individual SKUs that I need to reference, so i was thinking some kind of V-lookup formula, not just dealing with this individual one.

 

[Legacy 143009]

I don't get it. Let's say you have records up to 1000th row with different SKU codes. This formula will give you the result whatever you have in the value cell already.


This wil check everything for VA42092 in A23.
When you drag this formula down, for example, it will check the minimum value for let say VA42093 in cell A24.

That's what I can understand from your example.

 

[EddieD]

Sorry I am not explaining myself correctly.

In a completely separate tab I will need a list of all of my SKUs, but only one instance of that SKU. Column A is manually typed out to reference the Box Code to that SKU.

So I'm looking for a formula (in another tab) to VLOOKUP Sku VA42092, but only grab the cell that summarizes the minimum quantity available for that SKU.

Does this make more sense?

 

[EddieD]

I guess another way to explain it is:

Out of this range of data here, find the row that has both VA42092 in column A, and 'Min Available' in column B, then return the value of '12'

 

[EddieD]

Thank you Flashbond, that was the solution after all.

 

[smozgur]

Thank you Flashbond, that was the solution after all.

If that was the solution, then that post should be better marked as the solution to help future readers. I can also see it is the solution to find the minimum value of an item on a table as the answer to the original apart from the other separate requirements, therefore, I switched the marked solution post accordingly.