Using VLOOKUP to return value from price matrix

[motoko]

I'm trying in Excel to create a lookup to return a cost from a matrix based on three values: say type, width and length. The cost values are in bands depending on width and length.
I have a separate worksheet for each product type, then a matrix on each sheet with lengths and widths along each axis together with the costs.
Anybody got any pointers as to the best way to achieve the lookup?

 

[xlbo]

Yup - use named ranges for each of your product sheets
You'd be better off having a layout for length and width like:
Length Width L&W Cost
1 1 11 0.12
1 2 12 0.24
2 1 21 0.17

etc etc where L&W is a concatenated field (hidden if u need it to be) of the length & width
Then your vlookup becomes:
=vlookup(CellRef,NamedRangeForProduct,2,false)
where the cellref is a cell that concatenates the user entry for length & width

Hope this makes sense Rgds
~Geoff~

 

[motoko]

Thanks for that, that will return a value if it is an exact match but the lengths and widths are banded as well as the costs (I should have explained that better) e.g.

width to100 101to150 151to200
length
to100 143 143 147
101to150 149 152 154
151to200 153 156 158

so if the dimensions are within certain ranges it will return the appropriate value.
I hope this is a bit clearer.
Regards
Drew

 

[xlbo]

You are gonna have problems - whilst I'm not saying it's impossible to do, I would strongly suggest that you have a value cost for every length and width, otherwise, you're going to need a set of other calcs before you do the lookup, just to determine which banding it's in. At the very leaset, you'll need a full list of lengths and widths, matched up against the bandings (unless you want to go down the route of testing the entered value against the value of part of the header string)
Rgds
~Geoff~

 

[rmikesmith]

Drew,

Depending on what's known in terms of the allowed bands or ranges at "design time" versus "run time" will determine how difficult doing what you want will be. I've put together a workbook that demonstrates the cost lookup you require, with the assumption that the ranges for length and width are known at design time. This simplifies things greatly. If you are interested, please post your email address and I'll gladly send it to you.

Regards,
Mike

 

[motoko]

Hi Mike,
Thanks for that, I do know all variables so that would be a great help.

email is motoko6969@yahoo.com

regards,

drew