find value on table and print column lable

[covingj]

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%

 

[Ron Coderre]

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]

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

 

[David Biddulph]

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.

 

[covingj]

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