Reply
 
LinkBack Thread Tools Search this Thread Display Modes
  #1   Report Post  
Karen
 
Posts: n/a
Default 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   Report Post  
Biff
 
Posts: n/a
Default

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   Report Post  
Biff
 
Posts: n/a
Default

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.








Reply
Thread Tools Search this Thread
Search this Thread:

Advanced Search
Display Modes

Posting Rules

Smilies are On
[IMG] code is On
HTML code is Off
Trackbacks are On
Pingbacks are On
Refbacks are On


Similar Threads
Thread Thread Starter Forum Replies Last Post
How to create a lookup table with an added varable? GIZZMO Excel Worksheet Functions 3 April 6th 09 10:32 PM
Possible Lookup Table Karen Excel Worksheet Functions 5 June 8th 05 09:43 PM
Multiple table lookup KG Excel Discussion (Misc queries) 1 June 3rd 05 05:39 AM
How do I lookup a table from right to left ? Daz9 Excel Worksheet Functions 1 April 11th 05 11:21 AM
Index table lookup anomaly Carole O Excel Worksheet Functions 9 December 9th 04 05:33 PM


All times are GMT +1. The time now is 09:18 PM.

Powered by vBulletin® Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
Copyright ©2004-2024 ExcelBanter.
The comments are property of their posters.
 

About Us

"It's about Microsoft Excel"