Home |
Search |
Today's Posts |
#1
|
|||
|
|||
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! |
#2
|
|||
|
|||
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! |
#3
|
|||
|
|||
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! |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Vlookup against multiple columns/worksheets question | Excel Discussion (Misc queries) | |||
VLookup Question | Excel Worksheet Functions | |||
question about vlookup | Excel Worksheet Functions | |||
Vlookup Question | Excel Discussion (Misc queries) | |||
vlookup question | Excel Worksheet Functions |