Formula with double IF statement

[Jennsy]

Hi,

I would like some help with a formula where I am trying to get a date into a cell based on 3 criteria being met. I have attached the spreadsheet I am using.

THE GOAL
I would like Column L in UserByOpp to show the date that a certain widget was uploaded. The date the widget was uploaded is in the Resources worksheet. The two worksheets are linked by the Record Name (Column B in Resources and Column E in UserByOpp).

The formula I need in Column L in UserByOpp is:

Give me the date (Column K in Resources) where:

- Column B in Resources = Column E in UserByOpp, AND
- Column G in Resources = ABCWidget, AND
- Column J in Resources = Uploaded

Ideally I would like it so the formula runs automatically, using IF statements.

Thank you so much in advance for any help.

Jennsy

[brocktoon93]

Try this in column L, see if it is what you need...

=IF(VLOOKUP([@[Record Name]],Resources!B:G,6,FALSE)="ABCWidget",IF(VLOOKUP([@[Record Name]],Resources!B:J,9,FALSE)="uploaded",VLOOKUP([@[Record Name]],Resources!B:K,10,FALSE)," ")," ")

[AlKey]

Maybe like this

=IF(AND(VLOOKUP([@[Record Name]],Resources!B:G,6,0)="ABCWidget",VLOOKUP([@[Record Name]],Resources!B:J,9,0)="uploaded"),VLOOKUP([@[Record Name]],Resources!B:K,10,),"")

[Jennsy]

Hi brocktoon93,

Thanks for your help, much appreciated, the formula works great!

However, I have now plugged that in to my real data and an issue has become apparent. It is likely that a Record Name will be in the Resources worksheet multiple times. If this is the case the formula misses the date for the ABCWidget unless that is the first one in Resources worksheet. I have uploaded the example of this, you will see that in UserByOpp the Record 3456aaa has no ABC Widget Uploaded date against it, but it should as it is in the Resources worksheet. Is there a way we can make sure this gets picked up?

ABC_SpecialReport2.xlsx

PS I added an ISERROR to the formula to remove #N/A results, as it is also possible that a record name does not exist in the Resources worksheet.

Thanks,
Jennsy

[Jennsy]

Hi AlKey,

That formula works too, thanks for your help, much appreciated.

But please see my note below, as I have encountered an issue with the real data.

====
Hi,

Thanks for your help, much appreciated, the formula works great!

However, I have now plugged that in to my real data and an issue has become apparent. It is likely that a Record Name will be in the Resources worksheet multiple times. If this is the case the formula misses the date for the ABCWidget unless that is the first one in Resources worksheet. I have uploaded the example of this, you will see that in UserByOpp the Record 3456aaa has no ABC Widget Uploaded date against it, but it should as it is in the Resources worksheet. Is there a way we can make sure this gets picked up?

ABC_SpecialReport2.xlsx

PS I added an ISERROR to the formula to remove #N/A results, as it is also possible that a record name does not exist in the Resources worksheet.

Thanks,
Jennsy

[AlKey]

Try this

Since you are using Excel 2007, you don't need to use ISERROR. You can now use a more efficient function such as IFERROR

Also, I have noticed that you using " " to show blank cell. Please note that this adds space in the cell. Instead use "".

=IFERROR(INDEX(Table2[Date (PST)],MATCH(1,INDEX((Table2[Widget]="ABCWidget")*(Table2[Record Name]=Table3[@[Record Name]]),,),0),MATCH("Uploaded",Table2[Action],0)),"")

[Jennsy]

Hi Alkey,

Thanks so much, nearly there! The only issue now is that in UserByOpp I am getting an ABC Widget Uploaded Date in row 8 (for record 789aaa), but this should not be there. If you look in the Resources worksheet for record 789aaa (row 6) you will see that the Widget is ABCWidget, but it was Downloaded, not uploaded. So that date should not show in the UserByOpp worksheet. Is there an easy fix for this?

Thank you for all your help, really is appreciated.

Jennsy

[AlKey]

Ok, what dates should be returned?

[Jennsy]

Hi,

Okay, so at the moment 3 dates are returned in UserByOpps. The top 2 are correct, in rows 4 and 5. The one in row 8 should not be there, as that is the date the ABCWidget was downloaded for that record, not uploaded. Does that help?

Thanks

[AlKey]

=IFERROR(1/(1/(SUMPRODUCT((Resources!$B$2:$B$9=E2)*(Resources!$G$2:$G$9="ABCWidget")*(Resources!$J$2:$J$9="Uploaded")*(Resources!$K$2:$K$9)))),"")

[Jennsy]

Alkey, you are a genius! Thanks