Excel formula that returns row if value is found in a column











up vote
1
down vote

favorite












I have a list of DNS entries that I need to sort to get the good records.



In sheet1, I have a dump of the raw data, in column 1 is a zone ID which is a number.



In sheet2, I have a column made up of the zone ID's that I want to keep.



On sheet3 I am looking for a way to take sheet1 column 1, to see if it matches one of the values in sheet2 column 1. If it does, then the result should be the entire row into sheet 3.



Is this possible? Data example is below:



Sheet1 - 4 columns



1   foo            A     IP_Address

1 foomaster CNAME IP_Address

392 jimmy A IP_Address


Sheet2



In column1 is a list of acceptable zone ID's I want.



Sheet3



If value from sheet1-column1 exists in sheet2-column1, paste the entire row from sheet1.










share|improve this question
























  • It's a simple join, then. Use a database, that's what they're made for.
    – Daniel B
    Feb 18 '15 at 22:04















up vote
1
down vote

favorite












I have a list of DNS entries that I need to sort to get the good records.



In sheet1, I have a dump of the raw data, in column 1 is a zone ID which is a number.



In sheet2, I have a column made up of the zone ID's that I want to keep.



On sheet3 I am looking for a way to take sheet1 column 1, to see if it matches one of the values in sheet2 column 1. If it does, then the result should be the entire row into sheet 3.



Is this possible? Data example is below:



Sheet1 - 4 columns



1   foo            A     IP_Address

1 foomaster CNAME IP_Address

392 jimmy A IP_Address


Sheet2



In column1 is a list of acceptable zone ID's I want.



Sheet3



If value from sheet1-column1 exists in sheet2-column1, paste the entire row from sheet1.










share|improve this question
























  • It's a simple join, then. Use a database, that's what they're made for.
    – Daniel B
    Feb 18 '15 at 22:04













up vote
1
down vote

favorite









up vote
1
down vote

favorite











I have a list of DNS entries that I need to sort to get the good records.



In sheet1, I have a dump of the raw data, in column 1 is a zone ID which is a number.



In sheet2, I have a column made up of the zone ID's that I want to keep.



On sheet3 I am looking for a way to take sheet1 column 1, to see if it matches one of the values in sheet2 column 1. If it does, then the result should be the entire row into sheet 3.



Is this possible? Data example is below:



Sheet1 - 4 columns



1   foo            A     IP_Address

1 foomaster CNAME IP_Address

392 jimmy A IP_Address


Sheet2



In column1 is a list of acceptable zone ID's I want.



Sheet3



If value from sheet1-column1 exists in sheet2-column1, paste the entire row from sheet1.










share|improve this question















I have a list of DNS entries that I need to sort to get the good records.



In sheet1, I have a dump of the raw data, in column 1 is a zone ID which is a number.



In sheet2, I have a column made up of the zone ID's that I want to keep.



On sheet3 I am looking for a way to take sheet1 column 1, to see if it matches one of the values in sheet2 column 1. If it does, then the result should be the entire row into sheet 3.



Is this possible? Data example is below:



Sheet1 - 4 columns



1   foo            A     IP_Address

1 foomaster CNAME IP_Address

392 jimmy A IP_Address


Sheet2



In column1 is a list of acceptable zone ID's I want.



Sheet3



If value from sheet1-column1 exists in sheet2-column1, paste the entire row from sheet1.







microsoft-excel-2010






share|improve this question















share|improve this question













share|improve this question




share|improve this question








edited Feb 18 '15 at 22:01









Greenonline

1,2562823




1,2562823










asked Feb 18 '15 at 21:33









Grady

612




612












  • It's a simple join, then. Use a database, that's what they're made for.
    – Daniel B
    Feb 18 '15 at 22:04


















  • It's a simple join, then. Use a database, that's what they're made for.
    – Daniel B
    Feb 18 '15 at 22:04
















It's a simple join, then. Use a database, that's what they're made for.
– Daniel B
Feb 18 '15 at 22:04




It's a simple join, then. Use a database, that's what they're made for.
– Daniel B
Feb 18 '15 at 22:04










2 Answers
2






active

oldest

votes

















up vote
0
down vote













One quick and dirty way to do it is with =COUNTIF(). If the value is found, return the value from cell A1, B1, C1, etc. by filling the formula to the right.



In Sheet 3, Cell A1, enter the following:



=IF(COUNTIF(Sheet2!$A:$A,Sheet1!$A1),Sheet1!A1)



Now use the Fill right (Ctrl+R) and Fill down (Ctrl+D) features to apply the formula to as many cells as required, depending on the number of columns+rows expected in the raw data you have in Sheet 1. If the search is successful, it will fill out the data from that row in Sheet 1.



If the search is unsuccessful, the row will return FALSE. If a cell on sheet 1 does not have data, it will return 0. If desired, you can return blank text ("") instead of a FALSE or a 0 with a formula like:



=IF(COUNTIF(Sheet2!$A:$A,Sheet1!$A1),IF(ISBLANK(Sheet1!A1),"",Sheet1!A1),"")



To say it again - this is quick and dirty and will have performance implications if you have a large dataset. You are typically better off putting your raw data in a database - you can then use a Pivot Table or simple SQL queries to extract the data you need in the format required.






share|improve this answer




























    up vote
    0
    down vote













    Ok, but you need a third sheet for this trick. In this third sheet, you'll need to drag this formula right and down, to match the size of the table(sheet1) where the values you will be looking for reside. Sheet2 will be where we will try to find said values.



    =index('sheet2'!A$1:A$1000,Match('sheet1'$D1:$D1000,'sheet2'!$A$1:$A$1000,0))


    So this is like a dragable vlookup where you will be filling a new table the same size as sheet1, in the case it doesn´t find any match it will return #N/A, if you want to handle that then sorround it in a IFERROR like this



    =IFERROR(index('sheet2'!A$1:A$1000,Match('sheet1'$D1:$D1000,'sheet2'!$A$1:$A$1000,0));"NO MATCH")



    Then you will need to filter and delete the "NO MATCH" entries. But this is the formula I personally use for this kind of things.



    Maybe the ,, ', etc; sintax is not the same to you because of locale and different Excel versions.



    Cheers






    share|improve this answer





















      Your Answer








      StackExchange.ready(function() {
      var channelOptions = {
      tags: "".split(" "),
      id: "3"
      };
      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%2fsuperuser.com%2fquestions%2f879675%2fexcel-formula-that-returns-row-if-value-is-found-in-a-column%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













      One quick and dirty way to do it is with =COUNTIF(). If the value is found, return the value from cell A1, B1, C1, etc. by filling the formula to the right.



      In Sheet 3, Cell A1, enter the following:



      =IF(COUNTIF(Sheet2!$A:$A,Sheet1!$A1),Sheet1!A1)



      Now use the Fill right (Ctrl+R) and Fill down (Ctrl+D) features to apply the formula to as many cells as required, depending on the number of columns+rows expected in the raw data you have in Sheet 1. If the search is successful, it will fill out the data from that row in Sheet 1.



      If the search is unsuccessful, the row will return FALSE. If a cell on sheet 1 does not have data, it will return 0. If desired, you can return blank text ("") instead of a FALSE or a 0 with a formula like:



      =IF(COUNTIF(Sheet2!$A:$A,Sheet1!$A1),IF(ISBLANK(Sheet1!A1),"",Sheet1!A1),"")



      To say it again - this is quick and dirty and will have performance implications if you have a large dataset. You are typically better off putting your raw data in a database - you can then use a Pivot Table or simple SQL queries to extract the data you need in the format required.






      share|improve this answer

























        up vote
        0
        down vote













        One quick and dirty way to do it is with =COUNTIF(). If the value is found, return the value from cell A1, B1, C1, etc. by filling the formula to the right.



        In Sheet 3, Cell A1, enter the following:



        =IF(COUNTIF(Sheet2!$A:$A,Sheet1!$A1),Sheet1!A1)



        Now use the Fill right (Ctrl+R) and Fill down (Ctrl+D) features to apply the formula to as many cells as required, depending on the number of columns+rows expected in the raw data you have in Sheet 1. If the search is successful, it will fill out the data from that row in Sheet 1.



        If the search is unsuccessful, the row will return FALSE. If a cell on sheet 1 does not have data, it will return 0. If desired, you can return blank text ("") instead of a FALSE or a 0 with a formula like:



        =IF(COUNTIF(Sheet2!$A:$A,Sheet1!$A1),IF(ISBLANK(Sheet1!A1),"",Sheet1!A1),"")



        To say it again - this is quick and dirty and will have performance implications if you have a large dataset. You are typically better off putting your raw data in a database - you can then use a Pivot Table or simple SQL queries to extract the data you need in the format required.






        share|improve this answer























          up vote
          0
          down vote










          up vote
          0
          down vote









          One quick and dirty way to do it is with =COUNTIF(). If the value is found, return the value from cell A1, B1, C1, etc. by filling the formula to the right.



          In Sheet 3, Cell A1, enter the following:



          =IF(COUNTIF(Sheet2!$A:$A,Sheet1!$A1),Sheet1!A1)



          Now use the Fill right (Ctrl+R) and Fill down (Ctrl+D) features to apply the formula to as many cells as required, depending on the number of columns+rows expected in the raw data you have in Sheet 1. If the search is successful, it will fill out the data from that row in Sheet 1.



          If the search is unsuccessful, the row will return FALSE. If a cell on sheet 1 does not have data, it will return 0. If desired, you can return blank text ("") instead of a FALSE or a 0 with a formula like:



          =IF(COUNTIF(Sheet2!$A:$A,Sheet1!$A1),IF(ISBLANK(Sheet1!A1),"",Sheet1!A1),"")



          To say it again - this is quick and dirty and will have performance implications if you have a large dataset. You are typically better off putting your raw data in a database - you can then use a Pivot Table or simple SQL queries to extract the data you need in the format required.






          share|improve this answer












          One quick and dirty way to do it is with =COUNTIF(). If the value is found, return the value from cell A1, B1, C1, etc. by filling the formula to the right.



          In Sheet 3, Cell A1, enter the following:



          =IF(COUNTIF(Sheet2!$A:$A,Sheet1!$A1),Sheet1!A1)



          Now use the Fill right (Ctrl+R) and Fill down (Ctrl+D) features to apply the formula to as many cells as required, depending on the number of columns+rows expected in the raw data you have in Sheet 1. If the search is successful, it will fill out the data from that row in Sheet 1.



          If the search is unsuccessful, the row will return FALSE. If a cell on sheet 1 does not have data, it will return 0. If desired, you can return blank text ("") instead of a FALSE or a 0 with a formula like:



          =IF(COUNTIF(Sheet2!$A:$A,Sheet1!$A1),IF(ISBLANK(Sheet1!A1),"",Sheet1!A1),"")



          To say it again - this is quick and dirty and will have performance implications if you have a large dataset. You are typically better off putting your raw data in a database - you can then use a Pivot Table or simple SQL queries to extract the data you need in the format required.







          share|improve this answer












          share|improve this answer



          share|improve this answer










          answered Feb 19 '15 at 0:48







          user387876































              up vote
              0
              down vote













              Ok, but you need a third sheet for this trick. In this third sheet, you'll need to drag this formula right and down, to match the size of the table(sheet1) where the values you will be looking for reside. Sheet2 will be where we will try to find said values.



              =index('sheet2'!A$1:A$1000,Match('sheet1'$D1:$D1000,'sheet2'!$A$1:$A$1000,0))


              So this is like a dragable vlookup where you will be filling a new table the same size as sheet1, in the case it doesn´t find any match it will return #N/A, if you want to handle that then sorround it in a IFERROR like this



              =IFERROR(index('sheet2'!A$1:A$1000,Match('sheet1'$D1:$D1000,'sheet2'!$A$1:$A$1000,0));"NO MATCH")



              Then you will need to filter and delete the "NO MATCH" entries. But this is the formula I personally use for this kind of things.



              Maybe the ,, ', etc; sintax is not the same to you because of locale and different Excel versions.



              Cheers






              share|improve this answer

























                up vote
                0
                down vote













                Ok, but you need a third sheet for this trick. In this third sheet, you'll need to drag this formula right and down, to match the size of the table(sheet1) where the values you will be looking for reside. Sheet2 will be where we will try to find said values.



                =index('sheet2'!A$1:A$1000,Match('sheet1'$D1:$D1000,'sheet2'!$A$1:$A$1000,0))


                So this is like a dragable vlookup where you will be filling a new table the same size as sheet1, in the case it doesn´t find any match it will return #N/A, if you want to handle that then sorround it in a IFERROR like this



                =IFERROR(index('sheet2'!A$1:A$1000,Match('sheet1'$D1:$D1000,'sheet2'!$A$1:$A$1000,0));"NO MATCH")



                Then you will need to filter and delete the "NO MATCH" entries. But this is the formula I personally use for this kind of things.



                Maybe the ,, ', etc; sintax is not the same to you because of locale and different Excel versions.



                Cheers






                share|improve this answer























                  up vote
                  0
                  down vote










                  up vote
                  0
                  down vote









                  Ok, but you need a third sheet for this trick. In this third sheet, you'll need to drag this formula right and down, to match the size of the table(sheet1) where the values you will be looking for reside. Sheet2 will be where we will try to find said values.



                  =index('sheet2'!A$1:A$1000,Match('sheet1'$D1:$D1000,'sheet2'!$A$1:$A$1000,0))


                  So this is like a dragable vlookup where you will be filling a new table the same size as sheet1, in the case it doesn´t find any match it will return #N/A, if you want to handle that then sorround it in a IFERROR like this



                  =IFERROR(index('sheet2'!A$1:A$1000,Match('sheet1'$D1:$D1000,'sheet2'!$A$1:$A$1000,0));"NO MATCH")



                  Then you will need to filter and delete the "NO MATCH" entries. But this is the formula I personally use for this kind of things.



                  Maybe the ,, ', etc; sintax is not the same to you because of locale and different Excel versions.



                  Cheers






                  share|improve this answer












                  Ok, but you need a third sheet for this trick. In this third sheet, you'll need to drag this formula right and down, to match the size of the table(sheet1) where the values you will be looking for reside. Sheet2 will be where we will try to find said values.



                  =index('sheet2'!A$1:A$1000,Match('sheet1'$D1:$D1000,'sheet2'!$A$1:$A$1000,0))


                  So this is like a dragable vlookup where you will be filling a new table the same size as sheet1, in the case it doesn´t find any match it will return #N/A, if you want to handle that then sorround it in a IFERROR like this



                  =IFERROR(index('sheet2'!A$1:A$1000,Match('sheet1'$D1:$D1000,'sheet2'!$A$1:$A$1000,0));"NO MATCH")



                  Then you will need to filter and delete the "NO MATCH" entries. But this is the formula I personally use for this kind of things.



                  Maybe the ,, ', etc; sintax is not the same to you because of locale and different Excel versions.



                  Cheers







                  share|improve this answer












                  share|improve this answer



                  share|improve this answer










                  answered Oct 18 at 17:45









                  dmb

                  557212




                  557212






























                      draft saved

                      draft discarded




















































                      Thanks for contributing an answer to Super User!


                      • Please be sure to answer the question. Provide details and share your research!

                      But avoid



                      • Asking for help, clarification, or responding to other answers.

                      • Making statements based on opinion; back them up with references or personal experience.


                      To learn more, see our tips on writing great answers.





                      Some of your past answers have not been well-received, and you're in danger of being blocked from answering.


                      Please pay close attention to the following guidance:


                      • Please be sure to answer the question. Provide details and share your research!

                      But avoid



                      • Asking for help, clarification, or responding to other answers.

                      • Making statements based on opinion; back them up with references or personal experience.


                      To learn more, see our tips on writing great answers.




                      draft saved


                      draft discarded














                      StackExchange.ready(
                      function () {
                      StackExchange.openid.initPostLogin('.new-post-login', 'https%3a%2f%2fsuperuser.com%2fquestions%2f879675%2fexcel-formula-that-returns-row-if-value-is-found-in-a-column%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

                      AnyDesk - Fatal Program Failure

                      How to calibrate 16:9 built-in touch-screen to a 4:3 resolution?

                      QoS: MAC-Priority for clients behind a repeater