Don't know how to create formula

[alchavar]

hello,

If anyone can help me do this.

I have this file enclosed. It has 2 spreadsheets.

One is named "Forecast". It contains a list of people assigned to 10 different projects with their forecasted % utilzation or allocation.

The other spreadsheet is named "Actual". It contains the actual time booked by each person on a given project. I need a formula (maybe using INDEX and MATCH) that could "import" the % Actual Utilization onto the "Forecast" spreadsheet from the "Actual" spreadsheet, and I don't know how to do it.

[attach]Book2.xlsx[/attach]

[AliGW]

The workbook is not attached.

Unfortunately the attachment icon doesn't work at the moment, so to attach an Excel file you have to do the following: just before posting, scroll down to Go Advanced and then scroll down to Manage Attachments. Now follow the instructions at the top of that screen.

[alchavar]

sorry. I missed to add the attachment. Here you go.

[AliGW]

In D2 copied down:

=INDEX(Table1[% Actual Utilization],MATCH([@Resource],Table1[Resource],0),0)

[alchavar]

Thank you, AliGW, but there's a problem with your formula: it is replicating the % of Actual Utilization across of all projects for a same person, but each person ended up working in only one project according to Spreadhseet "Actual". So I need the formula to return a "0" in column D if the person in column A didn't book time against a given project. See on column F an example on how results should be.

[AliGW]

OK. Try this instead:

=IFNA(INDEX(Table1[% Actual Utilization],MATCH(1,([@Resource]=Table1[Resource])*([@Project]=Table1[Project]),0)),0)

confirmed by pressing CTRL+SHIFT+ENTER to activate the array, not just ENTER. You will know the array is active when you see curly braces { } appear around your formula. If you do not CTRL+SHIFT+ENTER you will get an error or a clearly incorrect answer.

[JBeaucaire]

Use this then:

=IFERROR(INDEX(Table1[% Actual Utilization], MATCH($A2&"-"&$B2, INDEX(Table1[Resource]&"-"&Table1[Project],0),0)),0)

[bebo021999]

How about this:

Please Login or Register  to view this content.

for Excel 2003:

Please Login or Register  to view this content.

[alchavar]

Thank both. I worked.

[AliGW]

There were three solutions offered: mine, Jerry's and Bebo's. So you have three ways to skin this cat.

Please mark the thread as solved using the thread tools at the top.