# If = between range than * by this.

#### Ashman88

##### New Member
Hi,

I am trying to setup a spreadsheet for costings on weights.

Users will enter in weight in cell 1 on sheet 1 and cost will appear in cell 2 on sheet 1. But different weight ranges are equal to different value. I have create a separate sheet (2) for the weight prices as these need to be changed form time to time.

Example below

When users enters in value to cell 1 it needs to look up range in other sheet, check what the number is bewteen than multiply cell 1 by corresponding price and show result in cell 2.

Sheet 2 Example

 a b c 1 Start Finish Price 2 1 300 .5 3 301 500 .4 4 501 800 .2 5 800 .1

<tbody>
</tbody>

Where 800 is in A5 this will be anything above 800

Thanks,
Ashman

### Excel Facts

Copy a format multiple times
Select a formatted range. Double-click the Format Painter (left side of Home tab). You can paste formatting multiple times. Esc to stop

#### vds1

##### Well-known Member
Try,

=LOOKUP(D2,Sheet1!A2:A5,Sheet1!C2:C5)

where D2 is the cell where user enters the value and sheet1 is the lookup sheet.

Sheet1!A2:A5 is the start
Sheet1!C2:C5 is the price

Note that A2 to A5 in lookup sheet should be in order.

#### RCBricker

##### Well-known Member
This will work with the example you gave (change sheet names to what you need). Also you had two instances for weight 800, following your logic I made the last entry in column A 801 rather than a second instance of 800.

=IF(AND(A2>=Sheet142!\$A\$2,A2<=Sheet142!\$B\$2),Sheet142!\$C\$2,IF(AND(A2>=Sheet142!\$A\$3,A2<=Sheet142!\$B\$3),Sheet142!\$C\$3,IF(AND(A2>=Sheet142!\$A\$4,A2<=Sheet142!\$B\$4),Sheet142!\$C\$4,Sheet142!\$C\$5)))

This assumes a separate column for the total weight * Price

If (as you stated) you wanted the calculated value in cell B2 on down use this:

=IF(AND(A11>=Sheet142!\$A\$2,A11<=Sheet142!\$B\$2),Sheet142!\$C\$2*A11,IF(AND(A11>=Sheet142!\$A\$3,A11<=Sheet142!\$B\$3),Sheet142!\$C\$3*A11,IF(AND(A11>=Sheet142!\$A\$4,A11<=Sheet142!\$B\$4),Sheet142!\$C\$4*A11,Sheet142!\$C\$5*A11)))

Last edited:

#### Ashman88

##### New Member
Hi vds1,

This worked perfectly. I did have to modify to this =LOOKUP(G6,'Sheet1'!\$D\$4:\$D\$8,(G6*'sheet1'!\$F\$4:\$F\$8)

Is there anyway to put in a isblank or to have the cell2 show as 0 if cell 1 has no data. At the moment it shows #N/A. Later i will need to create rows of the formula above with a SUM at the bottom of cell 2

#### vds1

##### Well-known Member
Perhaps you can try IFERROR,

=IFERROR(LOOKUP(G6,'Sheet1'!\$D\$4:\$D\$8,(G6*'sheet1'!\$F\$4:\$F\$8),"")

#### Ashman88

##### New Member
Thanks VDS1. That works perfectly.