Formula to return value from multiple sheets if volume matched

[Julie HSV]

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

 

[Paul C]

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)

 

[Fred Smith]

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