Complicated lookup

FrigidDigit

Board Regular
Joined
May 10, 2005
Messages
203
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
 

Some videos you may like

Excel Facts

Copy formula down without changing references
If you have =SUM(F2:F49) in F50; type Alt+' in F51 to copy =SUM(F2:F49) to F51, leaving the formula in edit mode. Change SUM to COUNT.

acw

MrExcel MVP
Joined
Feb 13, 2004
Messages
4,814
FD

This will need to be adjusted for errors and ranges but should get you started.
Code:
=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
 

Watch MrExcel Video

Forum statistics

Threads
1,118,534
Messages
5,572,738
Members
412,482
Latest member
arooshrana2
Top