Forum Discussion

alyson1940's avatar
alyson1940
Copper Contributor
May 23, 2020

need help with a formula

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

excel
Formulas and Functions

3 Replies

  • JMB17's avatar
    JMB17
    Bronze Contributor
    May 23, 2020
    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.
    • alyson1940's avatar
      alyson1940
      Copper Contributor
      May 23, 2020
      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.
      • JMB17's avatar
        JMB17
        Bronze Contributor
        May 23, 2020
        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.