column to be read for results to based on job type criteria

Dear Forum members
Any suggestions on the following would be greatly appreciated. I was looking to expand upon the concept discussed in my previous thread http://www.excelforum.com/excel-new-...ia-search.html
Please find attached file. In cell G1 there is a drop down to dicate what kind of job it is. What I'm looking for would be, when the user selects a Job type A in cell (G1), that Excel would populate (C3) based on values provided in colum (M), (E3) from column (N) and (F3) based on (O).
If the user selects Job type B in cell (G1), that Excel would populate (C3) based on values provided in colum (M), (E3) from column (P) and (F3) based on (Q).
If the user selects Job type C in cell (G1), that Excel would populate (C3) based on values provided in colum (M), (E3) from column (R) and (F3) based on (S).
Thank you.

If we are to assume that in addition to the above Size & Type are equally relevant to Type then perhaps:

Please Login or Register  to view this content.

The below is used to generate the appropriate summation range for use in the SUMIFS:

INDEX($N$3:$S$26,0,1+(E$2="install cost per unit")+2*(MATCH($G$1,{"A","B","C"})-1))

the above essentially references all possible result values (red)

states that irrespective of which column is to be used all rows (0) for whichever column is to be selected are to be returned (blue)

the final part (green) is used to determine which column to use from within the INDEX range

given this is the complex part - let's break this out also:

1+(E$2="install cost per unit")+2*(MATCH($G$1,{"A","B","C"})-1)

We know that the MIN column we will look at will be Column 1 / N (red)

We know that Type A commences in Column 1 (N), Type B in Column 3 (P) and Type C in Column 5 (R) ... ie each Type takes up 2 columns.

So if we're looking at Type A we'll add 0 to our "column offset", for Type B we want to add 2, Type C we want to add 4 - we can do this by conducting a MATCH and subtracting 1 from resulting value before * 2.
(eg MATCH of A will return 1, B will return 2 and C will return 3 - so we subtract 1 from this value before multiplying the result by 2 - 2 being the columns repeated per type)

The final part is to adjust for the fact that where we're looking for Install Cost per Unit we need to look at Columns 2, 4 & 6 rather than 1,3 & 5 - so we add 1 to the "column offset" calc. by virtue of a Boolean
(the act of addition will coerce the Boolean TRUE/FALSE to it's native Integer equivalent 1/0 respectively - eg 1+TRUE -> 1+1 -> 2).

So to elaborate by means of examples

Say G1 = TypeA and formula resident in Column E (cost per unit)

1+(FALSE)+2*(1-1) -> 1+0+0 -> 1

Say G1 = TypeC and formula resident in Column F (Install cost per unit)

1+(TRUE)+2*(3-1) -> 1+1+4 -> 6

This number is then used as column identifier in the INDEX range - so if 1 we would use Column N, if 6 we would use Column S


This all seems a little convoluted of course - it was just designed such that it could be copied across the matrix without need for alteration.