Formula to indicate missing item in range based on multiple criteria

[Excel Sheet Pro]

Preface: Based on a list of names and task numbers associated with them, I'd like to create a (non-VBA) formula to find and return the task number MISSING from a task list for each name, as defined in the following steps. I have color-coded the ranges to make it simple to identify.

The range in YELLOW is the main list. It comprises of names and tasks assigned to those names.
In range in GREEN contains the unique task number list.
The first column in the BLUE range is the unique name list associated with the main list.
The second column in the BLUE range is where the formula will go in each cell, using the name in the first column as the relative reference.

bbbb.xlsx
	A	B	C	D	E	F	G
1	Name_List	Task_No_List		Task_No		Name	Missing Task No.
2	Jim	1		1		Jim	4
3	Susan	1		2		Susan	3
4	Bart	1		3		Bart	2
5	Jim	2		4		Steve	1
6	Susan	2
7	Steve	2
8	Jim	3
9	Bart	3
10	Steve	3
11	Susan	4
12	Bart	4
13	Jim	4
Sheet1

What I would like to see is a formula that returns the MISSING task number associated with a name in the left column in the BLUE range. For instance, Jim is assigned to tasks 1, 2, and 3 in the Main list, but is not assigned 4. Therefore, 4 is the return I'm looking for. Each other name is associated with each of their missing task number.

There are several pre-conditions that should make the solution easier:

1. There could be at least 3 or more names.
2. There could be any number of tasks.
3. However, the tasks always start with 1 and increment by 1 until the last task. No text, and no skipped sequencing of numbers.
4. Each name will be associated with all task numbers EXCEPT for one missing task number. In other words, for every task listed in the Green Task_No range, each name will be assigned to every one of those tasks exactly one time, EXCEPT for the missing task no. There will never be a situaiton where a name is associated with every task number, nor a situation where a name has more than one missing task number. This process is verified already.

I have tried a whole slew of index, match, vlookup, and other formulas, and combinations of such formulas, both straight and array, but couldn't get it right. Currently, the formula is dependent on a grid located on a "helper sheet" but it seems to me that I can simplify this; I just can't get the right formula in place. In addition, no VBA can be used as it is a macro-free workbook. I have the latest version of Excel (Office 365 subscription) if that helps. Many thanks.

 

[Excel Sheet Pro]

I made a mistake in the original chart. Cell A13 should contain the name "Steve". It is currently showing "Jim", which is incorrect. My apologies.

 

[Habtest]

This should do if pre-conditions #4 is infallible, though in the example Jim is associated with all tasks:

Book1
	A	B	C	D	E	F	G
1	Name_List	Task_No_List		Task_No		Name	Missing Task No.
2	Jim	1		1		Jim	4
3	Susan	1		2		Susan	3
4	Bart	1		3		Bart	2
5	Jim	2		4		Steve	1
6	Susan	2
7	Steve	2
8	Jim	3
9	Bart	3
10	Steve	3
11	Susan	4
12	Bart	4
13	Steve	4
Sheet1

Cell Formulas
Range	Formula
G2:G5	G2	=MATCH(TRUE,ISERROR(MATCH($D$2:$D$5,IF($A$2:$A$13=F2,$B$2:$B$13),0)),0)

One minute late for the correction...

 

[Excel Sheet Pro]

This should do if pre-conditions #4 is infallible, though in the example Jim is associated with all tasks:

Book1
	A	B	C	D	E	F	G
1	Name_List	Task_No_List		Task_No		Name	Missing Task No.
2	Jim	1		1		Jim	4
3	Susan	1		2		Susan	3
4	Bart	1		3		Bart	2
5	Jim	2		4		Steve	1
6	Susan	2
7	Steve	2
8	Jim	3
9	Bart	3
10	Steve	3
11	Susan	4
12	Bart	4
13	Steve	4
Sheet1

Cell Formulas
Range	Formula
G2:G5	G2	=MATCH(TRUE,ISERROR(MATCH($D$2:$D$5,IF($A$2:$A$13=F2,$B$2:$B$13),0)),0)

One minute late for the correction...

Spiffing! I didn't think of using Match twice without the IsError function in between. Good job!!

 

[Peter_SSs]

I have the latest version of Excel (Office 365 subscription) if that helps.

Please put that into your Account details (click your user name at the top right of the forum) so helpers always know what Excel version(s) & platform(s) you are using as the best solution often varies by version. (Don’t forget to scroll down & ‘Save’)

With 365 you could also use a single MATCH like this.

21 10 03.xlsm
	A	B	C	D	E	F	G
1	Name_List	Task_No_List		Task_No		Name	Missing Task No.
2	Jim	1		1		Jim	4
3	Susan	1		2		Susan	3
4	Bart	1		3		Bart	2
5	Jim	2		4		Steve	1
6	Susan	2
7	Steve	2
8	Jim	3
9	Bart	3
10	Steve	3
11	Susan	4
12	Bart	4
13	Steve	4
Missing

Cell Formulas
Range	Formula
G2:G5	G2	=MATCH(0,COUNTIFS(A$2:A$13,F2,B$2:B$13,D$2:D$5),0)

 

[Excel Sheet Pro]

Please put that into your Account details (click your user name at the top right of the forum) so helpers always know what Excel version(s) & platform(s) you are using as the best solution often varies by version. (Don’t forget to scroll down & ‘Save’)

With 365 you could also use a single MATCH like this.

21 10 03.xlsm
	A	B	C	D	E	F	G
1	Name_List	Task_No_List		Task_No		Name	Missing Task No.
2	Jim	1		1		Jim	4
3	Susan	1		2		Susan	3
4	Bart	1		3		Bart	2
5	Jim	2		4		Steve	1
6	Susan	2
7	Steve	2
8	Jim	3
9	Bart	3
10	Steve	3
11	Susan	4
12	Bart	4
13	Steve	4
Missing

Cell Formulas
Range	Formula
G2:G5	G2	=MATCH(0,COUNTIFS(A$2:A$13,F2,B$2:B$13,D$2:D$5),0)

Thanks for the heads up! Though to be fair, half of my work is done on Office 2003 on an old XP laptop for legacy maintenance of certain clients. So for the future, if I have a question that needs to be resolved on 2003 version I'll note it at the top of the post.

 

[Peter_SSs]

So for the future, if I have a question that needs to be resolved on 2003 version I'll note it at the top of the post.

That would be a good idea, though for other questions that you might have it would be good to be able to quickly see what version you have so that helpers know what resources you have available.