Estimates with multiple look ups

[Sri Harsha]

Hi,
I am preparing an estimation template. I have a source table which has the
estimated hours of effort and a table which gives options to select the
variables which gives the output.

Please assist me in finding a forumla for multiple variables. The last
column of Estimates table will have the formula. The table has all the
variables possible.

Ex: If i have Release notes as deliverable, and of low complexity with 5
pages, the result should be 5*4 = 20 Hrs.

Estimates table:
S.No Deliverable Complexity of FD No. of Pages
1 Release Notes Low 5
2 User Guides Medium 4
3 Dictionary Medium 5
4 File Layouts High 6


Source Table for Estimates.

FD Complexity RN UG Dictionary File Layout
Low 4 3 3 8
Medium 6 6 5 8
High 8 8 8 8

 

[Jacob Skaria]

Use MATCH function to get the row and column.
Get the right value using the INDEX function

A15 is the estimation table

In Cell E2 enter the below formula. D2 is 5

=D2*INDEX($A$9:$E$12,MATCH(C2,$A$9:$A$12,1),MATCH(B2,$A$9:$E$9,1))

$A$9:$E$12 is the Source of Estimates
$A$9:$A$12 is the FD Complexity
$A$9:$E$9 is the source header (Please make sure to use the same wordings)

If this post helps click Yes