Announcement

Collapse
No announcement yet.

lookup with changing parameter

Collapse
X
  • Filter
  • Time
  • Show
Clear All
new posts

  • lookup with changing parameter



    Hi

    after done a search on the forum I did not find a solution nor a clue to get me going ...
    I'm struggling with a lookup issue and can't find a solution

    here's the set-up
    I have a table with a factor that is linked with a supplier
    this factor is used to make ranges ( e.g. if a supplier is between this range he is deducted x points)

    I'm able to get the points of deduction for the mentioned supplier - unfortunately all other suppliers
    are then also treated with the same range for deduction , whereas it should use its own factor

    I've attached a file with the set-up - my explanation may be vague

    Hopefully someone can point me in the correct direction

    many thanks
    Attached Files
    EPC + ISOLATIE

  • #2
    I am not sure what you are trying to accomplish.

    Which table are we applying the formula to? what are your expected results?
    Where there is a will there are many ways. Finding one that works for you is the challenge!

    MS Excel MVP 2010-2016

    Comment


    • #3
      Originally posted by NBVC View Post
      I am not sure what you are trying to accomplish.

      Which table are we applying the formula to? what are your expected results?
      hi NBVC,

      it is a combination of looking in several tables

      1: factor is been looked up in table A1:M19 ( input for the correct factor is Supplier en month located in cells P5&P6)
      2: when correct factor is know this is put in cell S164
      3: due to this factor all formula change in table R3:T164
      4: Then the value of that supplier for that month ( again cell P5-P6) is looked up in table A26:M43
      5: this value found is compared again to table R3-T164 - points deducted are in column 3 of this table
      6: the points deducted are put in table V4:W21

      problem is that points deducted per supplier are now related to selected supplier in P5 - but factor should change per supplier ( so each supplier has its "own" table R3:T164 for that month

      I hope this explains it a bit more
      EPC + ISOLATIE

      Comment


      • #4
        Sorry, I am still not getting it.

        Can you post back the workbook showing where it is going wrong and what the actual values should be (with any explanations)?
        Where there is a will there are many ways. Finding one that works for you is the challenge!

        MS Excel MVP 2010-2016

        Comment


        • #5
          Hi I have updated the book1.xlsx a bit so it's more self explaining
          basically it comes down to that each supplier has it own correction factor for a particular month
          this correction factor is used to calculate the thresholds for this supplier then his score he achieved in that month Book1.xlsx
          is compared to different tresholdranges to deduct points

          my problem is that 1 correction factor is used to do the lookups for all suppliers whereas each supplier should use his own ....
          EPC + ISOLATIE

          Comment


          • #6
            update book1.xls
            Attached Files
            EPC + ISOLATIE

            Comment


            • #7


              I think the solution would involve you creating a group of tables on another tab, one for each vendor, then you can extract a factor for each table, then lookup corresponding tables.

              You can't virtually change the table contents, especially when you have a couple of hard coded values at the top of the table.
              Where there is a will there are many ways. Finding one that works for you is the challenge!

              MS Excel MVP 2010-2016

              Comment

              Working...
              X