Results 1 to 8 of 8

Thread: Search for a value with two criterias

  1. #1
    New Member
    Join Date
    May 2017
    Posts
    40
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Search for a value with two criterias

    Hello all!

    I have a project with a client and I would like a slick solution instead of what I usually come up with, which is bulky and slow.

    I am calculating a shipping price, where i have the zone and weight of the container. This held together with destination country should give me the final price.

    Every destination country has a zone in a zone map, And i have made a VLOOKUP to a cell where i have the zone number. So I always have the zone number when they choose a destination country in the drop down menu I have created.

    My problem is now that i need to search a matrix on both the zone and the weight (which is also inputted).

    For the sake of the example my Zone number is in cell A1 and weight is input in A2. The matrix for the price is on Sheet2.

    How do i do that?

  2. #2
    Board Regular
    Join Date
    Jul 2014
    Location
    The Netherlands
    Posts
    1,263
    Post Thanks / Like
    Mentioned
    11 Post(s)
    Tagged
    1 Thread(s)

    Default Re: Search for a value with two criterias

    Hi,

    Just as an example on how to approach:

    ABCDEFGHIJK
    13400Zone / weight100200300400500600
    2zoneweight10,9822410,1523950,9237710,4651430,5614830,527555
    320,2166140,9550840,9248270,0201970,4024080,694049
    430,7639830,4870430,6282710,1147540,7836940,368298
    50,11475440,8542050,8822790,9864130,7397430,8922220,08628
    650,554510,2353030,0872840,1727580,8659330,178537
    760,6732690,2675350,6148980,1734970,9536250,351748
    870,0572070,6032490,5888810,2210160,5608190,031514
    980,9728260,5365990,5256040,824340,772690,610188
    1090,6724330,2085210,1514880,709690,0784630,839913

    Sheet3



    Worksheet Formulas
    CellFormula
    B5=INDEX(F2:K10,MATCH(A1,E2:E10,0),MATCH(B1,F1:K1,0))

    --------------------------------------------------------------------------------
    If you found my answer helpful, please hit the Like or Thank button.

    Please follow the forum Rules and Guidelines and use Code tags around your VBA code.

    Remember: test VBA code always on a copy of your file because usually you can't use <undo>

  3. #3
    New Member
    Join Date
    May 2017
    Posts
    40
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Search for a value with two criterias

    Quote Originally Posted by jorismoerings View Post
    Hi,

    Just as an example on how to approach:

    A B C D E F G H I J K
    1 3 400 Zone / weight 100 200 300 400 500 600
    2 zone weight 1 0,982241 0,152395 0,923771 0,465143 0,561483 0,527555
    3 2 0,216614 0,955084 0,924827 0,020197 0,402408 0,694049
    4 3 0,763983 0,487043 0,628271 0,114754 0,783694 0,368298
    5 0,114754 4 0,854205 0,882279 0,986413 0,739743 0,892222 0,08628
    6 5 0,55451 0,235303 0,087284 0,172758 0,865933 0,178537
    7 6 0,673269 0,267535 0,614898 0,173497 0,953625 0,351748
    8 7 0,057207 0,603249 0,588881 0,221016 0,560819 0,031514
    9 8 0,972826 0,536599 0,525604 0,82434 0,77269 0,610188
    10 9 0,672433 0,208521 0,151488 0,70969 0,078463 0,839913
    Sheet3

    Worksheet Formulas
    Cell Formula
    B5 =INDEX(F2:K10,MATCH(A1,E2:E10,0),MATCH(B1,F1:K1,0))
    That works PERFECTLY - thank you!

    I have found out the weight-factor is a bit more complicated than just min-max unfortunately.

    The weight goes from for instance 100-1000, and then different prices per 100 until 5000 and other price until 10000. In this case I tried to put in an IF formula, so if the weight goes over the limit it will show the price for 1000 and then i can with another formula calculate the price for the "rest" of the weight if you understand.

    Code:
    =INDEX(F2:K10,MATCH(A1,E2:E10,0),MATCH(IF(B1>1000,1000,B1),F1:K1,0))
    This fails with a reference error though. But if it just can show me the max price within the matrix if it goes over weight, then i can formulate another cell to calculate the rest.

  4. #4
    Board Regular
    Join Date
    Jul 2014
    Location
    The Netherlands
    Posts
    1,263
    Post Thanks / Like
    Mentioned
    11 Post(s)
    Tagged
    1 Thread(s)

    Default Re: Search for a value with two criterias

    hi,

    The MATCH function in itself is already a check, so can't hold the IF part.
    You could add the 1000 column to the table and have it return the lowest number in the table.
    like this:
    ABCDEFGHIJKL
    131100Zone / weight1002003004005006001000
    2zoneweight10,9822410,1523950,9237710,4651430,5614830,5275550,083482
    320,2166140,9550840,9248270,0201970,4024080,6940490,378216
    430,7639830,4870430,6282710,1147540,7836940,3682980,669498
    50,66949840,8542050,8822790,9864130,7397430,8922220,086280,57495
    650,554510,2353030,0872840,1727580,8659330,1785370,53103
    760,6732690,2675350,6148980,1734970,9536250,3517480,923199
    870,0572070,6032490,5888810,2210160,5608190,0315140,98567
    980,9728260,5365990,5256040,824340,772690,6101880,021833
    1090,6724330,2085210,1514880,709690,0784630,8399130,614107

    Sheet1



    Worksheet Formulas
    CellFormula
    B5=INDEX(F2:L10,MATCH(A1,E2:E10,0),MATCH(B1,F1:L1,1))



    This way the weight in F1:L1 is the minimum weight so 450 will result in the number for 400 and 1050 will result in 1000.
    Does this help?
    If not please provide a sample of your tables and create a manual mockup of what the expected outcome of the formula should be.
    --------------------------------------------------------------------------------
    If you found my answer helpful, please hit the Like or Thank button.

    Please follow the forum Rules and Guidelines and use Code tags around your VBA code.

    Remember: test VBA code always on a copy of your file because usually you can't use <undo>

  5. #5
    New Member
    Join Date
    May 2017
    Posts
    40
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Search for a value with two criterias

    Thank you very much for your reply.

    I can provide a sample here (if i can manage to make it look good):

    Weight/zone 1 2 3 4 5 6 7 8 9
    1 100 100 100 100 100 100 100 100 100
    2 200 200 200 200 200 200 200 200 200
    3 300 300 300 300 300 300 300 300 300
    4 400 400 400 400 400 400 400 400 400
    5 500 500 500 500 500 500 500 500 500
    6 600 600 600 600 600 600 600 600 600
    7 700 700 700 700 700 700 700 700 700
    8 800 800 800 800 800 800 800 800 800
    9 900 900 900 900 900 900 900 900 900
    10 1000 1000 1000 1000 1000 1000 1000 1000 1000
    Break / 1 50 50 50 50 50 50 50 50 50
    Stop / 50 3000 3000 3000 3000 3000 3000 3000 3000 3000
    Break / 1 45 45 45 45 45 45 45 45 45
    Stop / 200 12000 12000 12000 12000 12000 12000 12000 12000 12000


    The break / 1 in this case means that every unit costs 50 from 10-50 because the stop / 50 means ultimately that 50 units costs 3000. And again the second break / 1 means that every unit from 50 cost 45 up to 200 units. Hopefully it makes sense to you.

    I need to have some sort of calculation so i can type in any unit and it calculates a price (if below 10 units it will just find the price in the matrix, but if it above maximum matrix unit (in this case 10) then it needs to calculate the price. There is no need for an elegant solution, as I have a data sheet which is not visible. So it is easy to make steps in order to make the formulas simple but in 2-4 steps instead of one long formula, which no one understands.

    My own take on it would be to make criterias based on the weight. So a 0/1 would determine if the calculation goes on to the next step (Is weight above 50 units? for instance) and then would calcuate based on the criteria steps. If there is a more elegant and viable solution I'm all ears.

  6. #6
    Board Regular
    Join Date
    Jul 2014
    Location
    The Netherlands
    Posts
    1,263
    Post Thanks / Like
    Mentioned
    11 Post(s)
    Tagged
    1 Thread(s)

    Default Re: Search for a value with two criterias

    Hi,

    Something like this:
    ABCDEFGHIJKLMN
    1386Weight/zone123456789
    2zoneweight1100100100100100100100100100
    32200200200200200200200200200
    43300300300300300300300300300
    538704400400400400400400400400400
    65500500500500500500500500500
    76600600600600600600600600600
    87700700700700700700700700700
    98800800800800800800800800800
    109900900900900900900900900900
    1110100010001000100010001000100010001000
    1211505050505050505050
    1351454545454545454545

    Sheet1



    Worksheet Formulas
    CellFormula
    B5=IF(B1>10,B1,1)*INDEX(F2:N13,MATCH(B1,E2:E13,1),MATCH(A1,F1:N1,0))

    --------------------------------------------------------------------------------
    If you found my answer helpful, please hit the Like or Thank button.

    Please follow the forum Rules and Guidelines and use Code tags around your VBA code.

    Remember: test VBA code always on a copy of your file because usually you can't use <undo>

  7. #7
    New Member
    Join Date
    May 2017
    Posts
    40
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Search for a value with two criterias

    Quote Originally Posted by jorismoerings View Post
    Hi,

    Something like this:
    A B C D E F G H I J K L M N
    1 3 86 Weight/zone 1 2 3 4 5 6 7 8 9
    2 zone weight 1 100 100 100 100 100 100 100 100 100
    3 2 200 200 200 200 200 200 200 200 200
    4 3 300 300 300 300 300 300 300 300 300
    5 3870 4 400 400 400 400 400 400 400 400 400
    6 5 500 500 500 500 500 500 500 500 500
    7 6 600 600 600 600 600 600 600 600 600
    8 7 700 700 700 700 700 700 700 700 700
    9 8 800 800 800 800 800 800 800 800 800
    10 9 900 900 900 900 900 900 900 900 900
    11 10 1000 1000 1000 1000 1000 1000 1000 1000 1000
    12 11 50 50 50 50 50 50 50 50 50
    13 51 45 45 45 45 45 45 45 45 45
    Sheet1

    Worksheet Formulas
    Cell Formula
    B5 =IF(B1>10,B1,1)*INDEX(F2:N13,MATCH(B1,E2:E13,1),MATCH(A1,F1:N1,0))
    Thank you for your reply. In this case I might have written the calculation in a weird way. Let me explain.

    Your designated weight is 86. What the calculation needs to do is take the maximum price that is available as a "full" price. You are missing a "full" price at 50 and again at the next stop. This means that the full price for weight 50 will be price of 10 + (price per unit * 40) which in this case is 1000+(50*40) = 3000. Then it is easy to calculate 86 weight which is max full price (50 weight which is 3000) and the added price per unit until designated price. In your example would this mean 3000+(45*36)=4620. It can be a bit confusing but this is how this works for the client.

    That is why i suggested that we use cells as step-counters before the final calculation. Meaning that we can use a cell to point to where the calculation should start. In this case <=10, <50, <100 (if thats the final stop). In this case we can name the parameters 0,1,2. If the cell is 0 we know we should look for a final price in the F2:N11 matrix. If its 1 (we say weight/B1 is 15), we need to find the right zone (in this case 3) and get the H11 value and do the calculation which will be =H11+((B1-E11)*50). Likewise with the 2 counter which is <100. Hopefully you understand what i mean. I will try myself to use some of the match/index formula to see if I can make it grab the right cell for this calculation.

  8. #8
    Board Regular
    Join Date
    Jul 2014
    Location
    The Netherlands
    Posts
    1,263
    Post Thanks / Like
    Mentioned
    11 Post(s)
    Tagged
    1 Thread(s)

    Default Re: Search for a value with two criterias

    Hi,

    Last attempt:

    ABCDEFGHIJKLMN
    1Calculation tableWeight/zone123456789
    210502001100100100100100100100100100
    3300002200200200200200200200200200
    403603300300300300300300300300300
    54504400400400400400400400400400
    65500500500500500500500500500
    7zoneweightAmount6600600600600600600600600600
    8386 € 4.620,00 7700700700700700700700700700
    98800800800800800800800800800
    109900900900900900900900900900
    1110100010001000100010001000100010001000
    1211505050505050505050
    1350300030003000300030003000300030003000
    1451454545454545454545
    15200120001200012000120001200012000120001200012000

    Sheet3



    Worksheet Formulas
    CellFormula
    A3=IF(OR(A4="",A4=0),"",INDEX($F$2:$N$15,MATCH($A$2,$E$2:$E$15,1),MATCH($A$8,$F$1:$N$1,0)))
    B3=IF(OR(B4=0,B4=""),0,INDEX($F$2:$N$15,MATCH($B$2,$E$2:$E$15,1),MATCH($A$8,$F$1:$N$1,0)))
    C3=IF(OR(C4="",C4=0),0,INDEX($F$2:$N$15,MATCH($C$2,$E$2:$E$15,1),MATCH($A$8,$F$1:$N$1,0)))
    A4=IF(A$2=IFERROR(LOOKUP($B8,$A$2:$C$2,$A$2:$C$2),0),$B$8-A$2,0)
    B4=IF(B$2=IFERROR(LOOKUP($B8,$A$2:$C$2,$A$2:$C$2),0),$B$8-B$2,0)
    C4=IF(C$2=IFERROR(LOOKUP($B8,$A$2:$C$2,$A$2:$C$2),0),$B$8-C$2,0)
    A5=IF(A4=0,"",INDEX($F$2:$N$15,MATCH($B$8,$E$2:$E$15,1),MATCH($A$8,$F$1:$N$1,0)))
    B5=IF(B4=0,0,INDEX($F$2:$N$15,MATCH($B$8,$E$2:$E$15,1),MATCH($A$8,$F$1:$N$1,0)))
    C5=IF(C4=0,0,INDEX($F$2:$N$15,MATCH($B$8,$E$2:$E$15,1),MATCH($A$8,$F$1:$N$1,0)))
    C8=IFERROR((B3+(B4*B5)),0)+IFERROR((C3+(C4*C5)),0)+IFERROR((A3+(A4*A5)),0)+IF(B8<=10,INDEX($F$2:$N$16,MATCH($B$8,$E$2:$E$16,1),MATCH($A$8,$F$1:$N$1,0)),0)

    --------------------------------------------------------------------------------
    If you found my answer helpful, please hit the Like or Thank button.

    Please follow the forum Rules and Guidelines and use Code tags around your VBA code.

    Remember: test VBA code always on a copy of your file because usually you can't use <undo>

Some videos you may like

User Tag List

Tags for this Thread

Like this thread? Share it with others

Like this thread? Share it with others

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •