select a cells satisfying row and column criteria excel

[Amolvijay]

Hi,
I am trying to extract information from a table as follows from rate card below...

untitled.JPG

Now from next table in blue, required Manpower is illustrated...

Depending upon geography and Level, the revenue should be calculated for each month, table in Green..

Right now I did by manually selecting that particular rate from rate card and multiplying with no. of hrs and no. of manpower.

untitled1.JPG

Please help to have this process automated. That means if I change my team composition to Level 1 to level 3, or 2 the revenue table should reflect the same, without manual interven...

[FDibbins]

Hi and welcome to the forum

Please do not upload a picture of your file...rather, upload a sample of your workbook, showing what data you are working with, a few samples of your expected outcome is (manually entered is ok) and how you arrived at that. (exclude sensitive info). Pictures are pretty much impossible to edit, and no-one wants to re-type your data for you

[Amolvijay]

Hi, I am not sure how to upload excel...please guide...

[FDibbins]

To attach a file to your post,
click advanced (next to quick post),
scroll down until you see "manage file",
click that and select "add files" (top right corner).
click "select files" find your file, click "open" click "upload" click 'done" bottom right. click "submit reply"

Once the upload is completed the file name will appear below the input boxes in this window.
You can then close the window to return to the new post screen.

[Amolvijay]

Thanks a TON....file uploaded, kindly help with solution

[FDibbins]

Thanks for the file

Which table is the "base" table that you need to pull the data from?

[Amolvijay]

RATE is the worksheet where rates are defined. I am looking for auto changes in TOTAL worksheet when I change composition and mix mentioned in TOTAL.
So as of now for US geo case 1 I will need 2 Level 2 associates. The green table gives me the cost i will incure in doing so. No if I want to Level 2 to Level 3 or 1, the green table should be auto updated, no manual intervention

[FDibbins]

OK try this. 1st, add in the missing US in A3 and A9. then in the Green table, use this, copied down and across...

=INDEX(Rate!$A$2:$E$5,MATCH($B2,Rate!$B$2:$B$5,0),MATCH($A2,Rate!$A$2:$E$2,0))*C2

[Amolvijay]

Hi ...........Euekaaaaa......this is the one I am looking at...Thank thanks a ton....you saved my day...

[FDibbins]

you'r welcome. If this answered your question, please take a moment to mark the thread as "solved" - it helps keep things neat and tidy lol, and consider adding the reputation to those that helped (see points 2 & 3 below)