0

thanks for reading my question.

I'm trying to find price points for various sizes of product packages. I have a data range on Sheet1, A1:Z102, with the top row as mass of the packages (B1:Z1), and the first column (A2:A102) as the selling price. In the array I've calculated the profit margins (B2:Z102)

I'm trying to set up "what-if" automatically on a sheet that allows me to enter various weights "Sheet2!B1:B10" -- that will select the corresponding column of package weights from "Sheet1!A2:Z2", then have the function MATCH the closest row of that column to my "what-if" profit margin "Sheet2!A1", and finally return on Sheet2, C1:C10, the selling price that is closest to that profit margin.

SHEET1
       1lbs    2lbs    3lbs    4lbs
$1     $0.10  -$0.80  -$1.80% -$2.80
$2     $1.74   $0.74  -$0.26  -$1.26
$3     $2.61   $1.61   $0.61  -$0.39
$4     ...     ...     ...     $0.47
...

SHEET2
$0.20  2lbs    X
       4lbs    X

[Edit1] So the function would return C1=$2, and C2=$4 in three iterative steps (I think).

  1. Cell.Sheet2!B1.Value matched to identical column in Sheet1 Row1: Returns Column Y
  2. For Column Y in Sheet1, find value closest positive value to Cell.Sheet2A1: Returns Column Y, Row Z
  3. For Sheet1 Column Y, Row Z return value in Sheet1 Column 1 Row Z. repeat that value in C1 Sheet2
  4. Repeat 1-3 for B2...Bn

I'm having a hard time solving each step:

  1. MATCH can identify correct column
  2. Can't figure out how to nest MATCH in "OFFSET" to search only Column Y. I've tried INDEX MATCH, INDEX MATCH MATCH, LOOKUPs(V&H).
  3. Return Column1 value in RowZ.

I'm realizing this might be a Macro solution, but I just don't know how to VBA it:

Dim profit, lbs, cost, reflbs, refprofit As Range

Set cost = Worksheets("Sheet1").Range("$A$2", "$A$5")
Set profit = Worksheets("Sheet1").Range("$B$2", "$E$5")
Set lbs = Worksheets("Sheet1").Range("$B$1", "$E$1")
Set reflbs = Worksheets("Sheet2").Range("$B1")
Set refprofit = Worksheets("zTest").Range("$H$39")

For Each profitCell In profit
    For Each costCell In cost
        For Each lbsCell In lbs
            If lbsCell.Value = reflbsCell.Value Then

Then I'm not sure how to direct a search in the column whose header lbs matches "reflbs", and then I'd put in a:

If profitCell.Value = refprofitCell.Value Then

Then return the Row Z for the match, then ultimately return Row Z, Column A Sheet1! in Sheet2!C1.

Thanks a bunch!

  • You seem to have a good idea of what needs to be done. Can you let us know what formula you've tried so far and how it fails to reach expected result? – cybernetic.nomad Nov 25 '18 at 14:05
  • Thanks for getting back to me as well. I have an experimental table with H38 as the desired bag weight (6 lbs, custom formatted number so the cell only has 6, i.e. "$"0.00 if dollars, or 0.0" lbs" if weight). First I have a function to find the relative column number of the matching weight: =MATCH(H38,G40:M40) which returns "7". I've tried LOOKUP (and H&V LOOKUP) realising it needs an ascending list. I've also tried INDEX MATCH and INDEX MATCH MATCH. MY last attempt was OFFSET, from the "A1" and from an ADDRESS, setting the offset columns to "7" found before then -1 to get to the "A" column. – YAtreyu Nov 26 '18 at 0:50
0

Assuming your source data is laid out like your example as follows:

enter image description here

Then on sheet2 if your data is laid out as follows:

enter image description here

Then you can use the following formula to to get the numbers in red:

=INDEX(Sheet1!$B$2:$E$5,IFERROR(MATCH(Sheet2!$A$1,Sheet1!$A$2:$A$5,1),1),MATCH(Sheet2!$B1,Sheet1!$B$1:$E$1,0))

enter image description here

When the dollar value you are looking for is less than the first value in your column, you were probably getting an error. The way vlookup and match work is that they do not look for the nearest number value. They look for the the last number that was less then what they were looking for. That is the reason why your column needs to be sorted in ascending order. In order to deal with a legitimate value that is less than the first value in the list, the match function that will throw the error is wrapped in an iferror function that returns 1 to represent the first row. Note that if an invalid entry for price is entered in sheet2!A1, it will still return 1 for the first row.

For your weights, they are actually string values most likely and not numbers. Its quite common to have your units listed in a separate cell and then just the value in a cell of its own to make working with the values easier. In this case I assumed your weight would always be an exact match for one of your column headers. a weight of 2.5lbs would throw an error as it is not found in your header list.

  • Hey thanks for getting back to me. I tried using your formula and it is working exactly how you suggested. I need it to do one more step, which is when the formula "finds" C2 in Sheet1! to report the value in Sheet1! ColumnA for the same row, i.e. report ideal package price, vs profit of that sized package price. – YAtreyu Nov 26 '18 at 0:30
  • So instead of $0.80 you want it to return $1? – Forward Ed Nov 26 '18 at 2:50
  • Yup! Its looking up the lbs I set to match to the column for a weight, then looking up in that column the profit I set, then to match the row that profit is in and return the price to set the product at. – YAtreyu Nov 27 '18 at 4:52
  • So -$ 0.80 is $1 away from $0.20, and $0.74 is only $.54 away. What is the expected value for X at 2lbs and 4lbs that you want to see? Still trying to wrap my head around which row you are try to pick and why. – Forward Ed Nov 27 '18 at 5:49

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.