Excel 2007 lookup value in table with 2 data elements

[manguilla]

Please see the table listed below. I have trying to return a % value in the last row but I need to use 2 pieces of data to find the %. So for example, if my value is 1 and the amount is 10500, I need to return 100% to my previous worksheet. The previous worksheet has the values say in A1(1) and B1(10500). So have ranges to deal with along with 2 pieces of data to find the value in row 4. Is this possible without using a macor or vb? Any help will be greatly appreciated.


A B C D
1 10210 11231 12763
2 13690 15059 17113
3 17170 18887 21463
4 100% 90% 85%


manguilla

 

[AnotherHiggins]

->So for example, if my value is 1 and the amount is 10500, I need to return 100%
Why? What math are you performing to get 100% from those two numbers? My crystal ball is hazy.

Please provide details about what you want to do.

[tt][blue]-John[/blue][/tt]
[tab][red]The plural of anecdote is not data[/red]

Help us help you. Please read FAQ 181-2886 before posting.

 

[manguilla]

Sorry here is more detail. I have stored values and ranges that deal with # people in the household and annual income. Based on those 2 criteria, I need to see where they fall on this table to I know the %(discount) that applies to them. So if there is 1 person in the household and the annual income is 10500, that person would qualify for 100%. If their income was 11,300, the value is 90%. I need to know where they fall based on the range of income and the household number. I hope this makes the crystal ball clear up . Thanks again.


manguilla

 

[Gavona]

Maybe a VLOOKUP to find the row
An Hlookup on that row to find the column which you then use to discover the value in row 4.

Seems messy. And do your values HAVE to be integers?

Could you have separate tables for each value?
With separate tables given names like Lookup1, Lookup2 and the use of the Indirect function* to specify the name of the range to lookup based on your value you could simplify to a simple vlookup or hlookup

*Indirect("Lookup"&value)

Gavin

 

[manguilla]

Yes they have to be integers. I will try seperating the tables out and see if that will work for me. I Thanks again for your help.


manguilla

 

[AnotherHiggins]

You will make your life much easier if you Normalize your table.

First, change your column headers to the respective percentages, like this:
[tt]
No. of People 100% 90% 85%
1 10210 11231 12763
2 13690 15059 17113
3 17170 18887 21463
[/tt]
Then follow Skip's terrific FAQ to get the data in a more managable format:
[tab]faq68-5287

You will end up with something that looks like this:
[tt]
Row Column Value
1 100% 10210
1 90% 11231
1 85% 12763
2 100% 13690
2 90% 15059
2 85% 17113
3 100% 17170
3 90% 18887
3 85% 21463
[/tt]
Now I'll rearrange and rename the columns, and add in an upper limit for each income range:
[tt]
Members Lower Upper
Of Income Income
Household Limit Limit Discount
3 21,463 100,000 85%
3 18,887 21,463 90%
3 17,170 18,887 100%
2 17,113 100,000 85%
2 15,059 17,113 90%
2 13,690 15,059 100%
1 12,763 100,000 85%
1 11,231 12,763 90%
1 10,210 11,231 100%
[/tt]
Now let's say that your you have the number of people in the household in Cell A1 and the income in cell B1. You could use a formula like this to find the discount:
[tab][COLOR=blue white]=SUMPRODUCT((rngMembersOfHousehold = A1) * (rngLowerIncomeLimit < B1) * (rngUpperIncomeLimit >= B1) * (rngDiscount))[/color]
(I've used named ranges for the columns of the table)

[tt][blue]-John[/blue][/tt]
[tab][red]The plural of anecdote is not data[/red]

Help us help you. Please read FAQ 181-2886 before posting.

 

[manguilla]

That got me on the right track. What I did is break up the amounts and %'s in different tables and I was able to use the HLOOKUP and VLOOKUP commands to get my results. Thanks again for all your help today.

 

[SkipVought]

What I did is break up the amounts and %'s in different tables "

That may have solved an immediate problem, but multiple tables greatly exacerbates the issue of data analysis.

Skip,

_{Just traded in my old subtlety...
for a NUANCE!}