VLookup Not Working....And I am Not Sure Why

[Jingles3X]

Hello! I have gotten some great help getting to this point in my project, and I am very grateful for that.
Currently, I am having Vlookup issues. Basically, I have a table that lists 3 columns, as follows " If the price is between 1.00 (first column), and 10.00 (second column) then the multiplier should be 5 (third Column). This data is on one worksheet. The place I am putting the formula is on another worksheet in column AP. I believe it should be working, but am not sure why it is not...

This mini sheet is where the formula is being entered, in AP9

FRP-In Progress.xlsx
	T	U	V	W	X	Y	Z	AA	AB	AC	AD	AE	AF	AG	AH	AI	AJ	AK	AL	AM	AN	AO	AP	AQ
4
5																		Pricing Calculation
6					Plan Pricing													Auto Fills	Auto Fills	Manually Enter	Auto Fills	Manually Enter	Auto Fills	Auto Fills
7	Standard Price								Secondary Repair				Plan Savings !					Diagnostic Charge	Labor Rate	Time To Complete	Advantage Discount %	Materials Cost	Materials Markup	Warranty Reserve
8
9	#N/A				#N/A				#N/A				#N/A					$ 95.00	$ 154.88	1.00	0.85	$ 8.00	#N/A	3%
10	302				257				207				45					$ 95.00	$ 154.88	0.50	0.85	$ 26.00	5.00	3%
11	404				343				309				61					$ 95.00	$ 154.88	0.75	0.85	$ 55.00	3.50	3%
12	526				447				431				79					$ 95.00	$ 154.88	0.75	0.85	$ 105.00	3.00	3%
13	836				711				741				125					$ 95.00	$ 154.88	0.75	0.85	$ 250.00	2.50	3%
14	961				817				866				144					$ 95.00	$ 154.88	0.75	0.85	$ 375.00	2.00	3%
15	1711				1455				1616				257					$ 95.00	$ 154.88	0.75	0.85	$ 1,200.00	1.25	3%
16	1931				1642				1836				290					$ 95.00	$ 154.88	0.75	0.85	$ 1,600.00	1.08	3%
17	211				180				116				32					$ 95.00	$ 154.88	0.75	0.85	$ 65.00		3%
18	116				0				116				116						$ 154.88	0.75		$ 134.00
19	116				0				116				116						$ 154.88	0.75		$ 65.00
20	211				180				116				32					$ 95.00	$ 154.88	0.75	0.85	$ 234.00		3%
21	211				180				116				32					$ 95.00	$ 154.88	0.75	0.85	$ 32.00		3%
22	211				180				116				32					$ 95.00	$ 154.88	0.75	0.85	$ 123.00		3%
23	211				180				116				32					$ 95.00	$ 154.88	0.75	0.85	$ 123.00		3%
24	211				180				116				32					$ 95.00	$ 154.88	0.75	0.85	$ 365.00		3%
25	211				180				116				32					$ 95.00	$ 154.88	0.75	0.85	$ 43.00		3%
26	211				180				116				32					$ 95.00	$ 154.88	0.75	0.85	$ 56.00		3%
27	211				180				116				32					$ 95.00	$ 154.88	0.75	0.85	$ 67.00		3%
28	211				180				116				32					$ 95.00	$ 154.88	0.75	0.85	$ 87.00		3%
29	211				180				116				32					$ 95.00	$ 154.88	0.75	0.85	$ 987.00		3%
30	211				180				116				32					$ 95.00	$ 154.88	0.75	0.85			3%
Flat Rate Book

Cell Formulas
Range	Formula
T9:T30	T9	=SUM((AL9*AM9)+(AO9*AP9)+AQ9)+AK9
X9,X11:X30	X9	=SUM(T9*AN9)
AB9:AB30	AB9	=SUM(T9-AK9)
AF9:AF30	AF9	=SUM(T9-X9)
X10	X10	=SUM(T10*AN9)
AP9	AP9	=VLOOKUP(AO9,'Set-Up'!$C$24:$D$32,'Set-Up'!E24:E32)
AP11:AP14	AP11	=IF(AND(AO11>'Set-Up'!B26,AO11<'Set-Up'!D26),'Set-Up'!E26)
AP15:AP16	AP15	=IF(AND(AO15>'Set-Up'!B31,AO15<'Set-Up'!D31),'Set-Up'!E31)
AK9	AK9	='Set-Up'!F11
AK10	AK10	='Set-Up'!F11
AK11	AK11	='Set-Up'!F11
AK12	AK12	='Set-Up'!F11
AK13	AK13	='Set-Up'!F11
AK14	AK14	='Set-Up'!F11
AK15	AK15	='Set-Up'!F11
AK16	AK16	='Set-Up'!F11
AK17	AK17	='Set-Up'!F11
AN9	AN9	='Set-Up'!F45
AN10	AN10	='Set-Up'!F45
AN11	AN11	='Set-Up'!F45
AN12	AN12	='Set-Up'!F45
AN13	AN13	='Set-Up'!F45
AN14	AN14	='Set-Up'!F45
AN15	AN15	='Set-Up'!F45
AN16	AN16	='Set-Up'!F45
AN17	AN17	='Set-Up'!F45
AQ9	AQ9	='Set-Up'!F15
AQ10	AQ10	='Set-Up'!F15
AQ11	AQ11	='Set-Up'!F15
AQ12	AQ12	='Set-Up'!F15
AQ13	AQ13	='Set-Up'!F15
AQ14	AQ14	='Set-Up'!F15
AQ15	AQ15	='Set-Up'!F15
AQ16	AQ16	='Set-Up'!F15
AQ17	AQ17	='Set-Up'!F15
AL9	AL9	='Labor Calculator'!R23
AL10	AL10	='Labor Calculator'!R23
AL11	AL11	='Labor Calculator'!R23
AL12	AL12	='Labor Calculator'!R23
AL13	AL13	='Labor Calculator'!R23
AL14	AL14	='Labor Calculator'!R23
AL15	AL15	='Labor Calculator'!R23
AL16	AL16	='Labor Calculator'!R23
AL17	AL17	='Labor Calculator'!R23
AL18	AL18	='Labor Calculator'!R23
AL19	AL19	='Labor Calculator'!R23
AK20	AK20	='Set-Up'!F11
AL20	AL20	='Labor Calculator'!R23
AK21	AK21	='Set-Up'!F11
AL21	AL21	='Labor Calculator'!R23
AK22	AK22	='Set-Up'!F11
AL22	AL22	='Labor Calculator'!R23
AK23	AK23	='Set-Up'!F11
AL23	AL23	='Labor Calculator'!R23
AK24	AK24	='Set-Up'!F11
AL24	AL24	='Labor Calculator'!R23
AK25	AK25	='Set-Up'!F11
AL25	AL25	='Labor Calculator'!R23
AK26	AK26	='Set-Up'!F11
AL26	AL26	='Labor Calculator'!R23
AK27	AK27	='Set-Up'!F11
AL27	AL27	='Labor Calculator'!R23
AK28	AK28	='Set-Up'!F11
AL28	AL28	='Labor Calculator'!R23
AK29	AK29	='Set-Up'!F11
AL29	AL29	='Labor Calculator'!R23
AK30	AK30	='Set-Up'!F11
AL30	AL30	='Labor Calculator'!R23
AN20	AN20	='Set-Up'!F45
AN21	AN21	='Set-Up'!F45
AN22	AN22	='Set-Up'!F45
AN23	AN23	='Set-Up'!F45
AN24	AN24	='Set-Up'!F45
AN25	AN25	='Set-Up'!F45
AN26	AN26	='Set-Up'!F45
AN27	AN27	='Set-Up'!F45
AN28	AN28	='Set-Up'!F45
AN29	AN29	='Set-Up'!F45
AN30	AN30	='Set-Up'!F45
AQ20	AQ20	='Set-Up'!F15
AQ21	AQ21	='Set-Up'!F15
AQ22	AQ22	='Set-Up'!F15
AQ23	AQ23	='Set-Up'!F15
AQ24	AQ24	='Set-Up'!F15
AQ25	AQ25	='Set-Up'!F15
AQ26	AQ26	='Set-Up'!F15
AQ27	AQ27	='Set-Up'!F15
AQ28	AQ28	='Set-Up'!F15
AQ29	AQ29	='Set-Up'!F15
AQ30	AQ30	='Set-Up'!F15

This mini sheet is where the data is drawing from...

FRP-In Progress.xlsx
	A	B	C	D	E
23		Parts Cost Between This….		And This…	The Multiplier Should Be….
24		$ -		$ 25.00	5
25		$ 25.01		$ 50.00	4
26		$ 50.01		$ 100.00	3.5
27		$ 100.01		$ 200.00	3
28		$ 200.01		$ 350.00	2.5
29		$ 350.01		$ 500.00	2
30		$ 500.01		$ 1,000.00	1.5
31		$ 1,000.01		$ 1,500.00	1.25
32		$ 1,500.00		$ 200,000.00	1.075
Set-Up

Can anyone spot why this would not be working?

 

[Peter_SSs]

Try

=VLOOKUP(AO9,'Set-Up'!$B$24:$E$32,4)

B24 will need to contain a zero.

 

[Jingles3X]

Try

=VLOOKUP(AO9,'Set-Up'!$B$24:$E$32,4)

B24 will need to contain a zero.

DOing it this way makes perfect sense to me.... but it is still returning a value of '5' for all items regardless of cost...
=+VLOOKUP(AO9,'Set-Up'!$B$24:$D$32,3) (when is substitute the last condition with 4 for the column as in your formula, it returns #ref.
Any other help would be truly appreciated.

FRP-In Progress.xlsx
	T	U	V	W	X	Y	Z	AA	AB	AC	AD	AE	AF	AG	AH	AI	AJ			AM	AN	AO	AP	AQ
6					Partner Pricing															Manually Enter	Auto Fills	Manually Enter	Auto Fills	Auto Fills
7	Standard Price								Secondary Repair				Secondary Rep. Partner $							Time To Complete	Partner Discount %	Materials Cost	Materials Markup	Warranty Reserve
8
9	#REF!				#REF!				#REF!				#REF!							1.00	0.85	$ 8.50	#REF!	3%
10	302				257				207				187							0.50	0.85	$ 26.00	5.00	3%
11	486				413				391				352							0.75	0.85	$ 55.00	5.00	3%
12	736				626				641				577							0.75	0.85	$ 105.00	5.00	3%
13	1461				1242				1366				1230							0.75	0.85	$ 250.00	5.00	3%
14	2086				1773				1991				1792							0.75	0.85	$ 375.00	5.00	3%
15	6211				5280				6116				5505							0.75	0.85	$ 1,200.00	5.00	3%
16	8211				6980				8116				7305							0.75	0.85	$ 1,600.00	5.00	3%
17	536				456				441				397							0.75	0.85	$ 65.00	5.0	3%
18	881				749				786				708							0.75	0.85	$ 134.00	5.00	3%
19	211				180				116				105							0.75	0.85	$ 65.00		3%
20	211				180				116				105							0.75	0.85	$ 234.00		3%
21	211				180				116				105							0.75	0.85	$ 32.00		3%
Flat Rate Book

Cell Formulas
Range	Formula
T9:T21	T9	=SUM((AL9*AM9)+(AO9*AP9)+AQ9)+AK9
X9,X11:X21	X9	=SUM(T9*AN9)
AB9:AB21	AB9	=SUM(T9-AK9)
AF9:AF21	AF9	=SUM(AB9*90%)
X10	X10	=SUM(T10*AN9)
AP9	AP9	=+VLOOKUP(AO9,'Set-Up'!$B$24:$D$32,4)
AP10	AP10	=+VLOOKUP(AO9,'Set-Up'!$B$24:$D$32,3)
AP11	AP11	=+VLOOKUP(AO9,'Set-Up'!$B$24:$D$32,3)
AP12	AP12	=+VLOOKUP(AO9,'Set-Up'!$B$24:$D$32,3)
AP13	AP13	=+VLOOKUP(AO9,'Set-Up'!$B$24:$D$32,3)
AP14	AP14	=+VLOOKUP(AO9,'Set-Up'!$B$24:$D$32,3)
AP15	AP15	=+VLOOKUP(AO9,'Set-Up'!$B$24:$D$32,3)
AP16	AP16	=+VLOOKUP(AO9,'Set-Up'!$B$24:$D$32,3)
AP17	AP17	=+VLOOKUP(AO9,'Set-Up'!$B$24:$D$32,3)
AP18	AP18	=+VLOOKUP(AO9,'Set-Up'!$B$24:$D$32,3)
AN9	AN9	='Set-Up'!F45
AN10	AN10	='Set-Up'!F45
AN11	AN11	='Set-Up'!F45
AN12	AN12	='Set-Up'!F45
AN13	AN13	='Set-Up'!F45
AN14	AN14	='Set-Up'!F45
AN15	AN15	='Set-Up'!F45
AN16	AN16	='Set-Up'!F45
AN17	AN17	='Set-Up'!F45
AN18	AN18	='Set-Up'!F45
AN19	AN19	='Set-Up'!F45
AN20	AN20	='Set-Up'!F45
AN21	AN21	='Set-Up'!F45
AQ9	AQ9	='Set-Up'!F15
AQ10	AQ10	='Set-Up'!F15
AQ11	AQ11	='Set-Up'!F15
AQ12	AQ12	='Set-Up'!F15
AQ13	AQ13	='Set-Up'!F15
AQ14	AQ14	='Set-Up'!F15
AQ15	AQ15	='Set-Up'!F15
AQ16	AQ16	='Set-Up'!F15
AQ17	AQ17	='Set-Up'!F15
AQ18	AQ18	='Set-Up'!F15
AQ19	AQ19	='Set-Up'!F15
AQ20	AQ20	='Set-Up'!F15
AQ21	AQ21	='Set-Up'!F15

FRP-In Progress.xlsx
	A	B	C	D
24		0.00	25.00	5
25		25.01	50.00	4
26		50.01	100.00	3.5
27		100.01	200.00	3
28		200.01	350.00	2.5
29		350.01	500.00	2
30		500.05	1,000.00	1.5
31		1,001.01	1,500.00	1.25
32		1,500.01	200,000.00	1.075
33
Set-Up

 

[Sufiyan97]

Try

Book2
	T	U	V	W	X	Y	Z	AA	AB	AC	AD	AE	AF	AG	AH	AI	AJ	AK	AL	AM	AN	AO	AP	AQ	AR
6					Partner Pricing															Manually Enter	Auto Fills	Manually Enter	Auto Fills	Auto Fills
7	Standard Price								Secondary Repair				Secondary Rep. Partner $							Time To Complete	Partner Discount %	Materials Cost	Materials Markup	Warranty Reserve
8
9	#N/A				#N/A				#N/A				#N/A							1	0	8.5	#N/A	0
10	130				0				130				117							0.5	0	26	5	0
11	220				0				220				198							0.75	0	55	4	0
12	367.5				0				367.5				330.75							0.75	0	105	3.5	0
13	750				0				750				675							0.75	0	250	3	0
14	937.5				0				937.5				843.75							0.75	0	375	2.5	0
15	1800				0				1800				1620							0.75	0	1200	1.5	0
16	2000				0				2000				1800							0.75	0	1600	1.25	0
17	260				0				260				234							0.75	0	65	4	0
18	469				0				469				422.1							0.75	0	134	3.5	0
19	260				0				260				234							0.75	0	65	4	0
20	702				0				702				631.8							0.75	0	234	3	0
21	160				0				160				144							0.75	0	32	5	0
22
23
Flat Rate Book

Cell Formulas
Range	Formula
T9:T21	T9	=SUM((AL9*AM9)+(AO9*AP9)+AQ9)+AK9
X9,X11:X21	X9	=SUM(T9*AN9)
AB9:AB21	AB9	=SUM(T9-AK9)
AF9:AF21	AF9	=SUM(AB9*90%)
X10	X10	=SUM(T10*AN9)
AP9:AP21	AP9	=+VLOOKUP(AO9,'Set-Up'!$C$24:$D$32,2,1)
AQ9:AQ21	AQ9	='Set-Up'!E15
AN9:AN21	AN9	='Set-Up'!E45

 

[Peter_SSs]

A couple of things

The formula in AP 9 is returning a #REF! error because the 3rd argument in you VLOOKUP is 4. That means the lookup is trying to look in column 4 of B24:D32 in Set-Up. That of course is impossible since that range only has 3 columns.

If you look at all the other formulas in column AP, you will see that they all say VLOOKUP(AO9,....

That would indicate either
a) You manually copy/pasted the same formula individually to each cell (unlikely), or
b) You pasted the formula once and copied it down but your worksheet is set to Manual calculation rather than Automatic to the formulas did not adjust & calculate.

With that workbook open check File -> Options -> Formulas and make sure it is set to Automatic

Here it is working for me:
(Note too that I have removed the "+" at the start of the formula as that does nothing.

Jingles3X.xlsm
	AO	AP
7	Materials Cost	Materials Markup
8
9	8.5	5
10	26	4
11	55	3.5
12	105	3
13	250	2.5
14	375	2
15	1200	1.25
16	1600	1.075
17	65	3.5
18	134	3
19	65	3.5
20	234	2.5
21	32	4
Flat Rate Book

Cell Formulas
Range	Formula
AP9:AP21	AP9	=VLOOKUP(AO9,'Set-Up'!$B$24:$D$32,3)