Loading
Ozgrid Excel Help & Best Practices Forums

Excel Training / Excel Dashboards Reports



Page 1 of 2 1 2 LastLast
Results 1 to 10 of 15

Thread: Look up Value Based on 2 Criteria

  1. #1
    Join Date
    6th September 2006
    Posts
    4

    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

    Excel Video Tutorials / Excel Dashboards Reports


  2. #2
    Join Date
    13th September 2003
    Location
    Singapore
    Posts
    1,236

    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. REMINDER! OzGrid accepts no responsibility for ANY adverse effects as a result from downloading attached files. ALWAYS run an up-to-date virus scan and disable macros. Attached Files. REMINDER! OzGrid accepts no responsibility for ANY adverse effects as a result from downloading attached files. ALWAYS run an up-to-date virus scan and disable macros.
    Cheers
    ___________
    Xlite
    All you need to learn VBA is an internet connection and Ozgrid.com

  3. #3
    Join Date
    6th September 2006
    Posts
    4

    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. REMINDER! OzGrid accepts no responsibility for ANY adverse effects as a result from downloading attached files. ALWAYS run an up-to-date virus scan and disable macros. Attached Files. REMINDER! OzGrid accepts no responsibility for ANY adverse effects as a result from downloading attached files. ALWAYS run an up-to-date virus scan and disable macros.

    Excel Video Tutorials / Excel Dashboards Reports


  4. #4
    Join Date
    7th December 2005
    Location
    Hampshire, England
    Posts
    4,898

    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 at 08:23.

    Excel Video Tutorials / Excel Dashboards Reports


  5. #5
    Join Date
    18th November 2004
    Location
    God's Own Country
    Posts
    4,678

    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

  6. #6
    Join Date
    7th December 2005
    Location
    Hampshire, England
    Posts
    4,898

    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 at 15:57.

    Excel Video Tutorials / Excel Dashboards Reports


  7. #7
    Join Date
    18th November 2004
    Location
    God's Own Country
    Posts
    4,678

    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 at 16:05.

  8. #8
    Join Date
    7th December 2005
    Location
    Hampshire, England
    Posts
    4,898

    Re: Look up Value Based on 2 Criteria

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

    Excel Video Tutorials / Excel Dashboards Reports


  9. #9
    Join Date
    18th November 2004
    Location
    God's Own Country
    Posts
    4,678

    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

  10. #10
    Join Date
    7th December 2005
    Location
    Hampshire, England
    Posts
    4,898

    Re: Look up Value Based on 2 Criteria

    Thanks, I will work on it.

    Excel Video Tutorials / Excel Dashboards Reports


Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Possible Answers

  1. Replies: 5
    Last Post: March 5th, 2008, 02:10
  2. Sum Based On 1 Criteria
    By Tee in forum Excel General
    Replies: 2
    Last Post: July 18th, 2007, 14:54
  3. Add Row Based On Criteria
    By JoePineapples in forum Excel General
    Replies: 6
    Last Post: March 7th, 2007, 17:56
  4. To sum value based on criteria
    By Tee in forum Excel General
    Replies: 5
    Last Post: March 29th, 2006, 21:29
  5. counting one criteria based on another criteria
    By chris1313 in forum Excel General
    Replies: 5
    Last Post: January 28th, 2006, 02:51

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  
porno