Argthtjtr

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).

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

I hope somebody can help. Thanks.

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.