Problems With an "If" Formula

[Jingles3X]

Hi. I recently lost my job, and am returning to the world of Self Employed, in a service business.
As part of setting this business up, I am creating a flat rate pricing system so that I can quickly
and easily update my pricing, while giving a visual aid for selling from the customer standpoint.

One of the components of the pricing system is the "Materials Multiplier"... If The part costs between
X and Y then multiply it by Z.

I have been trying for several days to figure this out, with no luck.
I can use this formula: "=IF(AND(AJ27>B27,AJ27<P27),AB27)" for a single line, but that only
helps if ALL parts fall within $0 and $10. I am uploading an image of the table that I have created.

Can anyone advise me as to how to have a cell evaluate the cost of a part against each line in this table,
then populate my materials column for each individual repair with the appropriate multiplier?

Thank you.

 

[Eric W]

I assume you're having problems with the VLOOKUP formula? This is what you have:

=VLOOKUP(AO9,'Set-Up'!C24:D32,3)

The 3 parameters in VLOOKUP are the value you're looking up (AO9), the table you're looking in ('Set-Up'!C24:D32) and the column to get the results from the table (3). Your table has only 2 columns (C and D) and you're asking for column 3, which is why you're getting the error. I assume that you set the table up like in post #9 with the 3 columns.

 

[Jingles3X]

I assume you're having problems with the VLOOKUP formula? This is what you have:

=VLOOKUP(AO9,'Set-Up'!C24:D32,3)

The 3 parameters in VLOOKUP are the value you're looking up (AO9), the table you're looking in ('Set-Up'!C24:D32) and the column to get the results from the table (3). Your table has only 2 columns (C and D) and you're asking for column 3, which is why you're getting the error. I assume that you set the table up like in post #9 with the 3 columns.

Hi. Yes, the table is set up with 3 columns. I did in-merge cells also

 

[Eric W]

Did you change the formula to match the table? Something like:

=VLOOKUP(AO9,'Set-Up'!C24:E32,3)

You showed several sheets, but not the Set-up sheet, so I don't know for sure what the address should be.

 

[Jingles3X]

Did you change the formula to match the table? Something like:

=VLOOKUP(AO9,'Set-Up'!C24:E32,3)

You showed several sheets, but not the Set-up sheet, so I don't know for sure what the address should be.

Yes, that is the exact and correct formula actually. I do believe i have it entered properly (willing to attach the entire workbook, just not sure it it is allowed).
Still receiving the same error


Here is the setup sheet.

FRP-In Progress.xlsx
	A	B	C	D	E	F
1
2
3
4
5		Flat Rate Pricing Set-Up
6
7
8
9		Labor Items
10
11		Diangnostic Charge/Service Call Fee				$ 95.00
12
13		Labor Rate Auto Populated From Labor Calculator				$ 154.88
14
15		Warranty Reserve % to cover return trips, call backs, etc…				3%
16
17		Materials Markup
18
19
20		Please Enter A Number that will be used as a materials multiplier for each of the ranges below
21
22
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

Cell Formulas
Range	Formula
F13	F13	='Labor Calculator'!R23

 

[Jingles3X]

Did you change the formula to match the table? Something like:

=VLOOKUP(AO9,'Set-Up'!C24:E32,3)

You showed several sheets, but not the Set-up sheet, so I don't know for sure what the address should be.

I am wondering if this is the problem....

 

[Eric W]

You can't upload your spreadsheet directly, but you can upload it to a file sharing site, and post a link to it. The problem with that is some people can't or won't download files from the internet for security reasons. My work won't allow it for example. If you want to do that, I'd recommend opening a new thread so more people will see it. One of them might be willing to download the file to look at it. I'm afraid I'm stuck. With what I can see, I don't know why your VLOOKUP isn't working.