Ozgrid Excel Help & Best Practices Forums

Excel Training / Excel Dashboards Reports

# Thread: Look up Value Based on 2 Criteria

1. I agreed to these rules
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. ## 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

3. I agreed to these rules
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

Excel Video Tutorials / Excel Dashboards Reports

4. Super Moderator
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:

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

Excel Video Tutorials / Excel Dashboards Reports

5. Super Moderator
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. Super Moderator
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. Super Moderator
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. Super Moderator
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. Super Moderator
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. Super Moderator
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

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

#### 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