software

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

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,quot;Invalid Entryquot;,LOOKUP(A1,H1:I5))HTH,
Paul
quot;lmattquot; gt; wrote in message
...
gt;I am trying to write a function such that the data returned to a given cell
gt; will vary depending upon the data entered into another cell. For example:
gt; If A1= 105, then G1=20; if A1=106, then G1=15
gt;
gt; I have more variables for A1 than 7, so I don't think that nested
gt; if...then
gt; statements would work. Can I do this using VLOOKUP?
gt;
gt; Thanks in advance.
gt;
gt; L
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)quot;lmattquot; wrote:

gt; I am trying to write a function such that the data returned to a given cell
gt; will vary depending upon the data entered into another cell. For example:
gt; If A1= 105, then G1=20; if A1=106, then G1=15
gt;
gt; I have more variables for A1 than 7, so I don't think that nested if...then
gt; statements would work. Can I do this using VLOOKUP?
gt;
gt; Thanks in advance.
gt;
gt; L

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

quot;Slothquot; wrote:

gt; you have a couple options. One is to use the VLOOKUP function as PCLIVE
gt; suggested. If you don't want to insert a table you can insert the table as
gt; part of the function like this
gt; =VLOOKUP(A1,{105,20;106,15;107,10},2)
gt; also if the options are linear (105, 106, 107, 108, 109, etc.) you can use
gt; the CHOOSE function like this
gt; =CHOOSE(A1-104,20,15,10)
gt;
gt;
gt; quot;lmattquot; wrote:
gt;
gt; gt; I am trying to write a function such that the data returned to a given cell
gt; gt; will vary depending upon the data entered into another cell. For example:
gt; gt; If A1= 105, then G1=20; if A1=106, then G1=15
gt; gt;
gt; gt; I have more variables for A1 than 7, so I don't think that nested if...then
gt; gt; statements would work. Can I do this using VLOOKUP?
gt; gt;
gt; gt; Thanks in advance.
gt; gt;
gt; gt; L

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.

Petegive me all the values you need and I will show you how to adapt our
functions to work for you.

quot;lmattquot; wrote:

gt; Thanks, all. My fault for not asking the question properly: what I meant to
gt; say was: if I have a value in cell A1 that can change, how can I write a
gt; function that will return a given value to the specified cell, e.g., if cell
gt; A1=105, then cell G1=10; if cell A1=106, then cell G1=20, etc. Further,
gt; values along column A can repeat, and the result must always be the same in
gt; column G for that row. Maybe if...then isn't far off after all?
gt;
gt; Again, thanks for any clarification you can give...
gt;
gt; L
gt;
gt; quot;Slothquot; wrote:
gt;
gt; gt; you have a couple options. One is to use the VLOOKUP function as PCLIVE
gt; gt; suggested. If you don't want to insert a table you can insert the table as
gt; gt; part of the function like this
gt; gt; =VLOOKUP(A1,{105,20;106,15;107,10},2)
gt; gt; also if the options are linear (105, 106, 107, 108, 109, etc.) you can use
gt; gt; the CHOOSE function like this
gt; gt; =CHOOSE(A1-104,20,15,10)
gt; gt;
gt; gt;
gt; gt; quot;lmattquot; wrote:
gt; gt;
gt; gt; gt; I am trying to write a function such that the data returned to a given cell
gt; gt; gt; will vary depending upon the data entered into another cell. For example:
gt; gt; gt; If A1= 105, then G1=20; if A1=106, then G1=15
gt; gt; gt;
gt; gt; gt; I have more variables for A1 than 7, so I don't think that nested if...then
gt; gt; gt; statements would work. Can I do this using VLOOKUP?
gt; gt; gt;
gt; gt; gt; Thanks in advance.
gt; gt; gt;
gt; gt; gt; L

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

quot;Slothquot; wrote:

gt; give me all the values you need and I will show you how to adapt our
gt; functions to work for you.
gt;
gt; quot;lmattquot; wrote:
gt;
gt; gt; Thanks, all. My fault for not asking the question properly: what I meant to
gt; gt; say was: if I have a value in cell A1 that can change, how can I write a
gt; gt; function that will return a given value to the specified cell, e.g., if cell
gt; gt; A1=105, then cell G1=10; if cell A1=106, then cell G1=20, etc. Further,
gt; gt; values along column A can repeat, and the result must always be the same in
gt; gt; column G for that row. Maybe if...then isn't far off after all?
gt; gt;
gt; gt; Again, thanks for any clarification you can give...
gt; gt;
gt; gt; L
gt; gt;
gt; gt; quot;Slothquot; wrote:
gt; gt;
gt; gt; gt; you have a couple options. One is to use the VLOOKUP function as PCLIVE
gt; gt; gt; suggested. If you don't want to insert a table you can insert the table as
gt; gt; gt; part of the function like this
gt; gt; gt; =VLOOKUP(A1,{105,20;106,15;107,10},2)
gt; gt; gt; also if the options are linear (105, 106, 107, 108, 109, etc.) you can use
gt; gt; gt; the CHOOSE function like this
gt; gt; gt; =CHOOSE(A1-104,20,15,10)
gt; gt; gt;
gt; gt; gt;
gt; gt; gt; quot;lmattquot; wrote:
gt; gt; gt;
gt; gt; gt; gt; I am trying to write a function such that the data returned to a given cell
gt; gt; gt; gt; will vary depending upon the data entered into another cell. For example:
gt; gt; gt; gt; If A1= 105, then G1=20; if A1=106, then G1=15
gt; gt; gt; gt;
gt; gt; gt; gt; I have more variables for A1 than 7, so I don't think that nested if...then
gt; gt; gt; gt; statements would work. Can I do this using VLOOKUP?
gt; gt; gt; gt;
gt; gt; gt; gt; Thanks in advance.
gt; gt; gt; gt;
gt; gt; gt; gt; L

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,quot;quot;)))))))

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.
Paulquot;lmattquot; gt; wrote in message
...
gt; This is for matching job code numbers to labor units. Here are the job
gt; code
gt; numbers/associated labor units:
gt; 102/20, 103/12, 105/20, 106/15, 109/60, 113/25, 11435, 115/20, 116/20,
gt; 120/15, 130/20, 131/15, 140/15, 150/60, 160/30, 170/15, 180/20
gt;
gt; Thnks.
gt;
gt; L
gt;
gt; quot;Slothquot; wrote:
gt;
gt;gt; give me all the values you need and I will show you how to adapt our
gt;gt; functions to work for you.
gt;gt;
gt;gt; quot;lmattquot; wrote:
gt;gt;
gt;gt; gt; Thanks, all. My fault for not asking the question properly: what I
gt;gt; gt; meant to
gt;gt; gt; say was: if I have a value in cell A1 that can change, how can I write
gt;gt; gt; a
gt;gt; gt; function that will return a given value to the specified cell, e.g., if
gt;gt; gt; cell
gt;gt; gt; A1=105, then cell G1=10; if cell A1=106, then cell G1=20, etc. Further,
gt;gt; gt; values along column A can repeat, and the result must always be the
gt;gt; gt; same in
gt;gt; gt; column G for that row. Maybe if...then isn't far off after all?
gt;gt; gt;
gt;gt; gt; Again, thanks for any clarification you can give...
gt;gt; gt;
gt;gt; gt; L
gt;gt; gt;
gt;gt; gt; quot;Slothquot; wrote:
gt;gt; gt;
gt;gt; gt; gt; you have a couple options. One is to use the VLOOKUP function as
gt;gt; gt; gt; PCLIVE
gt;gt; gt; gt; suggested. If you don't want to insert a table you can insert the
gt;gt; gt; gt; table as
gt;gt; gt; gt; part of the function like this
gt;gt; gt; gt; =VLOOKUP(A1,{105,20;106,15;107,10},2)
gt;gt; gt; gt; also if the options are linear (105, 106, 107, 108, 109, etc.) you
gt;gt; gt; gt; can use
gt;gt; gt; gt; the CHOOSE function like this
gt;gt; gt; gt; =CHOOSE(A1-104,20,15,10)
gt;gt; gt; gt;
gt;gt; gt; gt;
gt;gt; gt; gt; quot;lmattquot; wrote:
gt;gt; gt; gt;
gt;gt; gt; gt; gt; I am trying to write a function such that the data returned to a
gt;gt; gt; gt; gt; given cell
gt;gt; gt; gt; gt; will vary depending upon the data entered into another cell. For
gt;gt; gt; gt; gt; example:
gt;gt; gt; gt; gt; If A1= 105, then G1=20; if A1=106, then G1=15
gt;gt; gt; gt; gt;
gt;gt; gt; gt; gt; I have more variables for A1 than 7, so I don't think that nested
gt;gt; gt; gt; gt; if...then
gt;gt; gt; gt; gt; statements would work. Can I do this using VLOOKUP?
gt;gt; gt; gt; gt;
gt;gt; gt; gt; gt; Thanks in advance.
gt;gt; gt; gt; gt;
gt;gt; gt; gt; gt; L
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(D116,A1)=0,quot;Not Foundquot;,VLOOKUP(A1,D1:E16,2))

HTH,
Paulquot;Slothquot; gt; wrote in message
...
gt; give me all the values you need and I will show you how to adapt our
gt; functions to work for you.
gt;
gt; quot;lmattquot; wrote:
gt;
gt;gt; Thanks, all. My fault for not asking the question properly: what I meant
gt;gt; to
gt;gt; say was: if I have a value in cell A1 that can change, how can I write a
gt;gt; function that will return a given value to the specified cell, e.g., if
gt;gt; cell
gt;gt; A1=105, then cell G1=10; if cell A1=106, then cell G1=20, etc. Further,
gt;gt; values along column A can repeat, and the result must always be the same
gt;gt; in
gt;gt; column G for that row. Maybe if...then isn't far off after all?
gt;gt;
gt;gt; Again, thanks for any clarification you can give...
gt;gt;
gt;gt; L
gt;gt;
gt;gt; quot;Slothquot; wrote:
gt;gt;
gt;gt; gt; you have a couple options. One is to use the VLOOKUP function as
gt;gt; gt; PCLIVE
gt;gt; gt; suggested. If you don't want to insert a table you can insert the
gt;gt; gt; table as
gt;gt; gt; part of the function like this
gt;gt; gt; =VLOOKUP(A1,{105,20;106,15;107,10},2)
gt;gt; gt; also if the options are linear (105, 106, 107, 108, 109, etc.) you can
gt;gt; gt; use
gt;gt; gt; the CHOOSE function like this
gt;gt; gt; =CHOOSE(A1-104,20,15,10)
gt;gt; gt;
gt;gt; gt;
gt;gt; gt; quot;lmattquot; wrote:
gt;gt; gt;
gt;gt; gt; gt; I am trying to write a function such that the data returned to a
gt;gt; gt; gt; given cell
gt;gt; gt; gt; will vary depending upon the data entered into another cell. For
gt;gt; gt; gt; example:
gt;gt; gt; gt; If A1= 105, then G1=20; if A1=106, then G1=15
gt;gt; gt; gt;
gt;gt; gt; gt; I have more variables for A1 than 7, so I don't think that nested
gt;gt; gt; gt; if...then
gt;gt; gt; gt; statements would work. Can I do this using VLOOKUP?
gt;gt; gt; gt;
gt;gt; gt; gt; Thanks in advance.
gt;gt; gt; gt;
gt;gt; gt; gt; L
Thanks, all. You guys are great.

L

quot;PCLIVEquot; wrote:

gt; With the exact data you provided, you could use the following nested if
gt; statement.
gt;
gt; =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,quot;quot;)))))))
gt;
gt; Since the multiple labor units are the same, you are able to use the OR
gt; function and not exceed the limit of 7.
gt; Of course you could still use vlookup as mentioned before. You would still
gt; have to create the table somewhere in your workbook.
gt;
gt; Hope this helps.
gt; Paul
gt;
gt;
gt; quot;lmattquot; gt; wrote in message
gt; ...
gt; gt; This is for matching job code numbers to labor units. Here are the job
gt; gt; code
gt; gt; numbers/associated labor units:
gt; gt; 102/20, 103/12, 105/20, 106/15, 109/60, 113/25, 11435, 115/20, 116/20,
gt; gt; 120/15, 130/20, 131/15, 140/15, 150/60, 160/30, 170/15, 180/20
gt; gt;
gt; gt; Thnks.
gt; gt;
gt; gt; L
gt; gt;
gt; gt; quot;Slothquot; wrote:
gt; gt;
gt; gt;gt; give me all the values you need and I will show you how to adapt our
gt; gt;gt; functions to work for you.
gt; gt;gt;
gt; gt;gt; quot;lmattquot; wrote:
gt; gt;gt;
gt; gt;gt; gt; Thanks, all. My fault for not asking the question properly: what I
gt; gt;gt; gt; meant to
gt; gt;gt; gt; say was: if I have a value in cell A1 that can change, how can I write
gt; gt;gt; gt; a
gt; gt;gt; gt; function that will return a given value to the specified cell, e.g., if
gt; gt;gt; gt; cell
gt; gt;gt; gt; A1=105, then cell G1=10; if cell A1=106, then cell G1=20, etc. Further,
gt; gt;gt; gt; values along column A can repeat, and the result must always be the
gt; gt;gt; gt; same in
gt; gt;gt; gt; column G for that row. Maybe if...then isn't far off after all?
gt; gt;gt; gt;
gt; gt;gt; gt; Again, thanks for any clarification you can give...
gt; gt;gt; gt;
gt; gt;gt; gt; L
gt; gt;gt; gt;
gt; gt;gt; gt; quot;Slothquot; wrote:
gt; gt;gt; gt;
gt; gt;gt; gt; gt; you have a couple options. One is to use the VLOOKUP function as
gt; gt;gt; gt; gt; PCLIVE
gt; gt;gt; gt; gt; suggested. If you don't want to insert a table you can insert the
gt; gt;gt; gt; gt; table as
gt; gt;gt; gt; gt; part of the function like this
gt; gt;gt; gt; gt; =VLOOKUP(A1,{105,20;106,15;107,10},2)
gt; gt;gt; gt; gt; also if the options are linear (105, 106, 107, 108, 109, etc.) you
gt; gt;gt; gt; gt; can use
gt; gt;gt; gt; gt; the CHOOSE function like this
gt; gt;gt; gt; gt; =CHOOSE(A1-104,20,15,10)
gt; gt;gt; gt; gt;
gt; gt;gt; gt; gt;
gt; gt;gt; gt; gt; quot;lmattquot; wrote:
gt; gt;gt; gt; gt;
gt; gt;gt; gt; gt; gt; I am trying to write a function such that the data returned to a
gt; gt;gt; gt; gt; gt; given cell
gt; gt;gt; gt; gt; gt; will vary depending upon the data entered into another cell. For
gt; gt;gt; gt; gt; gt; example:
gt; gt;gt; gt; gt; gt; If A1= 105, then G1=20; if A1=106, then G1=15
gt; gt;gt; gt; gt; gt;
gt; gt;gt; gt; gt; gt; I have more variables for A1 than 7, so I don't think that nested
gt; gt;gt; gt; gt; gt; if...then
gt; gt;gt; gt; gt; gt; statements would work. Can I do this using VLOOKUP?
gt; gt;gt; gt; gt; gt;
gt; gt;gt; gt; gt; gt; Thanks in advance.
gt; gt;gt; gt; gt; gt;
gt; gt;gt; gt; gt; gt; L
gt;
gt;
gt;