Formula to return value from multiple sheets if volume matched
 

I have a worksheet with average unit prices for <=50k,
<=100k,<=200k,<=300k,<500k and am creating a cover worksheet which will pull
the relevant unit cost based on the volume entered.

EG, I have a volume of 150k so I want the formula to take the price from the
<=100k sheet. I originally did the formula for sum(if xxx) but it added
each unit price instead of looking for the correct volume price.

Tried this but have an error - anyone able to help me ?

=if(IF(E8<=50000,'=50k pricing sheet'!AB7,IF(E8=199999,'=100k pricing
sheet'!I7,IF(E8=299999,'=200k pricing sheet'!I7,IF(E8=499999,'=300k
pricing sheet'!I7,IF(E8=500000,'=500k pricing sheet'!AB7))))))

Formula to return value from multiple sheets if volume matched
 

As written you have one too many IFs and one extra) at the end

This will do the lookup.
=IF(E8<=50000,'=50k pricing sheet'!AB7,IF(E8=199999,'=100k pricing
sheet'!I7,IF(E8=299999,'=200k pricing sheet'!I7,IF(E8=499999,'=300k
pricing sheet'!I7,IF(E8=500000,'=500k pricing sheet'!AB7)))))

If you were trying to add another condition (like if e8=0 return 0) you need
to add it like this
=IF(e8=0,IF(E8<=50000,'=50k pricing sheet'!AB7,IF(E8=199999,'=100k
pricing sheet'!I7,IF(E8=299999,'=200k pricing
sheet'!I7,IF(E8=499999,'=300k pricing sheet'!I7,IF(E8=500000,'=500k
pricing sheet'!AB7))))),0)
--
If this helps, please remember to click yes.


"Julie HSV" wrote:

I have a worksheet with average unit prices for <=50k,
<=100k,<=200k,<=300k,<500k and am creating a cover worksheet which will pull
the relevant unit cost based on the volume entered.

EG, I have a volume of 150k so I want the formula to take the price from the
<=100k sheet. I originally did the formula for sum(if xxx) but it added
each unit price instead of looking for the correct volume price.

Tried this but have an error - anyone able to help me ?

=if(IF(E8<=50000,'=50k pricing sheet'!AB7,IF(E8=199999,'=100k pricing
sheet'!I7,IF(E8=299999,'=200k pricing sheet'!I7,IF(E8=499999,'=300k
pricing sheet'!I7,IF(E8=500000,'=500k pricing sheet'!AB7))))))

Formula to return value from multiple sheets if volume matched
 

You don't need the double If to start. Use:
=IF(E8<=50000,'=50k pricing sheet'!AB7,IF(E8=199999,'=100k pricing
sheet'!I7,IF(E8=299999,'=200k pricing sheet'!I7,IF(E8=499999,'=300k
pricing sheet'!I7,IF(E8=500000,'=500k pricing sheet'!AB7)))))

Also, you need to test for <= rather than =, because everything greater
than 199999 will be caught by the second test, and nothing between 50000 and
199999 will be found. Use:
=IF(E8<=50000,'=50k pricing sheet'!AB7,IF(E8<=199999,'=100k pricing
sheet'!I7,IF(E8<=299999,'=200k pricing sheet'!I7,IF(E8<=499999,'=300k
pricing sheet'!I7,IF(E8=500000,'=500k pricing sheet'!AB7)))))

Next, I would use < rather than <= in case you have a value like 199999.50:
=IF(E8<=50000,'=50k pricing sheet'!AB7,IF(E8<200000,'=100k pricing
sheet'!I7,IF(E8<300000,'=200k pricing sheet'!I7,IF(E8<500000,'=300k
pricing sheet'!I7,IF(E8=500000,'=500k pricing sheet'!AB7)))))

Next, you don't need the last If statement:
=IF(E8<=50000,'=50k pricing sheet'!AB7,IF(E8<200000,'=100k pricing
sheet'!I7,IF(E8<300000,'=200k pricing sheet'!I7,IF(E8<500000,'=300k
pricing sheet'!I7,'=500k pricing sheet'!AB7))))

Finally, I would put all your prices on one sheet, and use a Vlookup
instead. It's much easier to maintain.

Regards,
Fred




"Julie HSV" <Julie wrote in message
...
I have a worksheet with average unit prices for <=50k,
<=100k,<=200k,<=300k,<500k and am creating a cover worksheet which will
pull
the relevant unit cost based on the volume entered.

EG, I have a volume of 150k so I want the formula to take the price from
the
<=100k sheet. I originally did the formula for sum(if xxx) but it added
each unit price instead of looking for the correct volume price.

Tried this but have an error - anyone able to help me ?

=if(IF(E8<=50000,'=50k pricing sheet'!AB7,IF(E8=199999,'=100k pricing
sheet'!I7,IF(E8=299999,'=200k pricing sheet'!I7,IF(E8=499999,'=300k
pricing sheet'!I7,IF(E8=500000,'=500k pricing sheet'!AB7))))))