Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Lookup help, please
I am trying to write a function such that the data returned to a given cell
will vary depending upon the data entered into another cell. For example: If A1= 105, then G1=20; if A1=106, then G1=15 I have more variables for A1 than 7, so I don't think that nested if...then statements would work. Can I do this using VLOOKUP? Thanks in advance. L |
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Lookup help, please
Using columns H and I for a lookup table:
Ex. H1 through H5 will contain the lookup values. 105 106 107 108 109 I1 through I5 will contain the results. 20 15 10 5 0 In G1, use the following formula: =LOOKUP(A1,H1:I5) If you need to prevent errors in case A1 is not found in the lookup table, you can use something like the following: =IF(COUNTIF(H1:H5,A1)=0,"Invalid Entry",LOOKUP(A1,H1:I5)) HTH, Paul "lmatt" wrote in message ... I am trying to write a function such that the data returned to a given cell will vary depending upon the data entered into another cell. For example: If A1= 105, then G1=20; if A1=106, then G1=15 I have more variables for A1 than 7, so I don't think that nested if...then statements would work. Can I do this using VLOOKUP? Thanks in advance. L |
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Lookup help, please
you have a couple options. One is to use the VLOOKUP function as PCLIVE
suggested. If you don't want to insert a table you can insert the table as part of the function like this =VLOOKUP(A1,{105,20;106,15;107,10},2) also if the options are linear (105, 106, 107, 108, 109, etc.) you can use the CHOOSE function like this =CHOOSE(A1-104,20,15,10) "lmatt" wrote: I am trying to write a function such that the data returned to a given cell will vary depending upon the data entered into another cell. For example: If A1= 105, then G1=20; if A1=106, then G1=15 I have more variables for A1 than 7, so I don't think that nested if...then statements would work. Can I do this using VLOOKUP? Thanks in advance. L |
#4
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Lookup help, please
Thanks, all. My fault for not asking the question properly: what I meant to
say was: if I have a value in cell A1 that can change, how can I write a function that will return a given value to the specified cell, e.g., if cell A1=105, then cell G1=10; if cell A1=106, then cell G1=20, etc. Further, values along column A can repeat, and the result must always be the same in column G for that row. Maybe if...then isn't far off after all? Again, thanks for any clarification you can give... L "Sloth" wrote: you have a couple options. One is to use the VLOOKUP function as PCLIVE suggested. If you don't want to insert a table you can insert the table as part of the function like this =VLOOKUP(A1,{105,20;106,15;107,10},2) also if the options are linear (105, 106, 107, 108, 109, etc.) you can use the CHOOSE function like this =CHOOSE(A1-104,20,15,10) "lmatt" wrote: I am trying to write a function such that the data returned to a given cell will vary depending upon the data entered into another cell. For example: If A1= 105, then G1=20; if A1=106, then G1=15 I have more variables for A1 than 7, so I don't think that nested if...then statements would work. Can I do this using VLOOKUP? Thanks in advance. L |
#5
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Lookup help, please
You said to begin with that you had more than 7 variables, so you
couldn't use IF .. THEN. The VLOOKUP formulae will give you what you want - you just need to define a table somewhere with the values you expect in A1 and the derived values you want in G1 and then enter the Vlookup formula in G1. If your table covers, say, Y1:Z15, then ensure the range in the lookup formula is Y$1:Z$15, then you can copy this down column G for as many values that you have in column A. Pete |
#6
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Lookup help, please
give me all the values you need and I will show you how to adapt our
functions to work for you. "lmatt" wrote: Thanks, all. My fault for not asking the question properly: what I meant to say was: if I have a value in cell A1 that can change, how can I write a function that will return a given value to the specified cell, e.g., if cell A1=105, then cell G1=10; if cell A1=106, then cell G1=20, etc. Further, values along column A can repeat, and the result must always be the same in column G for that row. Maybe if...then isn't far off after all? Again, thanks for any clarification you can give... L "Sloth" wrote: you have a couple options. One is to use the VLOOKUP function as PCLIVE suggested. If you don't want to insert a table you can insert the table as part of the function like this =VLOOKUP(A1,{105,20;106,15;107,10},2) also if the options are linear (105, 106, 107, 108, 109, etc.) you can use the CHOOSE function like this =CHOOSE(A1-104,20,15,10) "lmatt" wrote: I am trying to write a function such that the data returned to a given cell will vary depending upon the data entered into another cell. For example: If A1= 105, then G1=20; if A1=106, then G1=15 I have more variables for A1 than 7, so I don't think that nested if...then statements would work. Can I do this using VLOOKUP? Thanks in advance. L |
#7
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Lookup help, please
This is for matching job code numbers to labor units. Here are the job code
numbers/associated labor units: 102/20, 103/12, 105/20, 106/15, 109/60, 113/25, 11435, 115/20, 116/20, 120/15, 130/20, 131/15, 140/15, 150/60, 160/30, 170/15, 180/20 Thnks. L "Sloth" wrote: give me all the values you need and I will show you how to adapt our functions to work for you. "lmatt" wrote: Thanks, all. My fault for not asking the question properly: what I meant to say was: if I have a value in cell A1 that can change, how can I write a function that will return a given value to the specified cell, e.g., if cell A1=105, then cell G1=10; if cell A1=106, then cell G1=20, etc. Further, values along column A can repeat, and the result must always be the same in column G for that row. Maybe if...then isn't far off after all? Again, thanks for any clarification you can give... L "Sloth" wrote: you have a couple options. One is to use the VLOOKUP function as PCLIVE suggested. If you don't want to insert a table you can insert the table as part of the function like this =VLOOKUP(A1,{105,20;106,15;107,10},2) also if the options are linear (105, 106, 107, 108, 109, etc.) you can use the CHOOSE function like this =CHOOSE(A1-104,20,15,10) "lmatt" wrote: I am trying to write a function such that the data returned to a given cell will vary depending upon the data entered into another cell. For example: If A1= 105, then G1=20; if A1=106, then G1=15 I have more variables for A1 than 7, so I don't think that nested if...then statements would work. Can I do this using VLOOKUP? Thanks in advance. L |
#8
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Lookup help, please
With the exact data you provided, you could use the following nested if
statement. =IF(OR(A1=102,A1=105,A1=116,A1=120,A1=131,A1=180), 20,IF(OR(A1=106,A1=130,A1=140,A1=150),15,IF(OR(A1= 109,A1=160),60,IF(A1=103,12,IF(A1=113,25,IF(A1=170 ,30,IF(A1=115,35,""))))))) Since the multiple labor units are the same, you are able to use the OR function and not exceed the limit of 7. Of course you could still use vlookup as mentioned before. You would still have to create the table somewhere in your workbook. Hope this helps. Paul "lmatt" wrote in message ... This is for matching job code numbers to labor units. Here are the job code numbers/associated labor units: 102/20, 103/12, 105/20, 106/15, 109/60, 113/25, 11435, 115/20, 116/20, 120/15, 130/20, 131/15, 140/15, 150/60, 160/30, 170/15, 180/20 Thnks. L "Sloth" wrote: give me all the values you need and I will show you how to adapt our functions to work for you. "lmatt" wrote: Thanks, all. My fault for not asking the question properly: what I meant to say was: if I have a value in cell A1 that can change, how can I write a function that will return a given value to the specified cell, e.g., if cell A1=105, then cell G1=10; if cell A1=106, then cell G1=20, etc. Further, values along column A can repeat, and the result must always be the same in column G for that row. Maybe if...then isn't far off after all? Again, thanks for any clarification you can give... L "Sloth" wrote: you have a couple options. One is to use the VLOOKUP function as PCLIVE suggested. If you don't want to insert a table you can insert the table as part of the function like this =VLOOKUP(A1,{105,20;106,15;107,10},2) also if the options are linear (105, 106, 107, 108, 109, etc.) you can use the CHOOSE function like this =CHOOSE(A1-104,20,15,10) "lmatt" wrote: I am trying to write a function such that the data returned to a given cell will vary depending upon the data entered into another cell. For example: If A1= 105, then G1=20; if A1=106, then G1=15 I have more variables for A1 than 7, so I don't think that nested if...then statements would work. Can I do this using VLOOKUP? Thanks in advance. L |
#9
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Lookup help, please
Now if you wanted to go the route of VLOOKUP, you would need to arrange a
table in your worksheet in the following manner. Notice that it is in ascending order by the first column This is necessary for the lookup to work properly. Col. D Col. E 102 20 103 12 105 20 106 15 109 60 113 25 115 35 116 20 120 20 130 15 131 20 140 15 150 15 160 60 170 30 180 20 Now your VLOOKUP formula: =VLOOKUP(A1,D1:E16,2) Once again, to prevent and error when the value doesn't exist in the table you can use: =IF(COUNTIF(D1:D16,A1)=0,"Not Found",VLOOKUP(A1,D1:E16,2)) HTH, Paul "Sloth" wrote in message ... give me all the values you need and I will show you how to adapt our functions to work for you. "lmatt" wrote: Thanks, all. My fault for not asking the question properly: what I meant to say was: if I have a value in cell A1 that can change, how can I write a function that will return a given value to the specified cell, e.g., if cell A1=105, then cell G1=10; if cell A1=106, then cell G1=20, etc. Further, values along column A can repeat, and the result must always be the same in column G for that row. Maybe if...then isn't far off after all? Again, thanks for any clarification you can give... L "Sloth" wrote: you have a couple options. One is to use the VLOOKUP function as PCLIVE suggested. If you don't want to insert a table you can insert the table as part of the function like this =VLOOKUP(A1,{105,20;106,15;107,10},2) also if the options are linear (105, 106, 107, 108, 109, etc.) you can use the CHOOSE function like this =CHOOSE(A1-104,20,15,10) "lmatt" wrote: I am trying to write a function such that the data returned to a given cell will vary depending upon the data entered into another cell. For example: If A1= 105, then G1=20; if A1=106, then G1=15 I have more variables for A1 than 7, so I don't think that nested if...then statements would work. Can I do this using VLOOKUP? Thanks in advance. L |
#10
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Lookup help, please
Thanks, all. You guys are great.
L "PCLIVE" wrote: With the exact data you provided, you could use the following nested if statement. =IF(OR(A1=102,A1=105,A1=116,A1=120,A1=131,A1=180), 20,IF(OR(A1=106,A1=130,A1=140,A1=150),15,IF(OR(A1= 109,A1=160),60,IF(A1=103,12,IF(A1=113,25,IF(A1=170 ,30,IF(A1=115,35,""))))))) Since the multiple labor units are the same, you are able to use the OR function and not exceed the limit of 7. Of course you could still use vlookup as mentioned before. You would still have to create the table somewhere in your workbook. Hope this helps. Paul "lmatt" wrote in message ... This is for matching job code numbers to labor units. Here are the job code numbers/associated labor units: 102/20, 103/12, 105/20, 106/15, 109/60, 113/25, 11435, 115/20, 116/20, 120/15, 130/20, 131/15, 140/15, 150/60, 160/30, 170/15, 180/20 Thnks. L "Sloth" wrote: give me all the values you need and I will show you how to adapt our functions to work for you. "lmatt" wrote: Thanks, all. My fault for not asking the question properly: what I meant to say was: if I have a value in cell A1 that can change, how can I write a function that will return a given value to the specified cell, e.g., if cell A1=105, then cell G1=10; if cell A1=106, then cell G1=20, etc. Further, values along column A can repeat, and the result must always be the same in column G for that row. Maybe if...then isn't far off after all? Again, thanks for any clarification you can give... L "Sloth" wrote: you have a couple options. One is to use the VLOOKUP function as PCLIVE suggested. If you don't want to insert a table you can insert the table as part of the function like this =VLOOKUP(A1,{105,20;106,15;107,10},2) also if the options are linear (105, 106, 107, 108, 109, etc.) you can use the CHOOSE function like this =CHOOSE(A1-104,20,15,10) "lmatt" wrote: I am trying to write a function such that the data returned to a given cell will vary depending upon the data entered into another cell. For example: If A1= 105, then G1=20; if A1=106, then G1=15 I have more variables for A1 than 7, so I don't think that nested if...then statements would work. Can I do this using VLOOKUP? Thanks in advance. L |
#11
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Lookup help, please
It's a thing of beauty...
It all seems so obvious once it's pointed out. Thanks again, all. L "PCLIVE" wrote: Now if you wanted to go the route of VLOOKUP, you would need to arrange a table in your worksheet in the following manner. Notice that it is in ascending order by the first column This is necessary for the lookup to work properly. Col. D Col. E 102 20 103 12 105 20 106 15 109 60 113 25 115 35 116 20 120 20 130 15 131 20 140 15 150 15 160 60 170 30 180 20 Now your VLOOKUP formula: =VLOOKUP(A1,D1:E16,2) Once again, to prevent and error when the value doesn't exist in the table you can use: =IF(COUNTIF(D1:D16,A1)=0,"Not Found",VLOOKUP(A1,D1:E16,2)) HTH, Paul "Sloth" wrote in message ... give me all the values you need and I will show you how to adapt our functions to work for you. "lmatt" wrote: Thanks, all. My fault for not asking the question properly: what I meant to say was: if I have a value in cell A1 that can change, how can I write a function that will return a given value to the specified cell, e.g., if cell A1=105, then cell G1=10; if cell A1=106, then cell G1=20, etc. Further, values along column A can repeat, and the result must always be the same in column G for that row. Maybe if...then isn't far off after all? Again, thanks for any clarification you can give... L "Sloth" wrote: you have a couple options. One is to use the VLOOKUP function as PCLIVE suggested. If you don't want to insert a table you can insert the table as part of the function like this =VLOOKUP(A1,{105,20;106,15;107,10},2) also if the options are linear (105, 106, 107, 108, 109, etc.) you can use the CHOOSE function like this =CHOOSE(A1-104,20,15,10) "lmatt" wrote: I am trying to write a function such that the data returned to a given cell will vary depending upon the data entered into another cell. For example: If A1= 105, then G1=20; if A1=106, then G1=15 I have more variables for A1 than 7, so I don't think that nested if...then statements would work. Can I do this using VLOOKUP? Thanks in advance. L |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Another way to lookup data | Excel Worksheet Functions | |||
lookup more than one cell | Excel Worksheet Functions | |||
Lookup Vector > Lookup Value | Excel Worksheet Functions | |||
Lookup function w/Text and Year | Excel Worksheet Functions | |||
double lookup, nest, or macro? | Excel Worksheet Functions |