Lookup function in a dynamic report

Hello Wise ones,

Its been so valuable browsing the threads on the forums lately!

However, I have run into a brick wall with a lookup today...

I'm running an XLOne report which incorporates several data sources. I wasn't able to combine datasources in order to have a one to one relationship with waht I want to do. So I have to do incorporate a lookup which inserts an amount from another part of the report (after the dynamic report has been run).

So, I run the report, it utilises a VLOOKUP to select a project code in one section of the report and jumps down to where the second report starts....then across 10 columns.... and down to the SUBTOTALS for the sum for that project.

e.g. VG07001 has 2 line amounts then a subtotal. Projects have either one or two amount lines then the subtotal following.

I've tried VLOOKUP but it doesnt know how far to go down to get the SUBTOTAL amount (relative place on the page after running the report).

=IF(ISERROR(VLOOKUP(C80,C107:V134,20,FALSE)=TRUE),0,(VLOOKUP(C80,C107:V134,20,4)))

I tried a 2 way lookup using Match() and*Index() but havent got very far. Can I ask it to look at the SUBTOTALS itself?

I'm wandering if anything is actually possible? Any suggestions?

Your advice is much appreciated!

Kind regards,

Muchado77

Thanks,

Please find attached,

There are two reports - Report 1 and Report 2 (in the same worksheet, labelled in green headings)

I'm using vlookup at V63. I'm looking up PROJECT CODE in Column I (report 1) and matching it with Column I (report 2) to find the amount in column V (Report 2).

However, I want the SUBTOTAL not the first or second line amount. A project in Report 2 can have up to 3 line amounts but I only want the subtotal (sum of these) which is the last line (sometimes this is 2 sometimes 3 sometimes 4)

I have the VLOOKUP in the command lines of a second worksheet (which check the criteria I want to produce the report) I havent attached it because Im not sure if its useful.

The vlookup is utilised when the report is run which populates each project line that appears in the report. If I could use the lookup AFTER I ran the report - it would be easier - but I need the formula to work at runtime.

Is there a way to get vlookup to know when it needs to go down one or two lines appropriately?

Kindest regards,

Muchado77

If your subtotals are always the largest amount eg you can not have negative figures in your column out the following in cell v63

=MAX(($I$108:$I$133=I63)*($V$108:$V$133))

and enter it as an array holding down shift and ctrl when you press enter after typing it

Does that work

Regards

Dav