# Thread: If statement and Vlookup (Excel 2000)

1. ## If statement and Vlookup (Excel 2000)

I need to write an If Statement that if true will look up data in a vlookup table, if false - do nothing. The problem is I need to lookup B2 and G2. This is the way it should go: In cell N2 I need to write something like this. If M2 is blank then Vlookup( B2 and G2, \$Y\$2:\$AD\$532,6) Can you help me correct the formula for N2?

2. ## Re: If statement and Vlookup (Excel 2000)

Cindy,
I do not understand what you are trying to accomplish.
What is in cells B2 and G2? Do you want to do the same thing if either of these is found in the VLookup?
You may want to attach a sample workbook.

Chuck

3. ## Re: If statement and Vlookup (Excel 2000)

B2 is the customer name and G2 is one of the customer's codes. (They can have several codes) In the Vlookup table I need the equation to match the customer name and the specific code in G2 to lookup the correct fee. Does this help. Seems to me an IF statement is needed to make sure M2 is blank. If it is then a match needs to be done for the customer name (B2) and the specifice code (G2) in order to retrieve the proper fee.

4. ## Re: If statement and Vlookup (Excel 2000)

This assumes that column Y has the name and customer number concatenated together.

5. ## Re: If statement and Vlookup (Excel 2000)

Your equation did not work because the customer name and code are not concatenated. Since many customers can have the same code I need to have the customer name match and the customer code match. If both customer name and code match, then look up the right fee. I can send a sample if you need.

6. ## Re: If statement and Vlookup (Excel 2000)

How is your lookup table setup?
If you have names in 1 column and the codes in a row, you could use MATCH to lookup each and then combine them using an INDEX to get the intersection of the row and column.

If they are setup differently, you will have to be more specific about the setup. (though to simplify the formula the setup I describe above might be the way to go).

Steve

7. ## Re: If statement and Vlookup (Excel 2000)

Yes, I think this is the way to go. Thank you.

8. ## Re: If statement and Vlookup (Excel 2000)

For the moment, I will assume that your names are in column Y and the codes are in column Z. You could add a column on the left border of your table (i.e. column X)that concatenates the name and code and then use the formula I had above, but with the table range extended to include column X.

Steve's method would certainly work well, although with >500 rows in your current table, it will be a bit tedious to fill your new table manually. Creating the extra column I described would help you fill out your new table as well.

9. ## Re: If statement and Vlookup (Excel 2000)

You could create the table with formulas relatively easily, all you would need is the unique names in a column and the unique codes in the rows and the formula. You could then create the concatenated column as you suggest and vlookup the name and code from the left column and top row to get the values to fill the table: just 1 formula to fill the entire table.

Once completed the formulas can be "paste special valued" and the original table deleted.

Steve

10. ## Re: If statement and Vlookup (Excel 2000)

Once you add the concatenated column to the original table, I'm not sure you need Steve's suggested table since you can do the VLOOKUP using the formula I suggested a few posts ago. I would agree that Steve's table setup is much better for human eyes to read and review for possible errors since patterns would be much easier to see in the 2-dimensional table.

11. ## Re: If statement and Vlookup (Excel 2000)

I agree. Adding the combined columns eliminates the need to the 2 way lookup table.

I, personally, would prefer the 2 way table and you mention very good reasons why it is easier to update and use.

Steve

#### Posting Permissions

• You may not post new threads
• You may not post replies
• You may not post attachments
• You may not edit your posts
•