How to divide two cells based on match?











up vote
0
down vote

favorite












In a table 1, I have,



+---+---+----+

|   | A | B  |

+---+---+----+

| 1 | A | 30 |

| 2 | B | 20 |

| 3 | C | 15 |

+---+---+----+


On table 2, I have



+---+---+---+----+

|   | A | B | C  |

+---+---+---+----+

| 1 | A | 2 | 15 |

| 2 | A | 5 |  6 |

| 3 | B | 4 |  5 |

+---+---+---+----+


I want the number in second column to divide the number in table 1, based on match, and the result in third column.
The number present in the bracket is the result needed. What is the formula that I must apply in third column in table 2?



Check the image



Please help me on this.



Thanks in advance






excel







share|improve this question
























  • What do you mean by match? based on column name or based on cell value? when you write A - 2 does that mean cell $A$1 has value of 2? Or has the value "A - 2"?
    – siggi_pop
    2 days ago










  • Hi siggi_pop, thanks for connecting. I wanted to distinguish columns through "-" A B C are the entries in column A and the numbers are the entries in Column B. And the match is based on the alphabet.In sheet 2, for A, the factor is 2, so A in sheet 2 matches with A in sheet 1 and returns a value in third column, say 15 (30/2).
    – ashwin
    2 days ago












  • Ok so based on your answer 'I wanted to distinguish rows through "-"' would mean that A - 2 is same as $A$2 ( column A, row 2 ) correct ?
    – siggi_pop
    2 days ago















up vote
0
down vote

favorite












In a table 1, I have,



+---+---+----+

|   | A | B  |

+---+---+----+

| 1 | A | 30 |

| 2 | B | 20 |

| 3 | C | 15 |

+---+---+----+


On table 2, I have



+---+---+---+----+

|   | A | B | C  |

+---+---+---+----+

| 1 | A | 2 | 15 |

| 2 | A | 5 |  6 |

| 3 | B | 4 |  5 |

+---+---+---+----+


I want the number in second column to divide the number in table 1, based on match, and the result in third column.
The number present in the bracket is the result needed. What is the formula that I must apply in third column in table 2?



Check the image



Please help me on this.



Thanks in advance






excel







share|improve this question
























  • What do you mean by match? based on column name or based on cell value? when you write A - 2 does that mean cell $A$1 has value of 2? Or has the value "A - 2"?
    – siggi_pop
    2 days ago










  • Hi siggi_pop, thanks for connecting. I wanted to distinguish columns through "-" A B C are the entries in column A and the numbers are the entries in Column B. And the match is based on the alphabet.In sheet 2, for A, the factor is 2, so A in sheet 2 matches with A in sheet 1 and returns a value in third column, say 15 (30/2).
    – ashwin
    2 days ago












  • Ok so based on your answer 'I wanted to distinguish rows through "-"' would mean that A - 2 is same as $A$2 ( column A, row 2 ) correct ?
    – siggi_pop
    2 days ago













up vote
0
down vote

favorite









up vote
0
down vote

favorite











In a table 1, I have,



+---+---+----+

|   | A | B  |

+---+---+----+

| 1 | A | 30 |

| 2 | B | 20 |

| 3 | C | 15 |

+---+---+----+


On table 2, I have



+---+---+---+----+

|   | A | B | C  |

+---+---+---+----+

| 1 | A | 2 | 15 |

| 2 | A | 5 |  6 |

| 3 | B | 4 |  5 |

+---+---+---+----+


I want the number in second column to divide the number in table 1, based on match, and the result in third column.
The number present in the bracket is the result needed. What is the formula that I must apply in third column in table 2?



Check the image



Please help me on this.



Thanks in advance






excel







share|improve this question















In a table 1, I have,



+---+---+----+

|   | A | B  |

+---+---+----+

| 1 | A | 30 |

| 2 | B | 20 |

| 3 | C | 15 |

+---+---+----+


On table 2, I have



+---+---+---+----+

|   | A | B | C  |

+---+---+---+----+

| 1 | A | 2 | 15 |

| 2 | A | 5 |  6 |

| 3 | B | 4 |  5 |

+---+---+---+----+


I want the number in second column to divide the number in table 1, based on match, and the result in third column.
The number present in the bracket is the result needed. What is the formula that I must apply in third column in table 2?



Check the image



Please help me on this.



Thanks in advance






excel




excel






share|improve this question















share|improve this question













share|improve this question




share|improve this question








edited 2 days ago









JNevill

30.9k31544




30.9k31544










asked 2 days ago









ashwin

33




33












  • What do you mean by match? based on column name or based on cell value? when you write A - 2 does that mean cell $A$1 has value of 2? Or has the value "A - 2"?
    – siggi_pop
    2 days ago










  • Hi siggi_pop, thanks for connecting. I wanted to distinguish columns through "-" A B C are the entries in column A and the numbers are the entries in Column B. And the match is based on the alphabet.In sheet 2, for A, the factor is 2, so A in sheet 2 matches with A in sheet 1 and returns a value in third column, say 15 (30/2).
    – ashwin
    2 days ago












  • Ok so based on your answer 'I wanted to distinguish rows through "-"' would mean that A - 2 is same as $A$2 ( column A, row 2 ) correct ?
    – siggi_pop
    2 days ago


















  • What do you mean by match? based on column name or based on cell value? when you write A - 2 does that mean cell $A$1 has value of 2? Or has the value "A - 2"?
    – siggi_pop
    2 days ago










  • Hi siggi_pop, thanks for connecting. I wanted to distinguish columns through "-" A B C are the entries in column A and the numbers are the entries in Column B. And the match is based on the alphabet.In sheet 2, for A, the factor is 2, so A in sheet 2 matches with A in sheet 1 and returns a value in third column, say 15 (30/2).
    – ashwin
    2 days ago












  • Ok so based on your answer 'I wanted to distinguish rows through "-"' would mean that A - 2 is same as $A$2 ( column A, row 2 ) correct ?
    – siggi_pop
    2 days ago
















What do you mean by match? based on column name or based on cell value? when you write A - 2 does that mean cell $A$1 has value of 2? Or has the value "A - 2"?
– siggi_pop
2 days ago




What do you mean by match? based on column name or based on cell value? when you write A - 2 does that mean cell $A$1 has value of 2? Or has the value "A - 2"?
– siggi_pop
2 days ago












Hi siggi_pop, thanks for connecting. I wanted to distinguish columns through "-" A B C are the entries in column A and the numbers are the entries in Column B. And the match is based on the alphabet.In sheet 2, for A, the factor is 2, so A in sheet 2 matches with A in sheet 1 and returns a value in third column, say 15 (30/2).
– ashwin
2 days ago






Hi siggi_pop, thanks for connecting. I wanted to distinguish columns through "-" A B C are the entries in column A and the numbers are the entries in Column B. And the match is based on the alphabet.In sheet 2, for A, the factor is 2, so A in sheet 2 matches with A in sheet 1 and returns a value in third column, say 15 (30/2).
– ashwin
2 days ago














Ok so based on your answer 'I wanted to distinguish rows through "-"' would mean that A - 2 is same as $A$2 ( column A, row 2 ) correct ?
– siggi_pop
2 days ago




Ok so based on your answer 'I wanted to distinguish rows through "-"' would mean that A - 2 is same as $A$2 ( column A, row 2 ) correct ?
– siggi_pop
2 days ago












2 Answers
2






active

oldest

votes

















up vote
0
down vote













You can use a vlookup() formula to go get the dividend. (assuming table 1 is on Sheet1 and table 2 in Sheet2 where we are doing this formula):



 =VLOOKUP(A1,Sheet1!A:B, 2, FALSE)/Sheet2!B1


enter image description here






share|improve this answer




























    up vote
    0
    down vote













    Since you mention table, with structured references, though it seems you are not applying those here:



    =VLOOKUP([@Column1],Table1[#All],2,0)/[@Column2]


    enter image description here






    share|improve this answer





















      Your Answer






      StackExchange.ifUsing("editor", function () {
      StackExchange.using("externalEditor", function () {
      StackExchange.using("snippets", function () {
      StackExchange.snippets.init();
      });
      });
      }, "code-snippets");

      StackExchange.ready(function() {
      var channelOptions = {
      tags: "".split(" "),
      id: "1"
      };
      initTagRenderer("".split(" "), "".split(" "), channelOptions);

      StackExchange.using("externalEditor", function() {
      // Have to fire editor after snippets, if snippets enabled
      if (StackExchange.settings.snippets.snippetsEnabled) {
      StackExchange.using("snippets", function() {
      createEditor();
      });
      }
      else {
      createEditor();
      }
      });

      function createEditor() {
      StackExchange.prepareEditor({
      heartbeatType: 'answer',
      convertImagesToLinks: true,
      noModals: true,
      showLowRepImageUploadWarning: true,
      reputationToPostImages: 10,
      bindNavPrevention: true,
      postfix: "",
      imageUploader: {
      brandingHtml: "Powered by u003ca class="icon-imgur-white" href="https://imgur.com/"u003eu003c/au003e",
      contentPolicyHtml: "User contributions licensed under u003ca href="https://creativecommons.org/licenses/by-sa/3.0/"u003ecc by-sa 3.0 with attribution requiredu003c/au003e u003ca href="https://stackoverflow.com/legal/content-policy"u003e(content policy)u003c/au003e",
      allowUrls: true
      },
      onDemand: true,
      discardSelector: ".discard-answer"
      ,immediatelyShowMarkdownHelp:true
      });


      }
      });














       

      draft saved


      draft discarded


















      StackExchange.ready(
      function () {
      StackExchange.openid.initPostLogin('.new-post-login', 'https%3a%2f%2fstackoverflow.com%2fquestions%2f53342331%2fhow-to-divide-two-cells-based-on-match%23new-answer', 'question_page');
      }
      );

      Post as a guest















      Required, but never shown

























      2 Answers
      2






      active

      oldest

      votes








      2 Answers
      2






      active

      oldest

      votes









      active

      oldest

      votes






      active

      oldest

      votes








      up vote
      0
      down vote













      You can use a vlookup() formula to go get the dividend. (assuming table 1 is on Sheet1 and table 2 in Sheet2 where we are doing this formula):



       =VLOOKUP(A1,Sheet1!A:B, 2, FALSE)/Sheet2!B1


      enter image description here






      share|improve this answer

























        up vote
        0
        down vote













        You can use a vlookup() formula to go get the dividend. (assuming table 1 is on Sheet1 and table 2 in Sheet2 where we are doing this formula):



         =VLOOKUP(A1,Sheet1!A:B, 2, FALSE)/Sheet2!B1


        enter image description here






        share|improve this answer























          up vote
          0
          down vote










          up vote
          0
          down vote









          You can use a vlookup() formula to go get the dividend. (assuming table 1 is on Sheet1 and table 2 in Sheet2 where we are doing this formula):



           =VLOOKUP(A1,Sheet1!A:B, 2, FALSE)/Sheet2!B1


          enter image description here






          share|improve this answer












          You can use a vlookup() formula to go get the dividend. (assuming table 1 is on Sheet1 and table 2 in Sheet2 where we are doing this formula):



           =VLOOKUP(A1,Sheet1!A:B, 2, FALSE)/Sheet2!B1


          enter image description here







          share|improve this answer












          share|improve this answer



          share|improve this answer










          answered 2 days ago









          JNevill

          30.9k31544




          30.9k31544
























              up vote
              0
              down vote













              Since you mention table, with structured references, though it seems you are not applying those here:



              =VLOOKUP([@Column1],Table1[#All],2,0)/[@Column2]


              enter image description here






              share|improve this answer

























                up vote
                0
                down vote













                Since you mention table, with structured references, though it seems you are not applying those here:



                =VLOOKUP([@Column1],Table1[#All],2,0)/[@Column2]


                enter image description here






                share|improve this answer























                  up vote
                  0
                  down vote










                  up vote
                  0
                  down vote









                  Since you mention table, with structured references, though it seems you are not applying those here:



                  =VLOOKUP([@Column1],Table1[#All],2,0)/[@Column2]


                  enter image description here






                  share|improve this answer












                  Since you mention table, with structured references, though it seems you are not applying those here:



                  =VLOOKUP([@Column1],Table1[#All],2,0)/[@Column2]


                  enter image description here







                  share|improve this answer












                  share|improve this answer



                  share|improve this answer










                  answered 2 days ago









                  pnuts

                  46.3k76197




                  46.3k76197






























                       

                      draft saved


                      draft discarded



















































                       


                      draft saved


                      draft discarded














                      StackExchange.ready(
                      function () {
                      StackExchange.openid.initPostLogin('.new-post-login', 'https%3a%2f%2fstackoverflow.com%2fquestions%2f53342331%2fhow-to-divide-two-cells-based-on-match%23new-answer', 'question_page');
                      }
                      );

                      Post as a guest















                      Required, but never shown





















































                      Required, but never shown














                      Required, but never shown












                      Required, but never shown







                      Required, but never shown

































                      Required, but never shown














                      Required, but never shown












                      Required, but never shown







                      Required, but never shown







                      -

                      Popular posts from this blog

                      "Incorrect syntax near the keyword 'ON'. (on update cascade, on delete cascade,)

                      Image
                      .everyoneloves__top-leaderboard:empty,.everyoneloves__mid-leaderboard:empty,.everyoneloves__bot-mid-leaderboard:empty{ height:90px;width:728px;box-sizing:border-box; } 2 total noob sorry any help greatly appreciated. I keep getting this "Incorrect syntax near the keyword 'ON'." heres my codes code one CREATE TABLE supplier ( supplierID INT PRIMARY KEY, ISBN INT FOREIGN KEY REFERENCES book(ISBN), price DECIMAL (5,2), ON UPDATE CASCADE, ON DELETE CASCADE, ); code two CREATE TABLE orders ( orderID INT PRIMARY KEY, customerID INT FOREIGN KEY REFERENCES customer(customerID), ISBN INT FOREIGN KEY REFERENCES book(ISBN), orderDate date, ON UPDATE CASCADE, ON DELETE CASCADE, ); Ive run out of stuff to try other than dumbing it down to uselessness. ...
                      Read more

                      Alcedinidae

                      Image
                      Alcedinidae Martin-pêcheur à dos bleu ( Alcedo azurea ) Classification (COI) Règne Animalia Embranchement Chordata Sous-embr. Vertebrata Classe Aves Ordre Coraciiformes Famille Alcedinidae Rafinesque, 1815 Les Alcédinidés ou Alcedinidae forment une famille d'oiseaux plus connus sous les noms de martins-pêcheurs et martins-chasseurs. Sommaire 1 Description 2 Répartition et habitat 3 Systématique 3.1 Liste alphabétique des genres 3.2 Liste des espèces 4 Chez les Anciens : l'alcyon 4.1 Étymologie (Littré) 4.2 Oiseau « fabuleux » ? 5 Notes 6 Articles connexes 7 Liens externes 7.1 Bibliographie Description | Ce sont des oiseaux compacts de taille petite à moyenne (10 à 46 cm), au bec droit et long, en forme de poignard. Leurs pattes sont courtes, et ils portent un plumage aux couleurs vives. Répartition et habitat | Cosmopolites, ils fréquentent surtout ...
                      Read more

                      Origin of the phrase “under your belt”?

                      Image
                      .everyoneloves__top-leaderboard:empty,.everyoneloves__mid-leaderboard:empty,.everyoneloves__bot-mid-leaderboard:empty{ margin-bottom:0; } 17 1 Today, I found myself discussing what students should have "under their belts" during a lecture, and I wondered to myself if there was some inappropriate undertone here that I might be unaware of. Question : What is the origin of the phrase "under your belt"? A google search revealed some webpages (e.g. [1]) that assert its origin is related to consumption of food -- once a meal has been eaten, it's under your belt (which is a relief). However, with such websites, I have no way to determine fact from "random guy on the internet making stuff up". etymology idioms ...
                      Read more