Announcement

Collapse
No announcement yet.

Look up Value Based on 2 Criteria

Collapse
X
  • Filter
  • Time
  • Show
Clear All
new posts

  • Look up Value Based on 2 Criteria

    I am trying to create a sliding fee scale for a medical practice. Essentially it will categorize patients by family size and income level. The table which the scale is based off of is as follows: The far left column is family size (1-10) followed by 4 monthly income levels (ie. 1000, 1200, 1400, 1600). The table is based in the federal poverty line (FPL). I need to create a lookup formula which will reference this value and generate the appropriate category based on the income and family size of the patient. For example, according to the table a 3 person family which earns less than 1000 is in category A, but between 1000 and 1200 is in category B. Any help would be greatly appreciated. Thanks!!

    Nathan

  • #2
    Re: Look up value based on two criteria

    hi Nathan,

    welcome to Ozgrid,

    i came up with a table which i think is what you are describing, coz i don't really understand yr post
    adjust the numbers in the table to yr own.

    i used SumProduct and Vlookup
    Attached Files
    Cheers
    ___________
    Xlite
    All you need to learn VBA is an internet connection and Ozgrid.com

    Comment


    • #3
      Re: Look up Value Based on 2 Criteria

      Sorry, I am sure my description wasn't very clear. Here is the file I am working with which includes 3 worksheets. The monthly table in the FPL worksheet is what everything is based off of. For example, a family size of 4 who earns 2100 a month is a Level C. If the same family earns 2500 they are a level D. I want my sheet set up so the person merely inputs their family size and monthly income and then their Level will be given in cell C4 on the Assistance Level worksheet. Thanks for all the help.

      Nathan
      Attached Files

      Comment


      • #4
        Re: Look up Value Based on 2 Criteria

        Try this formula in cell C4:

        =IF(ISERROR(MATCH(B4,INDIRECT(ADDRESS(A4+3,2,1,TRUE,"FPL")):INDIRECT(ADDRESS(A4+3,5,1,TRUE,"FPL")),0)),INDEX(FPL!B2:E2,MATCH(B4,INDIRECT(ADDRESS(A4+3,2,1,TRUE,"FPL")):INDIRECT(ADDRESS(A4+3,5,1,TRUE,"FPL")),1)+1),INDEX(FPL!B2:E2,MATCH(B4,INDIRECT(ADDRESS(A4+3,2,1,TRUE,"FPL")):INDIRECT(ADDRESS(A4+3,5,1,TRUE,"FPL")),0)))

        EDITED to correct formula
        Last edited by ByTheCringe2; September 7th, 2006, 08:23.
        .

        Comment


        • #5
          Re: Look up Value Based on 2 Criteria

          Hi,

          Try,

          =INDEX(FPL!B2:E2,0,MATCH(B4,INDEX(FPL!B4:E13,MATCH(A4,FPL!A4:A13,0),0))+1)

          HTH
          Kris

          ExcelFox

          Comment


          • #6
            Re: Look up Value Based on 2 Criteria

            Kris, Try Family Size=4, Income Level=2000. Your formula does not give the right result. That's why mine is three times as long...

            EDIT: When you fix it, I can see your formula will be much better than mine...
            Last edited by ByTheCringe2; September 7th, 2006, 15:57.
            .

            Comment


            • #7
              Re: Look up Value Based on 2 Criteria

              OK. Here goes

              =INDEX(FPL!B2:E2,0,LOOKUP(9.9999E+307,CHOOSE({1,2},MATCH(B4,INDEX(FPL!B4:E13,MATCH(A4,FPL!A4:A13,0),0))+1,MATCH(B4,INDEX(FPL!B4:E13,MATCH(A4,FPL!A4:A13,0),0),0))))

              HTH

              BTW, length of the formula is not a matter. For me it should be simple, non-array(as possible as) and non-volatile
              Last edited by Krishnakumar; September 7th, 2006, 16:05.
              Kris

              ExcelFox

              Comment


              • #8
                Re: Look up Value Based on 2 Criteria

                Fine. Don't understand it, but it works, LOL.
                Last edited by ByTheCringe2; September 7th, 2006, 16:12.
                .

                Comment


                • #9
                  Re: Look up Value Based on 2 Criteria

                  CHOOSE gives an array of values.

                  MATCH(B4,INDEX(FPL!B4:E13,MATCH(A4,FPL!A4:A13,0),0))+1 returns 3

                  MATCH(B4,INDEX(FPL!B4:E13,MATCH(A4,FPL!A4:A13,0),0),0) returns 2

                  now LOOKUP returns the last value of the array,i.e. 2.

                  HTH
                  Kris

                  ExcelFox

                  Comment


                  • #10
                    Re: Look up Value Based on 2 Criteria

                    Thanks, I will work on it.
                    .

                    Comment


                    • #11
                      Re: Look up Value Based on 2 Criteria

                      My interpretation of the question was to return a result regardless of Income amount. THe formulas proposed so far do not consider what happens if the income falls below the $1,385 ( used in the Example for a 3 person family ) or exceeds the upper limit. In either case the formula resolves to and error of sorts. Is there a way to ensure that a maximum is returned for any Income level that exceeds the amount on the FPL table for that family size and the minimum for any that is below the amount of the FPL table?

                      As the formula is written currently a valid result will only be displayed if the Income level falls between the Max and Min amounts on the FPL table for that family size.

                      I was trying to figure this out with an Offset formula, you guys have gotten much further than I had.

                      Cheers
                      Michael

                      Comment


                      • #12
                        Re: Look up Value Based on 2 Criteria

                        Thanks so much! The only problem I seem to run into is that if a family of any size earns less than 100% of the FPL (federal poverty line) it returns a value of #NA. Any income less than the Level A should qualify as a level A. Also any income greater than level D should not qualify. Thanks for all the help.

                        Nathan

                        Comment


                        • #13
                          Re: Look up Value Based on 2 Criteria

                          Originally posted by Krishnakumar
                          OK. Here goes

                          =INDEX(FPL!B2:E2,0,LOOKUP(9.9999E+307,CHOOSE({1,2},MATCH(B4,INDEX(FPL!B4:E13,MATCH(A4,FPL!A4:A13,0),0))+1,MATCH(B4,INDEX(FPL!B4:E13,MATCH(A4,FPL!A4:A13,0),0),0))))

                          Hi Krishnakumar nice formula. I've notice if I change the family size to 1 and income level to 817 I get level B, It should be Level A correct? If not let me know.

                          Comment


                          • #14
                            Re: Look up Value Based on 2 Criteria

                            Hi,

                            Thanks so much! The only problem I seem to run into is that if a family of any size earns less than 100% of the FPL (federal poverty line) it returns a value of #NA. Any income less than the Level A should qualify as a level A. Also any income greater than level D should not qualify. Thanks for all the help.
                            Try,

                            In D3,

                            =SUMPRODUCT(--(FPL!A4:A13=A4),--(B4>=FPL!B4:B13))

                            In E3,

                            =SUMPRODUCT(--(FPL!A4:A13=A4),--(FPL!E4:E13<B4))

                            In C4,

                            =IF((D3=1)*(E3=1),"",IF((D3=0)*(E3=0),"Level A",INDEX(FPL!B2:E2,0,LOOKUP(9.9999E+307,CHOOSE({1,2},MATCH(B4,INDEX(FPL!B4:E13,MATCH(A4,FPL!A4:A13,0),0))+1,MATCH(B4,INDEX(FPL!B4:E13,MATCH(A4,FPL!A4:A13,0),0),0))))))

                            in B4 on sheet FPL and copied down & across,

                            =ROUND(B18/12,0)

                            Hi Krishnakumar nice formula.
                            Thanks!

                            I've notice if I change the family size to 1 and income level to 817 I get level B, It should be Level A correct? If not let me know.
                            The actual figure on sheet FPL is 816.67 not 817.

                            HTH
                            Last edited by Krishnakumar; September 8th, 2006, 13:45.
                            Kris

                            ExcelFox

                            Comment


                            • #15
                              Re: Look up Value Based on 2 Criteria

                              Thank you so much Krishnakumar and everyone else for all of your help. I couldn't have done it without you!

                              Nathan

                              Comment

                              Working...
                              X