How to have a cell return a number based on a range in another tab

[cmclaughlin]

Hello everyone!

Okay, so I have two tabs. The first tab is this.

The cell containing 132,602 is cell C9. The cell containing nothing is cell C10.

Then I have the second tab, which I've named Scale:

I am trying to figure out a formula that will take the number in C9 from the first tab, the 132,602, and then compare it to columns D & I in Scale, and then give me the correct number from column A or F.

The difference between the Non-VIP and VIP is a checkbox/button that I set up that gives back TRUE or FALSE, so ideally if I have it unchecked it will be Non-VIP and if I have it checked off it will be VIP.

I've been trying different things and just cannot get this to work.

Any help would be greatly appreciated.

Thank you!

 

[bartmaster]

Try this to start with (only looks at the first table):

=INDEX(Scale!$A$3:$A$35,MATCH($C$10,Scale!$D$3:$D$35,1))

If this works for you then add IF formula to look at the right table

 

[Toadstool]

Bartmaster beat me...

cmclaughlin.xlsx
	A	B	C	D	E	F	G	H	I
1		Non-VIP					VIP
2	Purchase	Base	Add	Cumul		Purchase	Base	Add	Cumul
3	1	10000	0	10000		1	9000	0	9000
4	2	11000	1000	21000		2	9900	900	18900
5	3	12000	1000	33000		3	10800	900	29700
6	4	13000	1000	46000		4	11700	900	41400
7	5	14000	1000	60000		5	12600	900	54000
8	6	15000	1000	75000		6	13500	900	67500
9	7	16000	1000	91000		7	14400	900	81900
10	8	17000	1000	108000		8	15300	900	97200
11	9	18000	1000	126000		9	16200	900	113400
12	10	19000	1000	145000		10	17100	900	130500
13	11	20000	1000	165000		11	18000	900	148500
14	12	21000	1000	186000		12	18900	900	167400
15	13	22000	1000	208000		13	19800	900	187200
16	14	23000	1000	231000		14	20700	900	207900
17	15	24000	1000	255000		15	21600	900	229500
18	16	25000	1000	280000		16	22500	900	252000
19	17	26000	1000	306000		17	23400	900	275400
20	18	27000	1000	333000		18	24300	900	299700
21	19	28000	1000	361000		19	25200	900	324900
22	20	29000	1000	390000		20	26100	900	351000
23	21	30000	1000	420000		21	27000	900	378000
24	22	30500	500	450500		22	27450	450	405450
25	23	31000	500	481500		23	27900	450	433350
26	24	31500	500	513000		24	28350	450	461700
27	25	32000	500	545000		25	28800	450	490500
28	26	32500	500	577500		26	29250	450	519750
29	27	33000	500	610500		27	29700	450	549450
30	28	33500	500	644000		28	30150	450	579600
31	29	34000	500	678000		29	30600	450	610200
32	30	34500	500	712500		30	31050	450	641250
33	31	35000	500	747500		31	31500	450	672750
34	32	35500	500	783000		32	31950	450	704700
35	33	36000	500	819000		33	32400	450	737100
Scale

Cell Formulas
Range	Formula
F4:F35,A4:A35	A4	=A3+1
G4:G35,B4:B35	B4	=B3+C4
D3,I3	D3	=B3
I4:I35,D4:D35	D4	=D3+B4

cmclaughlin.xlsx
	A	B	C	D
1
2
3
4
5
6
7
8		VIP?	TRUE
9		Total Points	132602
10		Action	10
11
1stTab

Cell Formulas
Range	Formula
C10	C10	=IFERROR(IF($C$8,INDEX(Scale!$F$3:$F$35,MATCH($C$9,Scale!$I$3:$I$35,1)),INDEX(Scale!$A$3:$A$35,MATCH($C$9,Scale!$D$3:$D$35,1))),"Too few points")

Cells with Data Validation
Cell	Allow	Criteria
C8	List	TRUE,FALSE

 

[bartmaster]

@Toadstool,
That's exactly what I meant but you did all hard work

 

[Fluff]

As you have xl365, another option

+Fluff 1.xlsm
	A	B	C
1
2
3
4
5
6
7
8		VIP?	TRUE
9		Total Points	132602
10		Action	10
Master

Cell Formulas
Range	Formula
C10	C10	=XLOOKUP(C9,IF(C8,Scale!$I$3:$I$35,Scale!$D$3:$D$35),IF(C8,Scale!$F$3:$F$35,Scale!$A$3:$A$35),"",-1)