Lookup with multiple results but only once

[Montanes]

I have a list of vehicles that are scheduled for maintenance work over 8 columns and 1873 rows (including the header).

Each vehicle is assigned to a department.

Column 'A' has the week number
Column 'G' has the department

If I enter a week number in cell J1 i am hoping to generate a list of departments from J2 downwards that have a vehicle scheduled for that week.

However, some departments may have multiple vehicles scheduled for a specific week and i only want it listed once...which is the part i am struggling with.

My head is mush trying to get this. Thank-you to anyone who can help.

 

[Nova1979]

Could you supply an sample image of what you are working with?

 

[Montanes]

Hi,

Please see attached snipping.

So the theory is that i would select a week number in cell J1, and under this i would have a list of all the departments that have a vehicle scheduled for that week. In the attached i have entered '1' as the week i want to look at and i would want all the departments to be listed but everything i do has "Sales" listed twice whereas i am trying to figure out how to remove the duplicates.

I hope i am explaining this correctly, thanks for your help.

 

[Nova1979]

Have you tried a VLOOKUP? What have you tried?

 

[AhoyNC]

If you have Excel 365 with the FILTER and UNIQUE functions then try the formula in L2. Otherwise try the formula in J2 which is an array function and must be entered with CTRL-SHIFT-ENTER.

Book1
	A	B	C	D	E	F	G	H	I	J	K	L
1	Week					Dept.				1
2	1					Acct				Acct		Acct
3	1					Workshop				Workshop		Workshop
4	1					Sales				Sales		Sales
5	1					Oper				Oper		Oper
6	1					Rec				Rec		Rec
7	1					Sales
Sheet1

Cell Formulas
Range	Formula
L2	L2	=UNIQUE(FILTER($F$2:$F$7,$A$2:$A$7=$J$1,""))
J2:J6	J2	{=IFERROR(INDEX($F$2:$F$7,SMALL(IF(FREQUENCY(MATCH($F$2:$F$7,$F$2:$F$7,0),ROW($F$2:$F$7)-ROW($F$2)+1),ROW($F$2:$F$7)-ROW($F$2)+1),ROWS($J$2:J2))),"")}
J7	J7	=IFERROR(INDEX($F$2:$F$7,SMALL(IF(FREQUENCY(MATCH($F$2:$F$7,$F$2:$F$7,0),ROW($F$2:$F$7)-ROW($F$2)+1),ROW($F$2:$F$7)-ROW($F$2)+1),ROWS($J$2:J7))),"")
Press CTRL+SHIFT+ENTER to enter array formulas surrounded with curly braces.

 

[AhoyNC]

There was an error in the formula in J2 above. It should be:
Enter with CTRL-SHIFT-ENTER.

Book1
	A	B	C	D	E	F	G	H	I	J
1	Week					Dept.				1
2	1					Acct				Acct
3	1					Workshop				Workshop
4	1					Sales				Sales
5	1					Oper				Oper
6	1					Rec				Rec
7	1					Sales
Sheet1

Cell Formulas
Range	Formula
J2:J7	J2	{=IFERROR(INDEX($F$2:$F$7,SMALL(IF(FREQUENCY(IF($A$2:$A$7=$J$1,MATCH($F$2:$F$7,$F$2:$F$7,0)),ROW($F$2:$F$7)-ROW($F$2)+1),ROW($F$2:$F$7)-ROW($F$2)+1),ROWS($J$2:J2))),"")}
Press CTRL+SHIFT+ENTER to enter array formulas surrounded with curly braces.

 

[Montanes]

Superb!! Thank-you so much.

 

[AhoyNC]

You're welcome. Thanks for the feedback.