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..

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.

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?

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)

5. Ok, thanks for your help.

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

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

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

#### Posting Permissions

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