Lookup based on two criteria

Hi there

I have a template P&L in which I have a hidden column with a unique code for each line item that appears. What I need to do is, pull the actual value for each unique line item from a separate Budget file, which is simple if you use indirect coupled with vlookup or sumif. However, I want all of the rows and columns from Jan10-Dec10 to act as a range.

On the P&L template, in Column G9, I have the current month (Jan10). I want to be able to update this so that it’ll automatically pull through the corresponding unique figure based on the month I input into G9.

I think an array formula might be necessary? Below is a copy of the budget file:

Jan10 Feb-10 Mar10 Apr10
Unique Identifier
41AA121130100 10 11 15 18
41AA121130150 10 11 15 18
41AA121130605 10 11 15 18
41AA121130700 10 11 15 18
41AA121130810 10 11 15 18
41AA121130820 10 11 15 18
41AA121130821 10 11 15 18


Would anyone please be able to help me? I'd really appreciate it.

Cheers,
Darren

I've updated it to reflect my situation, but it's working perfectly. Thank you so very much.

I was expecting to have the Budget file open, in order to propagate the updates, however using your Index/Match formula below, this is no longer necessary.

I really appreciate your help. Thank you.

Best wishes,
Darren

Yes, of course! I apologise, my bad :-)

Hi again,

I have one last issue, which I was hoping you could please assist with before I am able to conclude my work on this spreadsheet.

I need to add in an additional column which calculates the year to date. That is, for example, if I set the unique code to May-10, it'll give me the sum of Jan-May for each line item.

Is there any way of doing this, other than by creating a dedicated YTD column in the Budget file, which I manually update to include the latest month?

Thanks again.

Cheers,
Darren

Very useful.

Also, I've built the ROW match into a single cell as per your instruction.

I appreciate your help on this. Thanks again.