Home |
Search |
Today's Posts |
#1
![]() |
|||
|
|||
![]()
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
![]() |
|||
|
|||
![]()
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
![]() |
|||
|
|||
![]()
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
![]() |
|||
|
|||
![]()
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
![]() |
|||
|
|||
![]()
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
![]() |
|||
|
|||
![]()
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 |
Display Modes | |
|
|
![]() |
||||
Thread | Forum | |||
How to create a lookup table with an added varable? | Excel Worksheet Functions | |||
Multiple table lookup | Excel Discussion (Misc queries) | |||
How do I lookup a table from right to left ? | Excel Worksheet Functions | |||
Index table lookup anomaly | Excel Worksheet Functions | |||
Lookup Table | Excel Worksheet Functions |