Help with friend's spreadsheet for work

[itzzjason]

	A	B	C	D	E	F	G	H	I	J	K	L	M
1		Full time (100%)	Full time (100%)	Full time (100%)	3/4 time (75%)	3/4 time (75%)	3/4 time (75%)	1/2 time (50%)	1/2 time (50%)	1/2 time (50%)	<1/2 time (<50%)	<1/2 time (<50%)	<1/2 time (<50%)
2	Range	Full year	1st half	2nd half	Full year	1st half	2nd half	Full year	1st half	2nd half	Full year	1st half	2nd half
3	0-0	7000	3400	3600	2313	24789	2426	4572	14343	5454	21424	2427	124274
4	1-100	6500	3200	3300	...	...	...	...	...	...	...	.....	...
5	101-200	6547	2941	4174	...	...	...	...	...	...	...	...	...
6	201-300	98732	3234	32465	...	...	...	...	...	...	...	...	...
7	301-400	...	...	...	...	...	...	...	...	...	...	...	...
8	401-500	...	...	...	...	...	...	...	...	...	...	...	....
9	501-600	...	...	...	...	...	...	...	...	...	...	...	...
	.........
	(goes down to 1 million)

My friend wants an easier way to look at this chart.

He's provided a number, percentage, and what part of the year. He would then look which range (column A) the number falls under, and cross reference with the percentage and part of the year.

For example, he's given 153, 100%, 2nd half. Then he would use cell D5 ($4174).

Is there a formula to do that for him?

I was thinking he can add somewhere to the right of the chart, "Percentage" " Part of the year" "Number", and he can input those himself. The cell underneath will contain a formula to look up the corresponding cell.

Should he split the Range column into 2 columns, Range min and Range max?

 

[kweaver]

What version of Excel does your friend use?

 

[itzzjason]

What version of Excel does your friend use?

It says "Microsoft 365 Apps for enterprise"

 

[kweaver]

Here's one quickly thrown together way:

Book1
	A	B	C	D	E	F	G	H	I	J	K	L	M	N	O	P
1			Full time (100%)	Full time (100%)	Full time (100%)	3/4 time (75%)	3/4 time (75%)	3/4 time (75%)	1/2 time (50%)	1/2 time (50%)	1/2 time (50%)	<1/2 time (<50%)	<1/2 time (<50%)	<1/2 time (<50%)
2	Range		Full year	1st half	2nd half	Full year	1st half	2nd half	Full year	1st half	2nd half	Full year	1st half	2nd half		153
3	0	0	7000	3400	3600	2313	24789	2426	4572	14343	5454	21424	2427	124274		<1/2 time
4	1	100	6500	3200	3300	...	...	...	...	...	...	50	.....	...		Full year
5	101	200	6547	2941	4174	...	10	...	777	...	...	60	...	...		60
6	201	300	98732	3234	32465	...	20	...	888	...	...	70	...	...
7	301	400	...	...	...	...	30	...	999	...	...	80	...	...
8	401	500	...	...	...	...	40	...	...	...	...	90	...	....
9	501	600	...	...	...	...	50	...	...	...	...	100	...	...
Sheet1

Cell Formulas
Range	Formula
P5	P5	=INDEX(C3:N9,MATCH(P2,A3:A9),MMULT((P3=LEFT(C1:N1,LEN(P3)))*(P4=C2:N2),ROW(1:12)))

Could replace the MMULT process with SEQUENCE as part of the calculation since friend is running 365.