Vlookup question
 

Hi-
I have a spreadsheet that looks at individuals and the amount they paid
within a certain year for healthcare expenditures. Based upon this level of
expenditure, I would like Excel to spit out the Category that the individual
falls into. Here is what the table for looking up the category looks like:
A B C
Category Min Max
00 $0 $20
01 $20 $165

Thus, if the expenditure is $17, they would fall into category 00. How do I
do this in Excel? I know its probably an easy VLOOKUP function but I'm not
sure how to do the VLOOKUP function when telling Excel that it should
determine whether the value falls in between the Minimum and Maximum for the
category. Any help is greatly appreciated!
thanks!

If you switch places between category and then use on range for the values
There is an error since you have 20 for both max in the 00 and min in the 01
so if you switch and then use

$0 00
$21 01
$166 02

and so on and let's say you call the table MyTable


=VLOOKUP(Amount_cell,MyTable,2)

will return 00 for $17 and 00 for $20 but 01 for $21 and $165

if you need to have the 00, 01 to the left you need to use

=INDEX(A1:A10,MATCH(Amount_cell,B1:B10,1))



--
Regards,

Peo Sjoblom


"nycguy96" wrote in message
...
Hi-
I have a spreadsheet that looks at individuals and the amount they paid
within a certain year for healthcare expenditures. Based upon this level
of
expenditure, I would like Excel to spit out the Category that the
individual
falls into. Here is what the table for looking up the category looks
like:
A B C
Category Min Max
00 $0 $20
01 $20 $165

Thus, if the expenditure is $17, they would fall into category 00. How do
I
do this in Excel? I know its probably an easy VLOOKUP function but I'm
not
sure how to do the VLOOKUP function when telling Excel that it should
determine whether the value falls in between the Minimum and Maximum for
the
category. Any help is greatly appreciated!
thanks!

Thank you for the tip-I really appreciate the help! Actually, I was playing
around with it and doing some research and found out that I should just use
the minimum values and have the category to the right of the minimums. It
worked beautifully.
thanks again!

"Peo Sjoblom" wrote:

If you switch places between category and then use on range for the values
There is an error since you have 20 for both max in the 00 and min in the 01
so if you switch and then use

$0 00
$21 01
$166 02

and so on and let's say you call the table MyTable


=VLOOKUP(Amount_cell,MyTable,2)

will return 00 for $17 and 00 for $20 but 01 for $21 and $165

if you need to have the 00, 01 to the left you need to use

=INDEX(A1:A10,MATCH(Amount_cell,B1:B10,1))



--
Regards,

Peo Sjoblom


"nycguy96" wrote in message
...
Hi-
I have a spreadsheet that looks at individuals and the amount they paid
within a certain year for healthcare expenditures. Based upon this level
of
expenditure, I would like Excel to spit out the Category that the
individual
falls into. Here is what the table for looking up the category looks
like:
A B C
Category Min Max
00 $0 $20
01 $20 $165

Thus, if the expenditure is $17, they would fall into category 00. How do
I
do this in Excel? I know its probably an easy VLOOKUP function but I'm
not
sure how to do the VLOOKUP function when telling Excel that it should
determine whether the value falls in between the Minimum and Maximum for
the
category. Any help is greatly appreciated!
thanks!