V-Lookup with column index that can search for a specific value

[rhys_j08]

Hi,

I am trying to create a summary sheet which takes the single cost value from each tab in my workbook. I am using a vlookup formula combined with indirect to allow me to get the value from each tab (=VLOOKUP("P-50 ESTIMATE",INDIRECT("'"&$D8&"'!$C$1:$U$200"),7,FALSE) but the problem with this formula is that the cost is not always in the same column.

I need a formula that will allow me to find the Row based on "P-50 ESTIMATE" (currently using VLOOKUP) and then find the column IN THAT P-50 ESTIMATE ROW that contains "kUSD", return the value in cell 1+"kUSD". Is this possible?

I have attached an example sheet with how my tabs may look (unable to attach actual cost sheets for obvious reasons).

[mehmetcik]

I suspect that the summary sheet lists all the final cost from the previous sheets

So Sheet1 =MAX(Sheet1!1:1048576)
Sheet2 =MAX(Sheet2!1:1048576)

[rhys_j08]

This is very smart thinking and something i did not think of, but unfortunately it will not work as the final costs are related to a variety of variables such as hours, materials, footprint, etc. of which some have a greater value than the final cost.