If this is your first visit, be sure to check out the FAQ by clicking the link above. You may have to register before you can post: click the register link above to proceed. To start viewing messages, select the forum that you want to visit from the selection below. |
|
|
Thread Tools | Display Modes |
#1
|
|||
|
|||
Lookup Table Dilemma
Using Excel 2003
I was trying to explain a dilemma I'm having in a previous posting and I have not been doing a good job - After reading other messages regarding Lookup Tables, I have found a better way to explain what I need. I'll give it another try. I have 4 tables - All 4 tables have 2 columns (column A & B) The 1st table has a range of values in column A from 2.5 to 2.9 The 2nd table has a range of values in column A from 3.0 to 4.7 The 3rd table has a range of values in column A from 4.8 to 9.4 The 4th table has a range of values in column A from 9.5 to 14.2 Column B has minimum values that correspond to the values in column A I need to do the following: As an example: Let's say the value in a cell in column C is 5.9, (5.9 is in the 3rd table and the corresponding minimum value for 5.9 is 319) - the value in column D is 400 - it meets the minimum value of 319 - therefore, a number 1 is placed a cell in column F. If the value in column D was 315, it wouldn't meet the minimum value criteria, therefore a number 1 wouldn't be placed in column F, it would be left blank. If the value in column C falls within the range of the values in table 1 (column A) and meets the criteria (the minimum values as well in column B) a number 1 is placed in column F. If it doesn't meet the criteria, column F is left blank. If the value in column C falls within the range of the values in table 2 and meets the criteria (the minimum values as well) a number 1 is NOT placed in ANY column. If the value in column C falls within the range of the values in table 3 (column A) and meets the criteria (the minimum values as well in column B) a number 1 is placed in column G. If it doesn't meet the criteria, column G is left blank. If the value in column C falls within the range of the values in table 4 (column A) and meets the criteria (the minimum values as well in column B) a number 1 is placed in column H. If it doesn't meet the criteria, column H is left blank. I hope I am explaining this properly - I really need to set this up - The only problem is that the lookup table has to be in a separate sheet tab. The other data will be in separate sheet tabs for each day - For the month of June there will be 30 sheets tabs. |
#2
|
|||
|
|||
Hi!
Why have 4 tables? If you had 1 table things would be a lot easier on you! I think you need something like this: =IF(cell in column D that holds 400 =VLOOKUP(cell in column C is 5.9,table_range,2,0),1,"") Biff "Karen" wrote in message ... Using Excel 2003 I was trying to explain a dilemma I'm having in a previous posting and I have not been doing a good job - After reading other messages regarding Lookup Tables, I have found a better way to explain what I need. I'll give it another try. I have 4 tables - All 4 tables have 2 columns (column A & B) The 1st table has a range of values in column A from 2.5 to 2.9 The 2nd table has a range of values in column A from 3.0 to 4.7 The 3rd table has a range of values in column A from 4.8 to 9.4 The 4th table has a range of values in column A from 9.5 to 14.2 Column B has minimum values that correspond to the values in column A I need to do the following: As an example: Let's say the value in a cell in column C is 5.9, (5.9 is in the 3rd table and the corresponding minimum value for 5.9 is 319) - the value in column D is 400 - it meets the minimum value of 319 - therefore, a number 1 is placed a cell in column F. If the value in column D was 315, it wouldn't meet the minimum value criteria, therefore a number 1 wouldn't be placed in column F, it would be left blank. If the value in column C falls within the range of the values in table 1 (column A) and meets the criteria (the minimum values as well in column B) a number 1 is placed in column F. If it doesn't meet the criteria, column F is left blank. If the value in column C falls within the range of the values in table 2 and meets the criteria (the minimum values as well) a number 1 is NOT placed in ANY column. If the value in column C falls within the range of the values in table 3 (column A) and meets the criteria (the minimum values as well in column B) a number 1 is placed in column G. If it doesn't meet the criteria, column G is left blank. If the value in column C falls within the range of the values in table 4 (column A) and meets the criteria (the minimum values as well in column B) a number 1 is placed in column H. If it doesn't meet the criteria, column H is left blank. I hope I am explaining this properly - I really need to set this up - The only problem is that the lookup table has to be in a separate sheet tab. The other data will be in separate sheet tabs for each day - For the month of June there will be 30 sheets tabs. |
#3
|
|||
|
|||
Well, after re-reading your post I think you need a little more than I
suggested. I remember reading your original post and, yes, it was confusing! Can you send me the file? If so, I'm at: xl can help at comcast period net Remove "can" and change the obvious. Biff "Biff" wrote in message ... Hi! Why have 4 tables? If you had 1 table things would be a lot easier on you! I think you need something like this: =IF(cell in column D that holds 400 =VLOOKUP(cell in column C is 5.9,table_range,2,0),1,"") Biff "Karen" wrote in message ... Using Excel 2003 I was trying to explain a dilemma I'm having in a previous posting and I have not been doing a good job - After reading other messages regarding Lookup Tables, I have found a better way to explain what I need. I'll give it another try. I have 4 tables - All 4 tables have 2 columns (column A & B) The 1st table has a range of values in column A from 2.5 to 2.9 The 2nd table has a range of values in column A from 3.0 to 4.7 The 3rd table has a range of values in column A from 4.8 to 9.4 The 4th table has a range of values in column A from 9.5 to 14.2 Column B has minimum values that correspond to the values in column A I need to do the following: As an example: Let's say the value in a cell in column C is 5.9, (5.9 is in the 3rd table and the corresponding minimum value for 5.9 is 319) - the value in column D is 400 - it meets the minimum value of 319 - therefore, a number 1 is placed a cell in column F. If the value in column D was 315, it wouldn't meet the minimum value criteria, therefore a number 1 wouldn't be placed in column F, it would be left blank. If the value in column C falls within the range of the values in table 1 (column A) and meets the criteria (the minimum values as well in column B) a number 1 is placed in column F. If it doesn't meet the criteria, column F is left blank. If the value in column C falls within the range of the values in table 2 and meets the criteria (the minimum values as well) a number 1 is NOT placed in ANY column. If the value in column C falls within the range of the values in table 3 (column A) and meets the criteria (the minimum values as well in column B) a number 1 is placed in column G. If it doesn't meet the criteria, column G is left blank. If the value in column C falls within the range of the values in table 4 (column A) and meets the criteria (the minimum values as well in column B) a number 1 is placed in column H. If it doesn't meet the criteria, column H is left blank. I hope I am explaining this properly - I really need to set this up - The only problem is that the lookup table has to be in a separate sheet tab. The other data will be in separate sheet tabs for each day - For the month of June there will be 30 sheets tabs. |
Thread Tools | |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Thread Starter | Forum | Replies | Last Post |
lookup vs combo box | Scubaman | General Discussion | 4 | June 7th, 2005 02:02 AM |
Unable to edit records in a form or query | Merlin | Using Forms | 7 | May 10th, 2005 02:00 PM |
Table Wizard Does Not Set Relationship if Foreign Key and Primary Key Name Do Not Match Exactly in Case. | HDW | Database Design | 3 | October 16th, 2004 03:42 AM |
Complicated Databse w/many relationships | Søren | Database Design | 7 | July 13th, 2004 05:41 AM |
Make a field lookup dependent on the value in another field of a record? | Susan A | Database Design | 8 | May 22nd, 2004 09:10 PM |