need help with a formula

%3CLINGO-SUB%20id%3D%22lingo-sub-1414205%22%20slang%3D%22en-US%22%3Eneed%20help%20with%20a%20formula%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1414205%22%20slang%3D%22en-US%22%3E%3CP%3EHello%20everyone.%26nbsp%3B%20I'm%20a%20social%20worker%20trying%20to%20lighten%20my%20load%20of%20complex%20computations%20when%20doing%20quality%20control%20activities.%26nbsp%3B%20Our%20clients%20are%20assigned%20one%20of%20multiple%20service%20levels%20which%20determines%20a%20Case%20Weight%20and%20a%20certain%20number%20of%20expected%20visits%20per%20month.%26nbsp%3B%20I%20would%20like%20to%20be%20able%20to%20enter%20the%20Service%20Level%20and%20have%20the%20corresponding%20Case%20Weight%20and%20Expected%20Visits%20columns%20to%20autopopulate%20in%20the%20appropriate%20cells.%26nbsp%3B%20If%20anyone%20can%20help%20with%20this%2C%20I'd%20be%20very%20grateful.%26nbsp%3B%20I've%20watched%20a%20ton%20of%20videos%20and%20tried%20the%20forumlas%20suggested%2C%20but%20the%20closest%20I%20came%20to%20having%20it%20work%20got%20me%20a%20%23SPILL%20error.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EThank%20you%20for%20this%20network!%3C%2FP%3E%3CP%3EAlyson%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-LABS%20id%3D%22lingo-labs-1414205%22%20slang%3D%22en-US%22%3E%3CLINGO-LABEL%3EExcel%3C%2FLINGO-LABEL%3E%3CLINGO-LABEL%3EFormulas%20and%20Functions%3C%2FLINGO-LABEL%3E%3C%2FLINGO-LABS%3E%3CLINGO-SUB%20id%3D%22lingo-sub-1414219%22%20slang%3D%22en-US%22%3ERe%3A%20need%20help%20with%20a%20formula%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1414219%22%20slang%3D%22en-US%22%3EI'm%20assuming%20you%20want%20to%20lookup%20the%20level%20in%20Q4%20and%20return%20the%20case%20weight%20and%20expected%20visits%2C%20which%20are%20in%20the%20table%20starting%20at%20Q7%20(not%20sure%20how%20far%20down%20it%20goes).%3CBR%20%2F%3E%3CBR%20%2F%3ECell%20S4%3A%20%3DVLOOKUP(Q4%2C%24Q%247%3A%24T%2450%2C3%2CFALSE)%3CBR%20%2F%3ECell%20T4%3A%20%3DVLOOKUP(Q4%2C%24Q%247%3A%24T%2450%2C4%2CFALSE)%3CBR%20%2F%3E%3CBR%20%2F%3EChange%20%24Q%247%3A%24T%2450%20to%20the%20actual%20range%20address%20of%20your%20table.%3CBR%20%2F%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-1414231%22%20slang%3D%22en-US%22%3ERe%3A%20need%20help%20with%20a%20formula%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1414231%22%20slang%3D%22en-US%22%3EYou%20assume%20correctly.%20That's%20a%20new%20formula%20for%20me%20so%20I'll%20give%20it%20a%20try.%20Thank%20you.%20I'll%20get%20pack%20to%20this%20post%20after%20I%20try%20it%20to%20let%20you%20know.%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-1414250%22%20slang%3D%22en-US%22%3ERe%3A%20need%20help%20with%20a%20formula%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1414250%22%20slang%3D%22en-US%22%3EVLookup%20performs%20a%20vertical%20lookup.%20It%20matches%20a%20given%20value%20(Q4)%20to%20the%20leftmost%20column%20of%20a%20table%20(Q7%3AT50)%20and%20then%20returns%20a%20value%20from%20the%20nth%20column%20(3%20and%204%20respectively).%20The%20last%20argument%2C%20FALSE%2C%20tells%20it%20to%20exactly%20match%20the%20item%20it%20is%20looking%20up%2C%20versus%20an%20approximate%20match.%3CBR%20%2F%3E%3CBR%20%2F%3EHopefully%20it%20works%20for%20you.%3C%2FLINGO-BODY%3E
Highlighted
New Contributor

Hello everyone.  I'm a social worker trying to lighten my load of complex computations when doing quality control activities.  Our clients are assigned one of multiple service levels which determines a Case Weight and a certain number of expected visits per month.  I would like to be able to enter the Service Level and have the corresponding Case Weight and Expected Visits columns to autopopulate in the appropriate cells.  If anyone can help with this, I'd be very grateful.  I've watched a ton of videos and tried the forumlas suggested, but the closest I came to having it work got me a #SPILL error.

 

Thank you for this network!

Alyson

3 Replies
Highlighted
I'm assuming you want to lookup the level in Q4 and return the case weight and expected visits, which are in the table starting at Q7 (not sure how far down it goes).

Cell S4: =VLOOKUP(Q4,$Q$7:$T$50,3,FALSE)
Cell T4: =VLOOKUP(Q4,$Q$7:$T$50,4,FALSE)

Change $Q$7:$T$50 to the actual range address of your table.
Highlighted
You assume correctly. That's a new formula for me so I'll give it a try. Thank you. I'll get pack to this post after I try it to let you know.
Highlighted
VLookup performs a vertical lookup. It matches a given value (Q4) to the leftmost column of a table (Q7:T50) and then returns a value from the nth column (3 and 4 respectively). The last argument, FALSE, tells it to exactly match the item it is looking up, versus an approximate match.

Hopefully it works for you.