Complicated lookup

[FrigidDigit]

Hi all!

I have a summary sheet (1) with the following layout:

Mod01 Mod02 Modnn
Contractor 1 Date01 Date02 Datenn
Contractor 2 Date01 Date02 Datenn
Contractor n Date01 Date02 Datenn

On sheet 2 I have list of data
Sub Name File Name Mod # Mod Status Date

I need a lookup formula for sheet one that will lookup the date based on the contractor name and mod # on sheet 1, however if the status on sheet 2 is "Not submitted", I want to display "Pending" in the relevant cell on sheet 1.

I hope I have explained this clearly enough?

Any help would be much appreciated.

Thanks FD

 

[acw]

FD

This will need to be adjusted for errors and ranges but should get you started.

=IF(INDEX(Sheet2!$D$1:$D$9,SUMPRODUCT(--(Sheet2!$A$2:$A$9=Sheet1!$A2),--(Sheet2!$C$2:$C$9=Sheet1!B$1),ROW(Sheet2!$A$2:$A$9)),0)="Not Submitted","Pending",INDEX(Sheet2!$E$1:$E$9,SUMPRODUCT(--(Sheet2!$A$2:$A$9=Sheet1!$A2),--(Sheet2!$C$2:$C$9=Sheet1!B$1),ROW(Sheet2!$A$2:$A$9)),0))

Tony

 

[FrigidDigit]

Thanks Tony!

Appreciate the help!!

FD