unnecessarily long range

[sallan]

I have a sheet to represent price of an item by volume. It involves the use of a massive range. Is there any way I can achieve the same without using a range?

Sam

 

[NARAYANK991]

Hi Sam ,

The output in D3 appears to be a lookup of the data entered in B3 into the column E ; the problem is that the values in column E are the result of another calculation which appears to be quite complicated , since for a lot of input values the output remains the same.

Replacing all of this calculation by a formula will probably make the formula massive and undecipherable.

Narayan

 

[Luke M]

If you still want the formula, on the first worksheet, in D3, it would be:
=IFERROR(MAX(SUMPRODUCT((A3>='Data range'!$A$2:$A$9)*(A3<='Data range'!$B$2:$B$9)*('Data range'!$C$2:$C$9))*A3,('Data range'!$B$2:$B$9<A3)*('Data range'!$B$2:$B$9)*('Data range'!$C$2:$C$9)),"")

This would remove the need for the table in "Data range" columns E:H.