Excel Vlookup assistance












1















I have a table in which I need to get the vlookup to work using two criteria:




  1. Based on sheet 2 of list of vendors column H should automatically select payment terms (Advance/Credit).


Sheet 2




  1. Based on the date on which payment is made reflected in column I, the vlookup above should be superseded to change status to "completed."


Sheet 1



I hope somebody can help. Thanks.










share|improve this question





























    1















    I have a table in which I need to get the vlookup to work using two criteria:




    1. Based on sheet 2 of list of vendors column H should automatically select payment terms (Advance/Credit).


    Sheet 2




    1. Based on the date on which payment is made reflected in column I, the vlookup above should be superseded to change status to "completed."


    Sheet 1



    I hope somebody can help. Thanks.










    share|improve this question



























      1












      1








      1








      I have a table in which I need to get the vlookup to work using two criteria:




      1. Based on sheet 2 of list of vendors column H should automatically select payment terms (Advance/Credit).


      Sheet 2




      1. Based on the date on which payment is made reflected in column I, the vlookup above should be superseded to change status to "completed."


      Sheet 1



      I hope somebody can help. Thanks.










      share|improve this question
















      I have a table in which I need to get the vlookup to work using two criteria:




      1. Based on sheet 2 of list of vendors column H should automatically select payment terms (Advance/Credit).


      Sheet 2




      1. Based on the date on which payment is made reflected in column I, the vlookup above should be superseded to change status to "completed."


      Sheet 1



      I hope somebody can help. Thanks.







      microsoft-excel






      share|improve this question















      share|improve this question













      share|improve this question




      share|improve this question








      edited Jan 28 at 13:38









      Run5k

      11.5k73254




      11.5k73254










      asked Jan 28 at 11:02









      Rachan DsouzaRachan Dsouza

      82




      82






















          1 Answer
          1






          active

          oldest

          votes


















          2














          Write the formulas one step at a time.



          Use a formula just for your first step, the VLOOKUP command. The syntax is:



              =VLOOKUP(supplierName,supplierLookupRange,6,false)


          In your spreadsheet, go:



          j2: =VLOOKUP(C3,Sheet2!C:H,6,false)


          Then use a separate cell, with another formula to override:



          k2: =if(I2<=TODAY(),"Completed", J2)


          The final answer is then in K2. Check that it works. Copy the formulas in J and K downward.



          Then consolidate your formulas.



          You can consolidate J2 and K2 into H2 if you prefer, to have a single cell that does all the work.



          if you have:



          j2: =VLOOKUP(c3,Sheet2!C:H,6,FALSE)
          k2: =IF(I2<=TODAY(),"Completed", H3)


          Then go:



          h2:  =IF(I2<=TODAY(),"Completed", 
          VLOOKUP(C3,Sheet2!C:H,6,FALSE))


          If it works, copy the formula downward.



          Yes, you can have "returns" in the middle of a formula if it helps you read them more easily. (The "return" character is Alt-Enter on most platforms.)



          Then, if needed:



          Make it pretty: Add Error Handling.



          If there isn't an entry in the 'lookup table', your VLOOKUP will produce an error. You can fix this for a better appearance. There are several ways to do this, but here is an easy one. If you have:



          j2: =VLOOKUP(c3,Sheet2!C:H,6,FALSE)


          Then use an IF statement:



               IF (the lookup is valid), (then put the answer), ELSE (put an error message).


          which is the same as:



               IF (the lookup fails), THEN (put an error message), ELSE (put the answer).


          In Excel terms, that's:



          l2: =IF(
          ISERROR(VLOOKUP(c3,Sheet2!C:H,6,FALSE),
          "Not Found",
          VLOOKUP(c3,Sheet2!C:H,6,FALSE)
          )


          Yes, you have to repeat the VLOOKUP clause, but you can use "returns" to make the formula easy to read. (You can resize the formula bar if you can't see the whole thing).



          If you like the result, then consolidate the formula. Replace your VLOOKUP with the IF..VLOOKUP version.



             h2:  =IF(I2<=TODAY(),"Completed", 
          IF(
          ISERROR(VLOOKUP(c3,Sheet2!C:H,6,FALSE),
          "Not Found",
          VLOOKUP(c3,Sheet2!C:H,6,FALSE)
          )
          )


          Copy the formula down the column. If everything is working, you can delete (or hide) the formulas in columns J..K..L that you used to build it.



          You're Done.



          This is a good way to build complicated formulas. Break it down into tiny parts, check the individual parts work, then build them up (consolidate the parts) into a single formula, check it works, then add error handling for "weird situations" once everything seems to be working.






          share|improve this answer


























          • Dear #whiskeychief, yes, that formula worked perfectly. I do have one question though, how do I get the cells to come up blank instead of "0" when I do not have any data entered in Payment terms?

            – Rachan Dsouza
            Jan 28 at 11:58








          • 1





            Rewritten and added information about error-handling. If this answer worked for you, please "accept" it by pressing the checkmark icon. If not, please let me know.

            – whiskeychief
            Jan 29 at 11:08











          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',
          autoActivateHeartbeat: false,
          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%2f1399200%2fexcel-vlookup-assistance%23new-answer', 'question_page');
          }
          );

          Post as a guest















          Required, but never shown

























          1 Answer
          1






          active

          oldest

          votes








          1 Answer
          1






          active

          oldest

          votes









          active

          oldest

          votes






          active

          oldest

          votes









          2














          Write the formulas one step at a time.



          Use a formula just for your first step, the VLOOKUP command. The syntax is:



              =VLOOKUP(supplierName,supplierLookupRange,6,false)


          In your spreadsheet, go:



          j2: =VLOOKUP(C3,Sheet2!C:H,6,false)


          Then use a separate cell, with another formula to override:



          k2: =if(I2<=TODAY(),"Completed", J2)


          The final answer is then in K2. Check that it works. Copy the formulas in J and K downward.



          Then consolidate your formulas.



          You can consolidate J2 and K2 into H2 if you prefer, to have a single cell that does all the work.



          if you have:



          j2: =VLOOKUP(c3,Sheet2!C:H,6,FALSE)
          k2: =IF(I2<=TODAY(),"Completed", H3)


          Then go:



          h2:  =IF(I2<=TODAY(),"Completed", 
          VLOOKUP(C3,Sheet2!C:H,6,FALSE))


          If it works, copy the formula downward.



          Yes, you can have "returns" in the middle of a formula if it helps you read them more easily. (The "return" character is Alt-Enter on most platforms.)



          Then, if needed:



          Make it pretty: Add Error Handling.



          If there isn't an entry in the 'lookup table', your VLOOKUP will produce an error. You can fix this for a better appearance. There are several ways to do this, but here is an easy one. If you have:



          j2: =VLOOKUP(c3,Sheet2!C:H,6,FALSE)


          Then use an IF statement:



               IF (the lookup is valid), (then put the answer), ELSE (put an error message).


          which is the same as:



               IF (the lookup fails), THEN (put an error message), ELSE (put the answer).


          In Excel terms, that's:



          l2: =IF(
          ISERROR(VLOOKUP(c3,Sheet2!C:H,6,FALSE),
          "Not Found",
          VLOOKUP(c3,Sheet2!C:H,6,FALSE)
          )


          Yes, you have to repeat the VLOOKUP clause, but you can use "returns" to make the formula easy to read. (You can resize the formula bar if you can't see the whole thing).



          If you like the result, then consolidate the formula. Replace your VLOOKUP with the IF..VLOOKUP version.



             h2:  =IF(I2<=TODAY(),"Completed", 
          IF(
          ISERROR(VLOOKUP(c3,Sheet2!C:H,6,FALSE),
          "Not Found",
          VLOOKUP(c3,Sheet2!C:H,6,FALSE)
          )
          )


          Copy the formula down the column. If everything is working, you can delete (or hide) the formulas in columns J..K..L that you used to build it.



          You're Done.



          This is a good way to build complicated formulas. Break it down into tiny parts, check the individual parts work, then build them up (consolidate the parts) into a single formula, check it works, then add error handling for "weird situations" once everything seems to be working.






          share|improve this answer


























          • Dear #whiskeychief, yes, that formula worked perfectly. I do have one question though, how do I get the cells to come up blank instead of "0" when I do not have any data entered in Payment terms?

            – Rachan Dsouza
            Jan 28 at 11:58








          • 1





            Rewritten and added information about error-handling. If this answer worked for you, please "accept" it by pressing the checkmark icon. If not, please let me know.

            – whiskeychief
            Jan 29 at 11:08
















          2














          Write the formulas one step at a time.



          Use a formula just for your first step, the VLOOKUP command. The syntax is:



              =VLOOKUP(supplierName,supplierLookupRange,6,false)


          In your spreadsheet, go:



          j2: =VLOOKUP(C3,Sheet2!C:H,6,false)


          Then use a separate cell, with another formula to override:



          k2: =if(I2<=TODAY(),"Completed", J2)


          The final answer is then in K2. Check that it works. Copy the formulas in J and K downward.



          Then consolidate your formulas.



          You can consolidate J2 and K2 into H2 if you prefer, to have a single cell that does all the work.



          if you have:



          j2: =VLOOKUP(c3,Sheet2!C:H,6,FALSE)
          k2: =IF(I2<=TODAY(),"Completed", H3)


          Then go:



          h2:  =IF(I2<=TODAY(),"Completed", 
          VLOOKUP(C3,Sheet2!C:H,6,FALSE))


          If it works, copy the formula downward.



          Yes, you can have "returns" in the middle of a formula if it helps you read them more easily. (The "return" character is Alt-Enter on most platforms.)



          Then, if needed:



          Make it pretty: Add Error Handling.



          If there isn't an entry in the 'lookup table', your VLOOKUP will produce an error. You can fix this for a better appearance. There are several ways to do this, but here is an easy one. If you have:



          j2: =VLOOKUP(c3,Sheet2!C:H,6,FALSE)


          Then use an IF statement:



               IF (the lookup is valid), (then put the answer), ELSE (put an error message).


          which is the same as:



               IF (the lookup fails), THEN (put an error message), ELSE (put the answer).


          In Excel terms, that's:



          l2: =IF(
          ISERROR(VLOOKUP(c3,Sheet2!C:H,6,FALSE),
          "Not Found",
          VLOOKUP(c3,Sheet2!C:H,6,FALSE)
          )


          Yes, you have to repeat the VLOOKUP clause, but you can use "returns" to make the formula easy to read. (You can resize the formula bar if you can't see the whole thing).



          If you like the result, then consolidate the formula. Replace your VLOOKUP with the IF..VLOOKUP version.



             h2:  =IF(I2<=TODAY(),"Completed", 
          IF(
          ISERROR(VLOOKUP(c3,Sheet2!C:H,6,FALSE),
          "Not Found",
          VLOOKUP(c3,Sheet2!C:H,6,FALSE)
          )
          )


          Copy the formula down the column. If everything is working, you can delete (or hide) the formulas in columns J..K..L that you used to build it.



          You're Done.



          This is a good way to build complicated formulas. Break it down into tiny parts, check the individual parts work, then build them up (consolidate the parts) into a single formula, check it works, then add error handling for "weird situations" once everything seems to be working.






          share|improve this answer


























          • Dear #whiskeychief, yes, that formula worked perfectly. I do have one question though, how do I get the cells to come up blank instead of "0" when I do not have any data entered in Payment terms?

            – Rachan Dsouza
            Jan 28 at 11:58








          • 1





            Rewritten and added information about error-handling. If this answer worked for you, please "accept" it by pressing the checkmark icon. If not, please let me know.

            – whiskeychief
            Jan 29 at 11:08














          2












          2








          2







          Write the formulas one step at a time.



          Use a formula just for your first step, the VLOOKUP command. The syntax is:



              =VLOOKUP(supplierName,supplierLookupRange,6,false)


          In your spreadsheet, go:



          j2: =VLOOKUP(C3,Sheet2!C:H,6,false)


          Then use a separate cell, with another formula to override:



          k2: =if(I2<=TODAY(),"Completed", J2)


          The final answer is then in K2. Check that it works. Copy the formulas in J and K downward.



          Then consolidate your formulas.



          You can consolidate J2 and K2 into H2 if you prefer, to have a single cell that does all the work.



          if you have:



          j2: =VLOOKUP(c3,Sheet2!C:H,6,FALSE)
          k2: =IF(I2<=TODAY(),"Completed", H3)


          Then go:



          h2:  =IF(I2<=TODAY(),"Completed", 
          VLOOKUP(C3,Sheet2!C:H,6,FALSE))


          If it works, copy the formula downward.



          Yes, you can have "returns" in the middle of a formula if it helps you read them more easily. (The "return" character is Alt-Enter on most platforms.)



          Then, if needed:



          Make it pretty: Add Error Handling.



          If there isn't an entry in the 'lookup table', your VLOOKUP will produce an error. You can fix this for a better appearance. There are several ways to do this, but here is an easy one. If you have:



          j2: =VLOOKUP(c3,Sheet2!C:H,6,FALSE)


          Then use an IF statement:



               IF (the lookup is valid), (then put the answer), ELSE (put an error message).


          which is the same as:



               IF (the lookup fails), THEN (put an error message), ELSE (put the answer).


          In Excel terms, that's:



          l2: =IF(
          ISERROR(VLOOKUP(c3,Sheet2!C:H,6,FALSE),
          "Not Found",
          VLOOKUP(c3,Sheet2!C:H,6,FALSE)
          )


          Yes, you have to repeat the VLOOKUP clause, but you can use "returns" to make the formula easy to read. (You can resize the formula bar if you can't see the whole thing).



          If you like the result, then consolidate the formula. Replace your VLOOKUP with the IF..VLOOKUP version.



             h2:  =IF(I2<=TODAY(),"Completed", 
          IF(
          ISERROR(VLOOKUP(c3,Sheet2!C:H,6,FALSE),
          "Not Found",
          VLOOKUP(c3,Sheet2!C:H,6,FALSE)
          )
          )


          Copy the formula down the column. If everything is working, you can delete (or hide) the formulas in columns J..K..L that you used to build it.



          You're Done.



          This is a good way to build complicated formulas. Break it down into tiny parts, check the individual parts work, then build them up (consolidate the parts) into a single formula, check it works, then add error handling for "weird situations" once everything seems to be working.






          share|improve this answer















          Write the formulas one step at a time.



          Use a formula just for your first step, the VLOOKUP command. The syntax is:



              =VLOOKUP(supplierName,supplierLookupRange,6,false)


          In your spreadsheet, go:



          j2: =VLOOKUP(C3,Sheet2!C:H,6,false)


          Then use a separate cell, with another formula to override:



          k2: =if(I2<=TODAY(),"Completed", J2)


          The final answer is then in K2. Check that it works. Copy the formulas in J and K downward.



          Then consolidate your formulas.



          You can consolidate J2 and K2 into H2 if you prefer, to have a single cell that does all the work.



          if you have:



          j2: =VLOOKUP(c3,Sheet2!C:H,6,FALSE)
          k2: =IF(I2<=TODAY(),"Completed", H3)


          Then go:



          h2:  =IF(I2<=TODAY(),"Completed", 
          VLOOKUP(C3,Sheet2!C:H,6,FALSE))


          If it works, copy the formula downward.



          Yes, you can have "returns" in the middle of a formula if it helps you read them more easily. (The "return" character is Alt-Enter on most platforms.)



          Then, if needed:



          Make it pretty: Add Error Handling.



          If there isn't an entry in the 'lookup table', your VLOOKUP will produce an error. You can fix this for a better appearance. There are several ways to do this, but here is an easy one. If you have:



          j2: =VLOOKUP(c3,Sheet2!C:H,6,FALSE)


          Then use an IF statement:



               IF (the lookup is valid), (then put the answer), ELSE (put an error message).


          which is the same as:



               IF (the lookup fails), THEN (put an error message), ELSE (put the answer).


          In Excel terms, that's:



          l2: =IF(
          ISERROR(VLOOKUP(c3,Sheet2!C:H,6,FALSE),
          "Not Found",
          VLOOKUP(c3,Sheet2!C:H,6,FALSE)
          )


          Yes, you have to repeat the VLOOKUP clause, but you can use "returns" to make the formula easy to read. (You can resize the formula bar if you can't see the whole thing).



          If you like the result, then consolidate the formula. Replace your VLOOKUP with the IF..VLOOKUP version.



             h2:  =IF(I2<=TODAY(),"Completed", 
          IF(
          ISERROR(VLOOKUP(c3,Sheet2!C:H,6,FALSE),
          "Not Found",
          VLOOKUP(c3,Sheet2!C:H,6,FALSE)
          )
          )


          Copy the formula down the column. If everything is working, you can delete (or hide) the formulas in columns J..K..L that you used to build it.



          You're Done.



          This is a good way to build complicated formulas. Break it down into tiny parts, check the individual parts work, then build them up (consolidate the parts) into a single formula, check it works, then add error handling for "weird situations" once everything seems to be working.







          share|improve this answer














          share|improve this answer



          share|improve this answer








          edited Jan 29 at 11:12

























          answered Jan 28 at 11:33









          whiskeychiefwhiskeychief

          607




          607













          • Dear #whiskeychief, yes, that formula worked perfectly. I do have one question though, how do I get the cells to come up blank instead of "0" when I do not have any data entered in Payment terms?

            – Rachan Dsouza
            Jan 28 at 11:58








          • 1





            Rewritten and added information about error-handling. If this answer worked for you, please "accept" it by pressing the checkmark icon. If not, please let me know.

            – whiskeychief
            Jan 29 at 11:08



















          • Dear #whiskeychief, yes, that formula worked perfectly. I do have one question though, how do I get the cells to come up blank instead of "0" when I do not have any data entered in Payment terms?

            – Rachan Dsouza
            Jan 28 at 11:58








          • 1





            Rewritten and added information about error-handling. If this answer worked for you, please "accept" it by pressing the checkmark icon. If not, please let me know.

            – whiskeychief
            Jan 29 at 11:08

















          Dear #whiskeychief, yes, that formula worked perfectly. I do have one question though, how do I get the cells to come up blank instead of "0" when I do not have any data entered in Payment terms?

          – Rachan Dsouza
          Jan 28 at 11:58







          Dear #whiskeychief, yes, that formula worked perfectly. I do have one question though, how do I get the cells to come up blank instead of "0" when I do not have any data entered in Payment terms?

          – Rachan Dsouza
          Jan 28 at 11:58






          1




          1





          Rewritten and added information about error-handling. If this answer worked for you, please "accept" it by pressing the checkmark icon. If not, please let me know.

          – whiskeychief
          Jan 29 at 11:08





          Rewritten and added information about error-handling. If this answer worked for you, please "accept" it by pressing the checkmark icon. If not, please let me know.

          – whiskeychief
          Jan 29 at 11:08


















          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.




          draft saved


          draft discarded














          StackExchange.ready(
          function () {
          StackExchange.openid.initPostLogin('.new-post-login', 'https%3a%2f%2fsuperuser.com%2fquestions%2f1399200%2fexcel-vlookup-assistance%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

          Plaza Victoria

          Puebla de Zaragoza

          Musa