IF, OR, Index, Match, Multiple Criteria

[wintye]

Hi All,

I hope you're enjoying the weekend.

I wonder if anyone can help with the following formula. I'm trying to pull a date from another table but only if it matches certain criteria.

Table with Data

Unique Identifier	First Name	Last Name	Course	Date	Grade
12345	Jane	Doe	Course 1	15/04/2016	3
12345	Jane	Doe	Course 2	17/05/2017	3
12345	Jane	Doe	Course 3	19/01/2018	3
34567	John	Smith	Course 2	10/07/2017	5
34567	John	Smith	Course 3	25/06/2017	5
34567	John	Smith	Course 1	28/10/2016	5
78901	Jane	Smith	Course 3	01/01/2016	4
78901	Jane	Smith	Course 2	01/10/2017	4
78901	Jane	Smith	Course 1	01/05/2015	4
	John	Doe	Course 2	29/03/2015	7
	John	Doe	Course 1	23/10/2017	7
	John	Doe	Course 3	16/03/2016	7

<tbody>
</tbody>

Table pulling data into

Last Name	First Name	Unique Identifier	Course 1	Course 2	Course 3
VLOOKUP from another table	VLOOKUP from another table	VLOOKUP from another table	to include the formula	to include the formula	to include the formula

<tbody>
</tbody>

The formula I have so far is:

{=IF(C2="","",IF(OR(Data_Report[Grade]="3",Data_Report[Grade]="4"),INDEX(Data_Report[Date],MATCH(1,(Data_Report[Unique Identifier]=C2)*(Data_Report[Course]="1"),0)),"Not Applicable"))}

Basically I'd like the formula to show if there's nothing in the "unique Identifier" column then to leave the cell empty,
If there is a "unique Identifier" in cell 'C2' then to lookup the unique identifier to the data table ensuring to match the grade to either a grade 3 or 4 and additionally match the the course name and pulling through the date the course was completed.
If the grade is different to 3 or 4 then to return 'not applicable'

e.g. = Jane Doe, Course 2 should show as '17/05/2017'
John Smith, Course 3 should show as 'Not Applicable'
Jane Smith, Course 1 should show as '01/05/2015'
John Doe, Course 1 should show as a blank cell

Hoping the above makes sense and welcome any feedback and advise. I've spen forever trying to get it to work and am so close but haven't quite got it.

Many thanks in advance for reading
Wintye
****** id="cke_pastebin" style="position: absolute; top: 0px; width: 1px; height: 1px; overflow: hidden; left: -1000px;">

17/05/2017

<tbody>
</tbody>

</body>

 

[steve the fish]

Have a go with:

=IF(C2="","",IF(OR(INDEX(Data_Report[Grade],MATCH(1,INDEX((Data_Report[Unique Identifier]=C2)*(Data_Report[Course]=D$1),0),0))={3,4}),INDEX(Data_Report[Date],MATCH(1,INDEX((Data_Report[Unique Identifier]=C2)*(Data_Report[Course]=D$1),0),0)),"Not Applicable"))

 

[markmzz]

Maybe the Array Formula (use Ctrl+Shift+Enter to enter the formula) below:

In D2 and copy down and to the right:

=IF($C2="","",IFERROR(LOOKUP(1,1/((Data_Report[Unique Identifier]=$C2)*
((Data_Report[Grade]=3)+(Data_Report[Grade]=4))*(Data_Report[Course]=D$1)),Data_Report[Date]),"Not Applicable"))

Markmzz

 

[Peter_SSs]

.. yet another slight variation.
The formula in D2 must be Copy/Pasted (not dragged) across to E2:F2 before being copied (or dragged) down.
[An alternative is to enter my D2 formula as an array formula - as markmzz did with his - which could then be dragged across & down but the array entry makes my formula somewhat slower to calculate]

Excel Workbook
	A	B	C	D	E	F
1	Last Name	First Name	Unique Id	Course 1	Course 2	Course 3
2	Smith	Jane	78901	1/05/2015	1/10/2017	1/01/2016
3	Doe	Jane	12345	15/04/2016	17/05/2017	19/01/2018
4	Doe	John
5	Smith	John	34567	Not Applicable	Not Applicable	Not Applicable
6
7
8	Unique Identifier	First Name	Last Name	Course	Date	Grade
9	12345	Jane	Doe	Course 1	15/04/2016	3
10	12345	Jane	Doe	Course 2	17/05/2017	3
11	12345	Jane	Doe	Course 3	19/01/2018	3
12	34567	John	Smith	Course 2	10/07/2017	5
13	34567	John	Smith	Course 3	25/06/2017	5
14	34567	John	Smith	Course 1	28/10/2016	5
15	78901	Jane	Smith	Course 3	1/01/2016	4
16	78901	Jane	Smith	Course 2	1/10/2017	4
17	78901	Jane	Smith	Course 1	1/05/2015	3
18		John	Doe	Course 2	29/03/2015	7
19		John	Doe	Course 1	23/10/2017	7
20		John	Doe	Course 3	16/03/2016	7
21
Course Date

 

[wintye]

Maybe the Array Formula (use Ctrl+Shift+Enter to enter the formula) below:

In D2 and copy down and to the right:

=IF($C2="","",IFERROR(LOOKUP(1,1/((Data_Report[Unique Identifier]=$C2)*
((Data_Report[Grade]=3)+(Data_Report[Grade]=4))*(Data_Report[Course]=D$1)),Data_Report[Date]),"Not Applicable"))

Markmzz

Thank you all so much for your help.

Markmzz - Yours works very well.

However I also need for it to tell me if a grade 3 or 4 hasn't completed the course.

At the moment if their unique identifier isn't showing on the report (which it won't as not completed) for that particular course (course 3 for example) then at the moment it just goes straight to "Not Applicable" and I need to to say "Due". I do have a another sheet with all unique identifiers listed with grade.

Is there a way this can also be incorporated?

Thank you so much for your help so far, It's really appreciated

Wintye

 

[markmzz]

Markmzz - Yours works very well.

However I also need for it to tell me if a grade 3 or 4 hasn't completed the course.

At the moment if their unique identifier isn't showing on the report (which it won't as not completed) for that particular course (course 3 for example) then at the moment it just goes straight to "Not Applicable" and I need to to say "Due". I do have a another sheet with all unique identifiers listed with grade.

Is there a way this can also be incorporated?

Thank you so much for your help so far, It's really appreciated

Wintye

Hi Wintye,

I'm sorry, but I didn't understand what you want.

Could you put an example (like placed in your post #01) in your next post? With more details (what you have and what you want).

Markmzz

 

[wintye]

Hi Wintye,

I'm sorry, but I didn't understand what you want.

Could you put an example (like placed in your post #01) in your next post? With more details (what you have and what you want).

Markmzz

Hi Markmzz,

Thanks so much for your reply.

At the moment I have a colleague that is grade 4 and pulled through on the report for two of the courses but not the final report due to it's not yet been completed. I need the formula to show that that person hasn't completed and is still due - due to being a grade 4.

Table with Data

Unique Identifier	First Name	Last Name	Course	Date	Grade
12345	Jane	Doe	Course 1	15/04/2016	3
12345	Jane	Doe	Course 2	17/05/2017	3
34567	John	Smith	Course 2	10/07/2017	5
34567	John	Smith	Course 3	25/06/2017	5
34567	John	Smith	Course 1	28/10/2016	5
78901	Jane	Smith	Course 3	01/01/2016	4
78901	Jane	Smith	Course 2	01/10/2017	4
78901	Jane	Smith	Course 1	01/05/2015	4
	John	Doe	Course 2	29/03/2015	7
	John	Doe	Course 1	23/10/2017	7
	John	Doe	Course 3	16/03/2016	7

<tbody>
</tbody>

As you can see from the above table, Jane Doe is a grade 3 so needs to complete all three courses but is only showing on the data report as having completed course 1 & course 2. Course 3 won't show on this report until it's completed.

I need the formula (that's pulling this data from the data report into another sheet) to also advise that as that person is a grade 3 (or 4) and that course isn't listed then to show as due to show that it still needs to be completed.

Hopefully the above helps a little but let me know if not.

Many thanks
Wintye

 

[markmzz]

Hi Markmzz,

Thanks so much for your reply.

At the moment I have a colleague that is grade 4 and pulled through on the report for two of the courses but not the final report due to it's not yet been completed. I need the formula to show that that person hasn't completed and is still due - due to being a grade 4.

As you can see from the above table, Jane Doe is a grade 3 so needs to complete all three courses but is only showing on the data report as having completed course 1 & course 2. Course 3 won't show on this report until it's completed.

I need the formula (that's pulling this data from the data report into another sheet) to also advise that as that person is a grade 3 (or 4) and that course isn't listed then to show as due to show that it still needs to be completed.

Hopefully the above helps a little but let me know if not.

Many thanks
Wintye

Hi Wintye,

Do some tests with the Array Formula (use Ctrl+Shift+Enter to enter the formula) below and tell me if it works.

=IF($C2="","",IFERROR(LOOKUP(1,1/((Data_Report[Unique Identifier]=$C2)*
((Data_Report[Grade]=3)+(Data_Report[Grade]=4))*(Data_Report[Course]=D$1)),Data_Report[Date]),
IF(SUM((Data_Report[Unique Identifier]=$C2)*((Data_Report[Grade]=3)+(Data_Report[Grade]=4))),"Due","Not Applicable")))

Markmzz

 

[wintye]

Hi All,

Me again. Thank you so much for your help previously.

My needs have changed a little since the above and I'm wondering if anyone could help?


Table:

Unique Identifier	First Name	Last Name	Grade	Status	Completed On	+ Years			Due
12345	Jane	Doe	5	Not Applicable		1
12346	John	Doe	5	Not Applicable	13/12/2017	1			13/12/2018
12347	Jane	Smith	7	Not Applicable	12/11/2017	1			23/11/2018
12348	John	Smith	4	Due		1
12349	Jane	Jones	5	Not Applicable	11/12/2017	1			11/12/2018
12334	John	Jones	4	Completed	14/12/2017	1			14/12/2018
12335	Jane	Doe	6	Not Applicable	02/01/2018	1			02/01/2019
12336	John	Doe	5	Not Applicable	29/09/2017	1			27/09/2018
12337	Jane	Smith	4	Completed	27/09/2017	1			27/09/2018
12338	John	Smith	3	Due		1

<tbody>
</tbody>

I need the formula inserted in the Status column to return the values as shown above.

I need this column to show as

"Not Applicable" if the grade is 5 or higher. (Some that are grade 5 or above have completed the course but I need it to ignore that and still show as not available.

"Due" if a Grade 3 or 4 doesn't have a date entered in the 'completed on' column
"Due in 2 wks" if the due column is today's date + 14
"Due in 4 wks" if the due column is today's date + 28
"Expired" if less than Today's Date
"Completed" if the date is within a year (less a month)

Any help would be greatly greatly appreciated.

Thank you in advance
Wintye

 

[markmzz]

Hi All,

Me again. Thank you so much for your help previously.

My needs have changed a little since the above and I'm wondering if anyone could help?


I need the formula inserted in the Status column to return the values as shown above.

I need this column to show as

"Not Applicable" if the grade is 5 or higher. (Some that are grade 5 or above have completed the course but I need it to ignore that and still show as not available.

"Due" if a Grade 3 or 4 doesn't have a date entered in the 'completed on' column
"Due in 2 wks" if the due column is today's date + 14
"Due in 4 wks" if the due column is today's date + 28
"Expired" if less than Today's Date
"Completed" if the date is within a year (less a month)

Any help would be greatly greatly appreciated.

Thank you in advance
Wintye

Hi Wintye!

If I understand correctly what you want now, maybe the suggestion below can help you:

In E2

=IF([@Grade]>4,"Not Applicable",
IF([@[Completed On]]="","Due",
LOOKUP(N([@Due])-TODAY(),{-2958465,"";-3650,"Expired";14,"Due in 2 wks";28,"Due in 4 wks";30,"Completed";366,""})))

	A	B	C	D	E	F	G	H
1	Unique Identifier	First Name	Last Name	Grade	Status	Completed On	+ Years	Due
2	12345	Jane	Doe	5	Not Applicable		1
3	12346	John	Doe	5	Not Applicable	13/12/2017	1	13/12/2018
4	12347	Jane	Smith	7	Not Applicable	12/11/2017	1	23/11/2018
5	12348	John	Smith	4	Due		1
6	12349	Jane	Jones	5	Not Applicable	11/12/2017	1	11/12/2018
7	12334	John	Jones	4	Completed	14/12/2017	1	14/12/2018
8	12335	Jane	Doe	6	Not Applicable	02/01/2018	1	02/01/2019
9	12336	John	Doe	5	Not Applicable	29/09/2017	1	27/09/2018
10	12337	Jane	Smith	4	Completed	27/09/2017	1	27/09/2018
11	12338	John	Smith	3	Due		1
12
****	****************	***********	************	*******	*****************	***************	********	************

<tbody>
</tbody>

Markmzz