# Excel - Lookup Value Greater and Less Then

Microsoft Office professional edition 20...
August 5, 2011 at 07:22:23
Specs: Windows 7, Pentium Dual Core / 2 gigs
 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.10How 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.

See More: Excel - Lookup Value Greater and Less Then

#1
August 5, 2011 at 19:48:17
 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.

Report •

#2
August 6, 2011 at 00:50:22
 ``` A B C 1 \$2.47 \$3.50 2 \$2.00 \$2.25 \$2.50 3 \$3.00 \$3.50 \$4.00 ```I 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 B2So 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.

Report •

#3
August 6, 2011 at 05:56:17
 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 V stands for Vertical while the H stands for Horizontal.The key thing to understand is the use of the 1 (or TRUE) as the range_lookup argument which is required since you are not looking for an exact match. (I like to use 1 instead 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.

Report •

Related Solutions