1 Reply Latest reply on Aug 16, 2018 5:05 AM by Jim Dehner

    Vlookup with true Case

    S N

      HI All,

       

      I am new to tableau.

       

      I am looking for some thing which is similar to Vlookup with true in excel for calculation.

       

      Below is my scenario, from table 1 if i filter with product 1. then in parameter if i change service level to 99.5 it should calculate result by lookup the value from table 2  (factor table) (ex., formula vlookup(1- (Service level/100),Table2!A:B,2,TRUE)*Consumption) similar to excel) in this case calculation result will be 1-(99.5/100) = 0.005 and lookup result will be 2.2

      therefore my calculated result will be 2.2*24500= 53900

       

      Service level will be my parameter when ever i change the service level it should show the result.

       

      If in case my factor value is not available then show result column as "No Factor"

       

      can anybody help on this. many thanks for your support and help.

       

      Table 1 Data

       

          

      ProductProduct Name

      Parameter

      Service Level

      ConsumptionResult
      1A99.52450053900

       

       

      TABLE 2 (i have only these two values in lookup table)

        

        

      0.0000801620234307523.42
      0.0000866596651718513.40
      0.0000936503680906143.38
      0.0001011687186660563.36
      0.0001092515036860143.34
      0.0001179378332110133.32
      0.0001272692691873803.30
      0.0001372899599054733.28
      0.0001480467804837463.26
      0.0001595894795500823.24
      0.0001719708323033593.22
      0.0001852468001342093.20
      0.0001994766969852793.18
      0.0002147233626139673.16
      0.0002310533429432663.14
      0.0002485370776592613.12
      0.0002672490952231323.10
      0.0002872682154624333.08
      0.0003086777598864113.06
      0.0003315657698874653.04
      0.0003560252329535273.02
      0.0003821543170476893.00
      0.0004100566132607672.98
      0.0004398413868678122.96
      0.0004716238368893682.94
      0.0005055253642569652.92
      0.0005416738486623272.90
      0.0005802039341702432.88
      0.0006212573236452632.86
      0.0006649830820388842.84
      0.0007115379485664872.82
      0.0007610866577816292.80
      0.0008138022695436402.78
      0.0008698665078532562.76
      0.0009294701085100782.74
      0.0009928131755270192.72
      0.0010601055462129402.70
      0.0011315671648164402.68
      0.0012074284645939802.66
      0.0012879307581426002.64
      0.0013733266358171302.62
      0.0014638803720168302.60
      0.0015598683391013802.58
      0.0016615794286747302.56
      0.0017693154799309802.54
      0.0018833917147431702.52
      0.0020041371791284002.50
      0.0021318951907058902.48
      0.0022670237917208302.46
      0.0024098962071756902.44
      0.0025609013075826902.42
      0.0027204440758121802.40
      0.0028889460774719202.38
      0.0030668459342311202.36
      0.0032545997994493102.34
      0.0034526818354518902.32
      0.0036615846917471602.30
      0.0038818199834405002.28
      0.0041139187690921702.26
      0.0043584320271774502.24
      0.0046159311303402002.22
      0.0048870083165346602.20
      0.0051722771561499802.18
      0.0054723730141765802.16
      0.0057879535064246902.14
      0.0061196989487890502.12
      0.0064683127985123402.10
      0.0068345220863839102.08
      0.0072190778387604102.06
      0.0076227554882879502.04
      0.0080463552721741202.02
      0.0084907026168297202.00
        • 1. Re: Vlookup with true Case
          Jim Dehner

          Good morning

          If you haven't got a response see the attached

           

          it will return this

           

          the 2 formulas are

          and the lookup formula is a if - then statement

          that I made from your list - I did it in excel and then copy and pasted it (attached) - you did not specify the end conditions so you will want to check them

           

           

          if [lookupvalue] <= 8.0162E-05 then 3.42

          elseif [lookupvalue] <= 8.66597E-05 then 3.42

          elseif [lookupvalue] <= 9.36504E-05 then 3.4

          elseif [lookupvalue] <= 0.000101169 then 3.38

          elseif [lookupvalue] <= 0.000109252 then 3.36

          elseif [lookupvalue] <= 0.000117938 then 3.34

          elseif [lookupvalue] <= 0.000127269 then 3.32

          elseif [lookupvalue] <= 0.00013729 then 3.3

          elseif [lookupvalue] <= 0.000148047 then 3.28

          elseif [lookupvalue] <= 0.000159589 then 3.26

          elseif [lookupvalue] <= 0.000171971 then 3.24

          elseif [lookupvalue] <= 0.000185247 then 3.22

          elseif [lookupvalue] <= 0.000199477 then 3.2

          elseif [lookupvalue] <= 0.000214723 then 3.18

          elseif [lookupvalue] <= 0.000231053 then 3.16

          elseif [lookupvalue] <= 0.000248537 then 3.14

          elseif [lookupvalue] <= 0.000267249 then 3.12

          elseif [lookupvalue] <= 0.000287268 then 3.1

          elseif [lookupvalue] <= 0.000308678 then 3.08

          elseif [lookupvalue] <= 0.000331566 then 3.06

          elseif [lookupvalue] <= 0.000356025 then 3.04

          elseif [lookupvalue] <= 0.000382154 then 3.02

          elseif [lookupvalue] <= 0.000410057 then 3

          elseif [lookupvalue] <= 0.000439841 then 2.98

          elseif [lookupvalue] <= 0.000471624 then 2.96

          elseif [lookupvalue] <= 0.000505525 then 2.94

          elseif [lookupvalue] <= 0.000541674 then 2.92

          elseif [lookupvalue] <= 0.000580204 then 2.9

          elseif [lookupvalue] <= 0.000621257 then 2.88

          elseif [lookupvalue] <= 0.000664983 then 2.86

          elseif [lookupvalue] <= 0.000711538 then 2.84

          elseif [lookupvalue] <= 0.000761087 then 2.82

          elseif [lookupvalue] <= 0.000813802 then 2.8

          elseif [lookupvalue] <= 0.000869867 then 2.78

          elseif [lookupvalue] <= 0.00092947 then 2.76

          elseif [lookupvalue] <= 0.000992813 then 2.74

          elseif [lookupvalue] <= 0.001060106 then 2.72

          elseif [lookupvalue] <= 0.001131567 then 2.7

          elseif [lookupvalue] <= 0.001207428 then 2.68

          elseif [lookupvalue] <= 0.001287931 then 2.66

          elseif [lookupvalue] <= 0.001373327 then 2.64

          elseif [lookupvalue] <= 0.00146388 then 2.62

          elseif [lookupvalue] <= 0.001559868 then 2.6

          elseif [lookupvalue] <= 0.001661579 then 2.58

          elseif [lookupvalue] <= 0.001769315 then 2.56

          elseif [lookupvalue] <= 0.001883392 then 2.54

          elseif [lookupvalue] <= 0.002004137 then 2.52

          elseif [lookupvalue] <= 0.002131895 then 2.5

          elseif [lookupvalue] <= 0.002267024 then 2.48

          elseif [lookupvalue] <= 0.002409896 then 2.46

          elseif [lookupvalue] <= 0.002560901 then 2.44

          elseif [lookupvalue] <= 0.002720444 then 2.42

          elseif [lookupvalue] <= 0.002888946 then 2.4

          elseif [lookupvalue] <= 0.003066846 then 2.38

          elseif [lookupvalue] <= 0.0032546 then 2.36

          elseif [lookupvalue] <= 0.003452682 then 2.34

          elseif [lookupvalue] <= 0.003661585 then 2.32

          elseif [lookupvalue] <= 0.00388182 then 2.3

          elseif [lookupvalue] <= 0.004113919 then 2.28

          elseif [lookupvalue] <= 0.004358432 then 2.26

          elseif [lookupvalue] <= 0.004615931 then 2.24

          elseif [lookupvalue] <= 0.004887008 then 2.22

          elseif [lookupvalue] <= 0.005172277 then 2.2

          elseif [lookupvalue] <= 0.005472373 then 2.18

          elseif [lookupvalue] <= 0.005787954 then 2.16

          elseif [lookupvalue] <= 0.006119699 then 2.14

          elseif [lookupvalue] <= 0.006468313 then 2.12

          elseif [lookupvalue] <= 0.006834522 then 2.1

          elseif [lookupvalue] <= 0.007219078 then 2.08

          elseif [lookupvalue] <= 0.007622755 then 2.06

          elseif [lookupvalue] <= 0.008046355 then 2.04

          elseif [lookupvalue] <= 0.008490703 then 2.02

          else 2 end

           

           

          Jim

           

          If this posts assists in resolving the question, please mark it helpful or as the 'correct answer' if it resolves the question. This will help other users find the same answer/resolution.  Thank you.