Look-up formula to reference data in another sheet

Nichole Pyle

I am looking for some help with a formula that would look up a resource name and pull their total departmental allocation for a given month into a another sheet. Here are screen shots of both sheets - the one on the left is where I want it to find the highlighted # and drop it into the cell on the sheet shown to the right.

Any help would be appreciated.

Thank you

David Joyeuse

Hi Nichole,

Got it for you as I've just tried it on testing sheets. Your formula for the sheet on the right is this one:

=INDEX({Full Year Range}, MATCH([Resource Name]@row, {Resource Name - Range}, 0), MATCH([Month & Year]@row, {Month & Year Range}, 0))

The full year range is all the columns as in the picture above that goes from January up to December.

Make sure the Month & Year range, is just the first line of your left sheet as in the picture above. Also make sure they are labeled the same way between the 2 sheets. As in your example I noticed you wrote 09/1/2020 on one sheet and 9/1/2020 on the other. If they aren't written the same way it won't work.

Last point is to make sure that the TOTAL line you're looking for each resource is the first line in your department category because that's what we're returning. We are not looking for total here, but just the first line. If you keep it this way, this will work just fine.

Results in my testing:

David Joyeuse

Hi Nichole,

If I'm reading you and understanding your pix rightly, then the numbers you want to display in the "Departmental Allocation" of your right sheet is the total number of the resource on the line depending on the month & year?

I'm asking because Watching your pix:

• John Smith has a 45 Total deparmental allocation on 9/1/2020, yet your pix on the right shows nothing.
• Jane Doe has a 45 Total deparmental allocation on 10/1/2020, yet your pix on the right shows 10.

If all you want to do is watch the total departmental allocation of each resource, I would rather suggest to use a report where you only display the "total" lines.

Nichole Pyle

Unfortunately this is for a feed into Power Bi so a report won't do the trick :-( I'm wanting the the numbers from the left to populate to the sheet on the right so that the sheet on the right can be used for the feed. Does that make sense?

David Joyeuse

Hi Nichole,

Got it for you as I've just tried it on testing sheets. Your formula for the sheet on the right is this one:

=INDEX({Full Year Range}, MATCH([Resource Name]@row, {Resource Name - Range}, 0), MATCH([Month & Year]@row, {Month & Year Range}, 0))

The full year range is all the columns as in the picture above that goes from January up to December.

Make sure the Month & Year range, is just the first line of your left sheet as in the picture above. Also make sure they are labeled the same way between the 2 sheets. As in your example I noticed you wrote 09/1/2020 on one sheet and 9/1/2020 on the other. If they aren't written the same way it won't work.

Last point is to make sure that the TOTAL line you're looking for each resource is the first line in your department category because that's what we're returning. We are not looking for total here, but just the first line. If you keep it this way, this will work just fine.

Results in my testing: