Results 1 to 11 of 11

20040428, 18:45 #1
 Join Date
 Aug 2002
 Location
 St. Charles, Illinois
 Posts
 222
 Thanks
 0
 Thanked 0 Times in 0 Posts
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?

20040428, 19:02 #2
 Join Date
 May 2002
 Location
 Mpls, Minnesota, USA
 Posts
 271
 Thanks
 0
 Thanked 0 Times in 0 Posts
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.
ChuckChuck Reimer
I'm from the Government and I'm here to help...

20040428, 19:09 #3
 Join Date
 Aug 2002
 Location
 St. Charles, Illinois
 Posts
 222
 Thanks
 0
 Thanked 0 Times in 0 Posts
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.

20040428, 20:12 #4
 Join Date
 Sep 2003
 Location
 Louisville, Kentucky, USA
 Posts
 134
 Thanks
 0
 Thanked 0 Times in 0 Posts
Re: If statement and Vlookup (Excel 2000)
=IF(M2="",VLOOKUP(B2&G2,$Y$2:$AD$532,6),"")
This assumes that column Y has the name and customer number concatenated together.

20040428, 21:19 #5
 Join Date
 Aug 2002
 Location
 St. Charles, Illinois
 Posts
 222
 Thanks
 0
 Thanked 0 Times in 0 Posts
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.

20040428, 23:57 #6
 Join Date
 Jul 2002
 Location
 Pittsburgh, Pennsylvania, USA
 Posts
 11,225
 Thanks
 14
 Thanked 342 Times in 335 Posts
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

20040429, 12:36 #7
 Join Date
 Aug 2002
 Location
 St. Charles, Illinois
 Posts
 222
 Thanks
 0
 Thanked 0 Times in 0 Posts
Re: If statement and Vlookup (Excel 2000)
Yes, I think this is the way to go. Thank you.

20040429, 12:40 #8
 Join Date
 Sep 2003
 Location
 Louisville, Kentucky, USA
 Posts
 134
 Thanks
 0
 Thanked 0 Times in 0 Posts
Re: If statement and Vlookup (Excel 2000)
A sample would be helpful.
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.

20040429, 12:55 #9
 Join Date
 Jul 2002
 Location
 Pittsburgh, Pennsylvania, USA
 Posts
 11,225
 Thanks
 14
 Thanked 342 Times in 335 Posts
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

20040430, 13:18 #10
 Join Date
 Sep 2003
 Location
 Louisville, Kentucky, USA
 Posts
 134
 Thanks
 0
 Thanked 0 Times in 0 Posts
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 2dimensional table.

20040430, 13:40 #11
 Join Date
 Jul 2002
 Location
 Pittsburgh, Pennsylvania, USA
 Posts
 11,225
 Thanks
 14
 Thanked 342 Times in 335 Posts
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