How to look up data by a heading and then a subheading that are the same.

**PastaJ** · 07-09-2010, 05:37 PM

I need to look up specific data based on a format like:

Project Number 1 (in column A)
Transportation (in column B) $500 (in column C)
Structural $50
Water $250

Project Number 2 (in column A)
Transportation (in Column B) $75 (in column C)
Structural $100
Water $250....etc.

So I first need to lookup the project number and then lookup transportation or Structural or Water and return the dollar value. This needs to be done using a lookup or something because each month a new report is generated and the cell location of Project Number 1..2..etc. will change from month to month so I don't have a fixed cell location for any of the data.

Any ideas how to look this data up?

Thank you.

**DonkeyOte** · 07-10-2010, 01:54 AM

I would suggest posting a sample file that illustrates the layout...

If (as inferred) the subheadings are consistent for all "tables" (ie all have Transportation, Structural, Water) then in reality it would simply be a case of:

=INDEX(Sheet2!C:C,MATCH(A1,Sheet2!A:A,0)+MATCH(B1,{"Transportation","Structural","Water"},0)-1)

where A1:B1 contains heading & sub heading of interest (respectively)

In reality it may not be this trivial, in which case to reiterate, post a sample file which accurately reflects setup and requirements.

**PastaJ** · 07-15-2010, 10:17 AM

OK here are the files. I want to find the dollar amount projected for transportation or Land Development or...etc. for the month for a specific project from the "File I want to get data from.xls" and place that data in the "File I want to place the data in.xls" The difficulty is the "File I want to get data from.xls" changes each month as new projects are added to the list, so I do not have a fixed cell location. Right now I am hand entering the information from the one file to the other file. I would love to automate this so it will just look up the information each month as I replace the "File I want to get data from.xls" I hope these files make it more clear.

Thanks so much for the help!

**PastaJ** · 07-21-2010, 10:20 AM

OK, I'm getting closer to the answer I need. Here is what I have so far.

=INDEX('[CMRP10-07.xls]Clutter'!$E:$E,MATCH("*: 1000-5280.00*",'[CMRP10-07.xls]Clutter'!$B:$B,0)+8,0)

The issue I'm having is still in the subheading area. I have found that the subheadings are not consistant. I'll have a project that has 4 subheadings and others with just two. It depends upon if accounting placed a budget in the subheading or not. If they did, the subheading will show up, if they did not the subheading will not show up in the spreadsheet. So you will have:

1000-5000.09
Transportation
Structural
Water

1000-5020.00
Land Development
Transportation
Water

1000-5025.00
Land Development
Water

etc.

So the order of the subheadings is consistant, but on each project you will only have the subheadings appear if accounting has placed a budget in that item. That is why in my equation I have a +8, (in red) because I do not know how to make it look up the location of the subheading since I do not know how many subheadings there will be for any given project number. So it is almost automatic, I just have to make sure from month to month that accounting did not add a subheading to a project since the +8 is hard code with no intelegence! So if I did as suggested:

=INDEX(Sheet2!C:C,MATCH(A1,Sheet2!A:A,0)+MATCH(B1,{"Transportation","Structural","Water"},0)-1)

I will get the wrong number to add, because the array {"Transportation","Structural","Water"} is not consistant between all the projects. The order is consistant, but on any given project one might have just Transportation, or Transportation and Water...etc.

Any ideas on how to solve this?

Thanks again for the help!

**PastaJ** · 07-22-2010, 04:17 PM

I Solved it!!! Here is what I came up with:

=INDEX('[CMRP10-07.xls]Clutter'!$E:$E,MATCH("*: 1000-5000.19*",'[CMRP10-07.xls]Clutter'!$B:$B,0)+MATCH("*Total for Water*",INDIRECT("'[CMRP10-07.xls]Clutter'!"&"C"&MATCH("*: 1000-5000.19*",'[CMRP10-07.xls]Clutter'!$B:$B,0)+1&":"&"C"&MATCH("*: 1000-5000.19*",'[CMRP10-07.xls]Clutter'!$B:$B,0)+12),0),0)

I know this is a long string. Is there anyway any of you see to shorten this equation up?

Thanks for the help!