Complex lookup array with 4 variables

[Ronan]

Hi

Can you help?

I have a complex array of values and want to return an answer based on
certain selections. The array allows the user to calculate the capacity of a
road based on road class (two way or dual carriageway), number of lanes (2,
3, 4, 5, 6, etc), road type (A, B, C, D, E), and road width (6.1m, 6.75m,
7.3m, 9.0m, 10.0m, etc). So for example, a two way road could be type B or C
or D, then there could be any number of lanes between 2 and 5, and then any
road width between 6.1 and 9.0. So the possible value choices become less and
less as more choices are made.

So im trying to use index and match but doesnt seem to like the fact that
some choices are in rows and some are in columns. Would it be possible to use
VBA in any way. Im already using combo boxes to choose the input values.

I hope this makes sense, it is difficult to include the array itself as the
formatting becomes skewed when I paste it into this message.

Thanks for your help

Two-way Dual Carriageway

2 2 2 2 2-3 3 3-4 4 4+ 2 2 3 4
Carriageway 6.1m 6.75m 7.3m 9.0m
Width 10.0m 12.3m 13.5m 14.6m 18.0m 6.75m 7.3m 11.0m 14.6m

Road type UM -- -- -- -- -- -- -- -- -- -- 4000 5600 7200
UAP1 1020 1320 1590 1860 2010 2550 2800 3050 3300 3350 3600 5200 --
UAP2 1020 1260 1470 1550 1650 1700 1900 2100 2700 2950 3200 4800 --
UAP3 900 1110 1300 1530 1620 -- -- -- -- 2300 2600 3300 --
UAP4 750 900 1140 1320 1410 -- -- -- -- -- -- -- --

 

[Gary''s Student]

If you re-structure your data so the criteria can be applied to columns only,
then you can use AutoFilter. You can set separate criteria for each column
and Excel will display only those rows meeting the criteria.

 

[Ronan]

Do you know if it is possible to put that into a formula to return the
appropriate answer based on the choices or can it only be done using
autofilter?

 

[Gary''s Student]

Filtering can be done completely with formulae.
It is a bit complex, but can be done.

 

[Herbert Seidenberg]

Excel 2007
Tables
Easy formula, some convenience code:
http://www.mediafire.com/?sharekey=8756bfaf09e707abd2db6fb9a8902bda