1. ## Formula Required

In cell G32, this cell returns a number from 0 through to 59.
In cell G33, I would like cell G33 to return a result based on the following....
If G32 is either 0,1,2,3, then cell G33 is to return 0.
If G32 is either 4,5,6,7,8, then cell G33 is to return 0.1.
If G32 is either 9,10,11,12,13,14, then cell G33 is to return 0.2.
Look forward to getting a formula. Thank you..  Reply With Quote

2. try in G33:
=VLOOKUP(G32,{0,0;4,0.1;9,0.2;15,999},2)
or:
=INDEX({0;0.1;0.2;999},MATCH(G32,{0;4;9;15}))

You don't say what you want returned for values above 14, so I threw in 999.  Reply With Quote

3. p45cal....many thanks for your time in creating the formula for me..it works great....yes that's right, I didn't say what values I wanted returned above 14 as any number above that I have is dealt with in another cell....I acknowledge you going beyond what I asked....thank you very much..
So, since I am not concerned with numbers above 14, what will be the shortened formula then?  Reply With Quote

4. in either formula, replace the 999 with what you want to see in the cell, eg. for nothing:
=VLOOKUP(G32,{0,0;4,0.1;9,0.2;15,""},2)
for some text:
=VLOOKUP(G32,{0,0;4,0.1;9,0.2;15,"this is above 14"},2)  Reply With Quote

5. Ok, thanks for your help.  Reply With Quote

6. What is the number 2 at the end of the formula for?  Reply With Quote

7. Originally Posted by Secant What is the number 2 at the end of the formula for?

VLOOKUP(lookup_value, table_array, col_index_num, [range_lookup])

from Help:

col_index_num Required. The column number in the table_array argument from which the matching value must be returned. A col_index_num argument of 1 returns the value in the first column in table_array; a col_index_num of 2 returns the value in the second column in table_array, and so on.

{0,0;4,0.1;9,0.2;15,999}
is really a table:
 0 0 4 0.1 9 0.2 15 999  Reply With Quote

8. Thanks for the response....it all makes sense to me.  Reply With Quote

#### Posting Permissions

• You may not post new threads
• You may not post replies
• You may not post attachments
• You may not edit your posts
•