Cannot figure out correct formula to use

[bororob85]

Hello all. So I am attempting to make an easy tool for us to calculate a sliding scale for financial assistance. There are 2 numbers that drive this and that is: Household Size and Yearly Income. Based on where that falls on the chart, a certain percentage would be applied.

Essentially its a range lookup. For 100% anything equal or less than should give a 100% response. Anything that is greater than or equal/less than the 50% Column should return 50% response and thus the same would apply for the 25% and then anything greater than the 25% column should return zero.

It does not have to be on the same sheet but for picture purposes I put on the same sheet but I did initially have sheet 1 for the tool and sheet 2 for the data ranges.

 

[awoohaw]

Welcome to the forum!

maybe try this (sorry, not using your exact numbers, you can paste your numbers in)

Book2
	A	B	C	D
1	Household Size	8
2
3	Yearly Income	201000
4
5	Percentage Off	100%
6
7	Household Size	100%	50%	25%
8	1	58,000	72,900	88,000
9	2	78,900	98,600	118,000
10	3	99,400	124,300	149,000
11	4	120,000	150,000	180,000
12	5	140,000	175,000	210,000
13	6	151,000	201,000	241,000
14	7	181,000	227,000	270,000
15	8	202,000	252,000	303,000
16	9	222,200	278,000	334,000
Sheet2

Cell Formulas
Range	Formula
B5	B5	=XLOOKUP(B3,INDEX($B$8:$D$16,$B$1,0),$B$7:$D$7,0,1,1)

 

[bororob85]

BOOM! I was trying to do vlookup and if statements.