conditional lookup

[JimVarney01]

I need to find a value in a column based on the column's heading and a certain number a couple columns away. This info is not static, so I need a formula robust enough to always look for the column with 'PCB' as the header, and a particular number in the PCB column. Then it needs to find the total number of PCBs scheduled in a column that has a date heading.



Attached is a picture with an example of what I'm talking about. Currently I'm using a formula that will always look in column G for the number and then lookup G+2 for the amount.

Here is the formula I'm using now. $A4 is the PCB number in the spreadsheet that this formula is in.
=SUMIF('[LocalFile.xls]102-1'!$G:$G,$A4,'[LocalFile.xls]102-1'!$K:$K)

Looking for robustness!!
Thank you!!


I apologize for all of the censored info
Kurtis

[Domenic]

Is this what you're looking for?

=SUMIF(INDEX('[LocalFile.xls]102-1'!A6:J100,0,MATCH("PCB",'[LocalFile.xls]102-1'!A3:J3,0)),$A4,'[LocalFile.xls]102-1'!K6:K100)

OR

=SUMPRODUCT((INDEX('[LocalFile.xls]102-1'!A6:J100,0,MATCH("PCB",'[LocalFile.xls]102-1'!A3:J3,0))=$A4)*'[LocalFile.xls]102-1'!K6:M100*('[LocalFile.xls]102-1'!K3:M3=$B4))

...where B4 contains the date of interest.