Reply
 
LinkBack Thread Tools Search this Thread Display Modes
  #1   Report Post  
Karen
 
Posts: n/a
Default Possible Lookup Table

How do I accomplish this?
I have a table - I'll give you an example of the 2 columns
cell B6 = 2.5 cell D6 = 143
cell B7 = 2.6 cell D7 = 147
cell B8 = 2.7 cell D8 = 151
cell B9 = 2.8 cell D9 = 154
cell B10 = 2.9 cell D10 = 158
(Above is the complete first table)
If the value in cell J5 is 2.5 and a minimum of 143, place a number 1 in
cell N5
If the value in cell J5 is 2.6 and a minimum of 147, place a number 1 in
cell N5
If the value in cell J5 is 2.7 and a minimum of 151, place a number 1 in
cell N5
and so on...
Three other tables continue and are similar to the above table.
Another table looks up values and a number 1 is not placed in a column based
on the values in the table.
Another table looks up values and a number 1 is placed in cell L5 based on
the values in the table.
Another table looks up values and a number 1 is placed in cell M5 based on
the values in the table.
This may not be enough info or I'm not explaining this properly - If anyone
knows what I need, can someone at least guide me in the right direction to
accomplish this task?
Thank you
  #2   Report Post  
Barb R.
 
Posts: n/a
Default

I'm not quite following what you want. If cell B6 = 2.5 and what is a
minimum of 143. I'm guessing what you have in column D identifies the
minimum value for something.

"Karen" wrote:

How do I accomplish this?
I have a table - I'll give you an example of the 2 columns
cell B6 = 2.5 cell D6 = 143
cell B7 = 2.6 cell D7 = 147
cell B8 = 2.7 cell D8 = 151
cell B9 = 2.8 cell D9 = 154
cell B10 = 2.9 cell D10 = 158
(Above is the complete first table)
If the value in cell J5 is 2.5 and a minimum of 143, place a number 1 in
cell N5
If the value in cell J5 is 2.6 and a minimum of 147, place a number 1 in
cell N5
If the value in cell J5 is 2.7 and a minimum of 151, place a number 1 in
cell N5
and so on...
Three other tables continue and are similar to the above table.
Another table looks up values and a number 1 is not placed in a column based
on the values in the table.
Another table looks up values and a number 1 is placed in cell L5 based on
the values in the table.
Another table looks up values and a number 1 is placed in cell M5 based on
the values in the table.
This may not be enough info or I'm not explaining this properly - If anyone
knows what I need, can someone at least guide me in the right direction to
accomplish this task?
Thank you

  #3   Report Post  
Karen
 
Posts: n/a
Default

Thank you for offering your help - I'm sorry, I totally screwed up the
explanation!

I wish I could show you all 4 tables, but there are over 100 rows and 2
columns of data in 4 tables. So, I'll show you the complete table one and a
portion of table three to reference. All 4 lookup tables will be on a
separate sheet tab (Lookup)

Note: Spanning 4 tables - The values in the first column follow in sequence
from 2.5 to 14.2 -- The values in the 2nd column follow in ascending order,
but not in consecutive order (They may stay the same or skip an integer or
more)

Table 1
1st Column 2nd Column
cell B6 = 2.5 cell D6 = 143
cell B7 = 2.6 cell D7 = 147
cell B8 = 2.7 cell D8 = 151
cell B9 = 2.8 cell D9 = 154
cell B10 = 2.9 cell D10 = 158

Excerpt of Table 3
cell B29 = 4.8 cell D29 = 278
cell B30 = 4.9 cell D30 = 282
cell B31 = 5.0 cell D31 = 286
cell B32 = 5.1 cell D32 = 290
cell B33 = 5.2 cell D33 = 294
cell B34 = 5.3 cell D34 = 298

Sheet Tab (Data)
Let's say we are working with the data in row 5 first.
For instance: Let's say the first formula will be placed in cell K5.

We start by referencing the 1st column in all 4 tables. We look up the value
in the 1st column and if the value in cell J5 is 2.8 and the value in cell I5
is a minimum of 154, then return a number 1 in cell N5

Another example: We look up the value in the 1st column and if the value in
cell J5 is 5.2 and the value in cell I5 is a minimum of 294, then return a
number 1 in cell L5

The value has to fall within table one to return a number 1 in N5
The value has to fall within table two to return nothing (So I guess we
don't have to mention that one)
The value has to fall within table three to return a number 1 in L5
The value has to fall within table four to return a number 1 in M5

Am I explaining this properly now? I hope so.
Thank you again
ANY help would be great!



"Barb R." wrote:

I'm not quite following what you want. If cell B6 = 2.5 and what is a
minimum of 143. I'm guessing what you have in column D identifies the
minimum value for something.

"Karen" wrote:

How do I accomplish this?
I have a table - I'll give you an example of the 2 columns
cell B6 = 2.5 cell D6 = 143
cell B7 = 2.6 cell D7 = 147
cell B8 = 2.7 cell D8 = 151
cell B9 = 2.8 cell D9 = 154
cell B10 = 2.9 cell D10 = 158
(Above is the complete first table)
If the value in cell J5 is 2.5 and a minimum of 143, place a number 1 in
cell N5
If the value in cell J5 is 2.6 and a minimum of 147, place a number 1 in
cell N5
If the value in cell J5 is 2.7 and a minimum of 151, place a number 1 in
cell N5
and so on...
Three other tables continue and are similar to the above table.
Another table looks up values and a number 1 is not placed in a column based
on the values in the table.
Another table looks up values and a number 1 is placed in cell L5 based on
the values in the table.
Another table looks up values and a number 1 is placed in cell M5 based on
the values in the table.
This may not be enough info or I'm not explaining this properly - If anyone
knows what I need, can someone at least guide me in the right direction to
accomplish this task?
Thank you

  #4   Report Post  
Barb R.
 
Posts: n/a
Default

I'll do this stepwise. First, are all of the tables on the same spreadsheet
or different sheets. It will change the equations.

Base on what you say he
"We start by referencing the 1st column in all 4 tables. We look up the value
in the 1st column and if the value in cell J5 is 2.8 and the value in cell I5
is a minimum of 154, then return a number 1 in cell N5"

I'm not sure if you want the "1" in N5 or in K5. In any case, I think this
is the equation you want:

=IF(I5<=VLOOKUP(J5,B6:D10,3,FALSE),1,"")

Let me know and we can go from there.

"Karen" wrote:

Thank you for offering your help - I'm sorry, I totally screwed up the
explanation!

I wish I could show you all 4 tables, but there are over 100 rows and 2
columns of data in 4 tables. So, I'll show you the complete table one and a
portion of table three to reference. All 4 lookup tables will be on a
separate sheet tab (Lookup)

Note: Spanning 4 tables - The values in the first column follow in sequence
from 2.5 to 14.2 -- The values in the 2nd column follow in ascending order,
but not in consecutive order (They may stay the same or skip an integer or
more)

Table 1
1st Column 2nd Column
cell B6 = 2.5 cell D6 = 143
cell B7 = 2.6 cell D7 = 147
cell B8 = 2.7 cell D8 = 151
cell B9 = 2.8 cell D9 = 154
cell B10 = 2.9 cell D10 = 158

Excerpt of Table 3
cell B29 = 4.8 cell D29 = 278
cell B30 = 4.9 cell D30 = 282
cell B31 = 5.0 cell D31 = 286
cell B32 = 5.1 cell D32 = 290
cell B33 = 5.2 cell D33 = 294
cell B34 = 5.3 cell D34 = 298

Sheet Tab (Data)
Let's say we are working with the data in row 5 first.
For instance: Let's say the first formula will be placed in cell K5.

We start by referencing the 1st column in all 4 tables. We look up the value
in the 1st column and if the value in cell J5 is 2.8 and the value in cell I5
is a minimum of 154, then return a number 1 in cell N5

Another example: We look up the value in the 1st column and if the value in
cell J5 is 5.2 and the value in cell I5 is a minimum of 294, then return a
number 1 in cell L5

The value has to fall within table one to return a number 1 in N5
The value has to fall within table two to return nothing (So I guess we
don't have to mention that one)
The value has to fall within table three to return a number 1 in L5
The value has to fall within table four to return a number 1 in M5

Am I explaining this properly now? I hope so.
Thank you again
ANY help would be great!



"Barb R." wrote:

I'm not quite following what you want. If cell B6 = 2.5 and what is a
minimum of 143. I'm guessing what you have in column D identifies the
minimum value for something.

"Karen" wrote:

How do I accomplish this?
I have a table - I'll give you an example of the 2 columns
cell B6 = 2.5 cell D6 = 143
cell B7 = 2.6 cell D7 = 147
cell B8 = 2.7 cell D8 = 151
cell B9 = 2.8 cell D9 = 154
cell B10 = 2.9 cell D10 = 158
(Above is the complete first table)
If the value in cell J5 is 2.5 and a minimum of 143, place a number 1 in
cell N5
If the value in cell J5 is 2.6 and a minimum of 147, place a number 1 in
cell N5
If the value in cell J5 is 2.7 and a minimum of 151, place a number 1 in
cell N5
and so on...
Three other tables continue and are similar to the above table.
Another table looks up values and a number 1 is not placed in a column based
on the values in the table.
Another table looks up values and a number 1 is placed in cell L5 based on
the values in the table.
Another table looks up values and a number 1 is placed in cell M5 based on
the values in the table.
This may not be enough info or I'm not explaining this properly - If anyone
knows what I need, can someone at least guide me in the right direction to
accomplish this task?
Thank you

  #5   Report Post  
Karen
 
Posts: n/a
Default

I was going to put the lookup table in the same file but a different sheet
tab - Sheet tab name: Lookup - Regarding the sheet tab that contains the
other data to calculate, there will be one for every day. Example: 1, 2, 3,
4, etc. But I can fix the first sheet tab (1) and copy the sheets to other
sheets and rename them.
I wish I could show you the table, it would be much easier.
I still think I am getting my point across.

"Barb R." wrote:

I'll do this stepwise. First, are all of the tables on the same spreadsheet
or different sheets. It will change the equations.

Base on what you say he
"We start by referencing the 1st column in all 4 tables. We look up the value
in the 1st column and if the value in cell J5 is 2.8 and the value in cell I5
is a minimum of 154, then return a number 1 in cell N5"

I'm not sure if you want the "1" in N5 or in K5. In any case, I think this
is the equation you want:

=IF(I5<=VLOOKUP(J5,B6:D10,3,FALSE),1,"")

Let me know and we can go from there.

"Karen" wrote:

Thank you for offering your help - I'm sorry, I totally screwed up the
explanation!

I wish I could show you all 4 tables, but there are over 100 rows and 2
columns of data in 4 tables. So, I'll show you the complete table one and a
portion of table three to reference. All 4 lookup tables will be on a
separate sheet tab (Lookup)

Note: Spanning 4 tables - The values in the first column follow in sequence
from 2.5 to 14.2 -- The values in the 2nd column follow in ascending order,
but not in consecutive order (They may stay the same or skip an integer or
more)

Table 1
1st Column 2nd Column
cell B6 = 2.5 cell D6 = 143
cell B7 = 2.6 cell D7 = 147
cell B8 = 2.7 cell D8 = 151
cell B9 = 2.8 cell D9 = 154
cell B10 = 2.9 cell D10 = 158

Excerpt of Table 3
cell B29 = 4.8 cell D29 = 278
cell B30 = 4.9 cell D30 = 282
cell B31 = 5.0 cell D31 = 286
cell B32 = 5.1 cell D32 = 290
cell B33 = 5.2 cell D33 = 294
cell B34 = 5.3 cell D34 = 298

Sheet Tab (Data)
Let's say we are working with the data in row 5 first.
For instance: Let's say the first formula will be placed in cell K5.

We start by referencing the 1st column in all 4 tables. We look up the value
in the 1st column and if the value in cell J5 is 2.8 and the value in cell I5
is a minimum of 154, then return a number 1 in cell N5

Another example: We look up the value in the 1st column and if the value in
cell J5 is 5.2 and the value in cell I5 is a minimum of 294, then return a
number 1 in cell L5

The value has to fall within table one to return a number 1 in N5
The value has to fall within table two to return nothing (So I guess we
don't have to mention that one)
The value has to fall within table three to return a number 1 in L5
The value has to fall within table four to return a number 1 in M5

Am I explaining this properly now? I hope so.
Thank you again
ANY help would be great!



"Barb R." wrote:

I'm not quite following what you want. If cell B6 = 2.5 and what is a
minimum of 143. I'm guessing what you have in column D identifies the
minimum value for something.

"Karen" wrote:

How do I accomplish this?
I have a table - I'll give you an example of the 2 columns
cell B6 = 2.5 cell D6 = 143
cell B7 = 2.6 cell D7 = 147
cell B8 = 2.7 cell D8 = 151
cell B9 = 2.8 cell D9 = 154
cell B10 = 2.9 cell D10 = 158
(Above is the complete first table)
If the value in cell J5 is 2.5 and a minimum of 143, place a number 1 in
cell N5
If the value in cell J5 is 2.6 and a minimum of 147, place a number 1 in
cell N5
If the value in cell J5 is 2.7 and a minimum of 151, place a number 1 in
cell N5
and so on...
Three other tables continue and are similar to the above table.
Another table looks up values and a number 1 is not placed in a column based
on the values in the table.
Another table looks up values and a number 1 is placed in cell L5 based on
the values in the table.
Another table looks up values and a number 1 is placed in cell M5 based on
the values in the table.
This may not be enough info or I'm not explaining this properly - If anyone
knows what I need, can someone at least guide me in the right direction to
accomplish this task?
Thank you



  #6   Report Post  
Karen
 
Posts: n/a
Default

I'm sorry, I just noticed a typo of mine - Imeant to say in the previous
reply that, I still think I am NOT getting my point across.
Sorry

"Karen" wrote:

I was going to put the lookup table in the same file but a different sheet
tab - Sheet tab name: Lookup - Regarding the sheet tab that contains the
other data to calculate, there will be one for every day. Example: 1, 2, 3,
4, etc. But I can fix the first sheet tab (1) and copy the sheets to other
sheets and rename them.
I wish I could show you the table, it would be much easier.
I still think I am getting my point across.

"Barb R." wrote:

I'll do this stepwise. First, are all of the tables on the same spreadsheet
or different sheets. It will change the equations.

Base on what you say he
"We start by referencing the 1st column in all 4 tables. We look up the value
in the 1st column and if the value in cell J5 is 2.8 and the value in cell I5
is a minimum of 154, then return a number 1 in cell N5"

I'm not sure if you want the "1" in N5 or in K5. In any case, I think this
is the equation you want:

=IF(I5<=VLOOKUP(J5,B6:D10,3,FALSE),1,"")

Let me know and we can go from there.

"Karen" wrote:

Thank you for offering your help - I'm sorry, I totally screwed up the
explanation!

I wish I could show you all 4 tables, but there are over 100 rows and 2
columns of data in 4 tables. So, I'll show you the complete table one and a
portion of table three to reference. All 4 lookup tables will be on a
separate sheet tab (Lookup)

Note: Spanning 4 tables - The values in the first column follow in sequence
from 2.5 to 14.2 -- The values in the 2nd column follow in ascending order,
but not in consecutive order (They may stay the same or skip an integer or
more)

Table 1
1st Column 2nd Column
cell B6 = 2.5 cell D6 = 143
cell B7 = 2.6 cell D7 = 147
cell B8 = 2.7 cell D8 = 151
cell B9 = 2.8 cell D9 = 154
cell B10 = 2.9 cell D10 = 158

Excerpt of Table 3
cell B29 = 4.8 cell D29 = 278
cell B30 = 4.9 cell D30 = 282
cell B31 = 5.0 cell D31 = 286
cell B32 = 5.1 cell D32 = 290
cell B33 = 5.2 cell D33 = 294
cell B34 = 5.3 cell D34 = 298

Sheet Tab (Data)
Let's say we are working with the data in row 5 first.
For instance: Let's say the first formula will be placed in cell K5.

We start by referencing the 1st column in all 4 tables. We look up the value
in the 1st column and if the value in cell J5 is 2.8 and the value in cell I5
is a minimum of 154, then return a number 1 in cell N5

Another example: We look up the value in the 1st column and if the value in
cell J5 is 5.2 and the value in cell I5 is a minimum of 294, then return a
number 1 in cell L5

The value has to fall within table one to return a number 1 in N5
The value has to fall within table two to return nothing (So I guess we
don't have to mention that one)
The value has to fall within table three to return a number 1 in L5
The value has to fall within table four to return a number 1 in M5

Am I explaining this properly now? I hope so.
Thank you again
ANY help would be great!



"Barb R." wrote:

I'm not quite following what you want. If cell B6 = 2.5 and what is a
minimum of 143. I'm guessing what you have in column D identifies the
minimum value for something.

"Karen" wrote:

How do I accomplish this?
I have a table - I'll give you an example of the 2 columns
cell B6 = 2.5 cell D6 = 143
cell B7 = 2.6 cell D7 = 147
cell B8 = 2.7 cell D8 = 151
cell B9 = 2.8 cell D9 = 154
cell B10 = 2.9 cell D10 = 158
(Above is the complete first table)
If the value in cell J5 is 2.5 and a minimum of 143, place a number 1 in
cell N5
If the value in cell J5 is 2.6 and a minimum of 147, place a number 1 in
cell N5
If the value in cell J5 is 2.7 and a minimum of 151, place a number 1 in
cell N5
and so on...
Three other tables continue and are similar to the above table.
Another table looks up values and a number 1 is not placed in a column based
on the values in the table.
Another table looks up values and a number 1 is placed in cell L5 based on
the values in the table.
Another table looks up values and a number 1 is placed in cell M5 based on
the values in the table.
This may not be enough info or I'm not explaining this properly - If anyone
knows what I need, can someone at least guide me in the right direction to
accomplish this task?
Thank you

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
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
Lookup Table Ben Excel Worksheet Functions 7 November 30th 04 08:05 PM


All times are GMT +1. The time now is 05:21 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"