I need to input a sales price based off a range of raw material prices in cell O22. In I23:O23, I have raw material value while in I24:024, I have the sales price based off the raw material value. So basically, if the raw material (A1) is less than $3.50 (O23) but higher than or equal to $3.25 (N23), the sales price is 5.00 (N24) Everything is row 23 goes down in 0.25 increments while row 24 goes down by 0.10

How exactly would I create a formula for this, especially since my range goes from 2.00 - 3.50. I figured it would an IF function but I can't figure it out. Any help would be appreciated.

I'm confused by your description of the layout of your spreadsheet. Please click on the following line, read the How To it links you to and then post a sample of your data and an explanation of what you are trying to do as it relates to the data posted. Thanks.

Click Here Before Posting Data or VBA Code ---> How To Post Data or Code.

A B C 1 $2.47 $3.50 2 $2.00 $2.25 $2.50 3 $3.00 $3.50 $4.00I apologize for being unclear before. Row 2 and 3 are assumptions while A1 is an input and B1 is the output. Basically row 2 is the range of raw material prices while row 3 is the sales price depending on how much the raw material is. I want users to input the current raw material price into A1, based off that, the sales price should show up in B2

So if a user says the raw material is 2.47 in A1, B1 should say 3.50 since 2.47 is less than 2.50 and higher than or equal to 2.25. I thought of doing an IF function but couldn't figure out how to do 2 criteria.

Read the Excel Help files on HLOOKUP to help you understand how this formula works. While VLOOKUP is more commonly used than HLOOKUP they both basically perform the same function. The

Vstands for Vertical while theHstands for Horizontal.The key thing to understand is the use of the

1(or TRUE) as therange_lookupargument which is required since you are not looking for an exact match. (I like to use1instead of TRUE simply because it's less typing.)=HLOOKUP(A1, $A$2:$C$3, 2, 1)

=HLOOKUP(A1, $A$2:$C$3, 2, TRUE)

Click Here Before Posting Data or VBA Code ---> How To Post Data or Code.

Ask Your Question

Weekly Poll