find value on table and print column lable
 

I want to create a table with income figures and then look up values based on
household size that is calculated and then return the column lable. For
instance, a 4x4 table with family size 1, 2, 3, 4 and column lables of 100%,
125%, 150% and 175%. The grid is filled with income amounts at each
intersection.

100% 125% 150% 175%
1 500 625 750 875
2 1000 1250 1500 1750
3 1500 1875 2250 2625
4 2000 2500 3000 3500

I want to look up an income amount for a calculated household size (using a
count function), and return the column lable to show the percentage, so that
a household of three with income of $1800 will return a value of 125%

find value on table and print column lable
 

With your posted data in A1:E5

And
G1: (an income....eg $1800)
H1: (a family size...eg 3)

This formula return the associated percentage from Row_1:
=INDEX(B1:E1,MATCH(1,FREQUENCY(G1,OFFSET(B1:E1,H1, ,)),0))

In the above example, the formula returns 125%

Is that something you can work with?
--------------------------

Regards,

Ron
Microsoft MVP (Excel)
(XL2003, Win XP)
"covingj" wrote in message
...
I want to create a table with income figures and then look up values based
on
household size that is calculated and then return the column lable. For
instance, a 4x4 table with family size 1, 2, 3, 4 and column lables of
100%,
125%, 150% and 175%. The grid is filled with income amounts at each
intersection.

100% 125% 150% 175%
1 500 625 750 875
2 1000 1250 1500 1750
3 1500 1875 2250 2625
4 2000 2500 3000 3500

I want to look up an income amount for a calculated household size (using
a
count function), and return the column lable to show the percentage, so
that
a household of three with income of $1800 will return a value of 125%

find value on table and print column lable
 

Thank you, that appears to work very well. Now to try to understand it and
apply.

"Ron Coderre" wrote:

With your posted data in A1:E5

And
G1: (an income....eg $1800)
H1: (a family size...eg 3)

This formula return the associated percentage from Row_1:
=INDEX(B1:E1,MATCH(1,FREQUENCY(G1,OFFSET(B1:E1,H1, ,)),0))

In the above example, the formula returns 125%

Is that something you can work with?
--------------------------

Regards,

Ron
Microsoft MVP (Excel)
(XL2003, Win XP)
"covingj" wrote in message
...
I want to create a table with income figures and then look up values based
on
household size that is calculated and then return the column lable. For
instance, a 4x4 table with family size 1, 2, 3, 4 and column lables of
100%,
125%, 150% and 175%. The grid is filled with income amounts at each
intersection.

100% 125% 150% 175%
1 500 625 750 875
2 1000 1250 1500 1750
3 1500 1875 2250 2625
4 2000 2500 3000 3500

I want to look up an income amount for a calculated household size (using
a
count function), and return the column lable to show the percentage, so
that
a household of three with income of $1800 will return a value of 125%

find value on table and print column lable
 

Do you really need a lookup table? In your case the numbers seem to be
straight multiplications by 500.
Your $1800 dollars would be =$1800/(3*$500) which would give 120%.
If you want to round to the nearest 25% you could use
=MROUND(1800/3/500,25%) or if you want to round up you could use
=CEILING(1800/3/500,25%).
Naturally the relevant values in the formula could use cell references as
appropriate.
--
David Biddulph

"covingj" wrote in message
...
I want to create a table with income figures and then look up values based
on
household size that is calculated and then return the column lable. For
instance, a 4x4 table with family size 1, 2, 3, 4 and column lables of
100%,
125%, 150% and 175%. The grid is filled with income amounts at each
intersection.

100% 125% 150% 175%
1 500 625 750 875
2 1000 1250 1500 1750
3 1500 1875 2250 2625
4 2000 2500 3000 3500

I want to look up an income amount for a calculated household size (using
a
count function), and return the column lable to show the percentage, so
that
a household of three with income of $1800 will return a value of 125%

find value on table and print column lable
 

Just checking old questions. Sorry for teh confusion, the table in the
question was just an example. The actual table is bigger and is adjusted
annually.

Thanks

"David Biddulph" wrote:

Do you really need a lookup table? In your case the numbers seem to be
straight multiplications by 500.
Your $1800 dollars would be =$1800/(3*$500) which would give 120%.
If you want to round to the nearest 25% you could use
=MROUND(1800/3/500,25%) or if you want to round up you could use
=CEILING(1800/3/500,25%).
Naturally the relevant values in the formula could use cell references as
appropriate.
--
David Biddulph

"covingj" wrote in message
...
I want to create a table with income figures and then look up values based
on
household size that is calculated and then return the column lable. For
instance, a 4x4 table with family size 1, 2, 3, 4 and column lables of
100%,
125%, 150% and 175%. The grid is filled with income amounts at each
intersection.

100% 125% 150% 175%
1 500 625 750 875
2 1000 1250 1500 1750
3 1500 1875 2250 2625
4 2000 2500 3000 3500

I want to look up an income amount for a calculated household size (using
a
count function), and return the column lable to show the percentage, so
that
a household of three with income of $1800 will return a value of 125%