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.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.


See More: Excel - Lookup Value Greater and Less Then

Report •

#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 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.


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


Ask Question