Filtering unique value and apply VLOOKUP

[excel_beta_345User]

Hi All,

Extremely sorry that I was not able to upload excel sheet hence had to go with snapshot.. Can you look into it and help me with possible solution. How do I filter unique value and vlookup it in left table? Please advise.

 

[iliace]

Assuming you are able to use "modern Excel" functions, here is a lambda that takes the standard, year, and table, and returns true/false value.

=LAMBDA(standard,whichyear,inputtable,
LET(filtarray, CHOOSECOLS(FILTER(inputtable, CHOOSECOLS(inputtable, 1)=standard), 2),
string, TEXTJOIN(",", TRUE, filtarray),
ISNUMBER(FIND(whichyear, string))))

Put that in a Name (I called mine FindStandard), then you can use in C8 a formula like this:

=FindStandard($B8, C$7, $N$7:$O$23)

 

[Fluff]

Hi & welcome to MrExcel.
Another option

Fluff.xlsm
	A	B	C	D	E	F	G	H	I	J	K	L	M	N	O
6
7			2001	2002	2003	2004	2005	2006	2007	2008	2009	2010
8		5th	TRUE	FALSE	TRUE	FALSE	TRUE	FALSE	TRUE	FALSE	TRUE	FALSE		5th	2001,2003,2005,2007
9		6th	FALSE	TRUE	FALSE	FALSE	TRUE	FALSE	FALSE	TRUE	TRUE	TRUE		5th	2001,2003,2009
10		7th	FALSE	FALSE	FALSE	FALSE	FALSE	FALSE	FALSE	FALSE	FALSE	FALSE		5th	2009
11		8th	FALSE	FALSE	FALSE	FALSE	FALSE	FALSE	FALSE	FALSE	FALSE	FALSE		5th	2007,2009
12		9th	FALSE	FALSE	FALSE	FALSE	FALSE	FALSE	FALSE	FALSE	FALSE	FALSE		6th	2002
13														6th	2002,2008
14														6th	2002,2005,2008
15														6th	2008,2009,2010
Sheet6

Cell Formulas
Range	Formula
C8:L12	C8	=ISNUMBER(XMATCH(C$7&"",TEXTSPLIT(TEXTJOIN(",",,FILTER($O$8:$O$15,$N$8:$N$15=$B8)),",")))

 

[excel_beta_345User]

Hi & welcome to MrExcel.
Another option

Fluff.xlsm
	A	B	C	D	E	F	G	H	I	J	K	L	M	N	O
6
7			2001	2002	2003	2004	2005	2006	2007	2008	2009	2010
8		5th	TRUE	FALSE	TRUE	FALSE	TRUE	FALSE	TRUE	FALSE	TRUE	FALSE		5th	2001,2003,2005,2007
9		6th	FALSE	TRUE	FALSE	FALSE	TRUE	FALSE	FALSE	TRUE	TRUE	TRUE		5th	2001,2003,2009
10		7th	FALSE	FALSE	FALSE	FALSE	FALSE	FALSE	FALSE	FALSE	FALSE	FALSE		5th	2009
11		8th	FALSE	FALSE	FALSE	FALSE	FALSE	FALSE	FALSE	FALSE	FALSE	FALSE		5th	2007,2009
12		9th	FALSE	FALSE	FALSE	FALSE	FALSE	FALSE	FALSE	FALSE	FALSE	FALSE		6th	2002
13														6th	2002,2008
14														6th	2002,2005,2008
15														6th	2008,2009,2010
Sheet6

Cell Formulas
Range	Formula
C8:L12	C8	=ISNUMBER(XMATCH(C$7&"",TEXTSPLIT(TEXTJOIN(",",,FILTER($O$8:$O$15,$N$8:$N$15=$B8)),",")))

This is too good, you saved my day. Really appreciate the support.

 

[Fluff]

You're welcome & thanks for the feedback.