NadoDude
Board Regular
- Joined
- Jun 24, 2010
- Messages
- 84
I am using the following array formula:
{=IF(ISERROR(MATCH($A5&MAX(IF(('ITEMPO Events'!$I$2:$I$15000=$A5)*(LEFT('ITEMPO Events'!$Q$2:$Q$15000,10)="Deployment"),'ITEMPO Events'!$P$2:$P$15000)),$A5&'ITEMPO Events'!$P$2:$P$15000,0)),"None",INDEX('ITEMPO Events'!$P$2:$P$15000,MATCH($A5&MAX(IF(('ITEMPO Events'!$I$2:$I$15000=$A5)*(LEFT('ITEMPO Events'!$Q$2:$Q$15000,10)="Deployment"),'ITEMPO Events'!$P$2:$P$15000)),$A5&'ITEMPO Events'!$P$2:$P$15000,0)))}
In the first part of the formula, the formula is returning the word "None" if there is no matching record. There are instances in my worksheet where columns I and Q match a record, but column P is blank. Can I add something to this array that will say if column I and Q match, but column P is blank, it will return "Currently Deployed" instead of "None"
{=IF(ISERROR(MATCH($A5&MAX(IF(('ITEMPO Events'!$I$2:$I$15000=$A5)*(LEFT('ITEMPO Events'!$Q$2:$Q$15000,10)="Deployment"),'ITEMPO Events'!$P$2:$P$15000)),$A5&'ITEMPO Events'!$P$2:$P$15000,0)),"None",INDEX('ITEMPO Events'!$P$2:$P$15000,MATCH($A5&MAX(IF(('ITEMPO Events'!$I$2:$I$15000=$A5)*(LEFT('ITEMPO Events'!$Q$2:$Q$15000,10)="Deployment"),'ITEMPO Events'!$P$2:$P$15000)),$A5&'ITEMPO Events'!$P$2:$P$15000,0)))}
In the first part of the formula, the formula is returning the word "None" if there is no matching record. There are instances in my worksheet where columns I and Q match a record, but column P is blank. Can I add something to this array that will say if column I and Q match, but column P is blank, it will return "Currently Deployed" instead of "None"
