Vlookup but exclude "this value in search"

[FrankieBoyDK]

Hi all,

I need in 1 list to find if any values are the same. And thought that Vlookup would be the easiest way to do it?

Example

Value	Name		Name	Value		Name with same value
1​	test 1		test 1	1​		test 11
2​	test 2		test 2	2​
3​	test 3		test 3	3​
4​	test 4		test 4	4​
5​	test 5		test 5	5​
6​	test 6		test 6	6​
7​	test 7		test 7	7​
8​	test 8		test 8	8​
9​	test 9		test 9	9​
10​	test 10		test 10	10​
1​	test 11		test 11	1​
2​	test 12		test 12	2​
3​	test 13		test 13	3​
4​	test 14		test 14	4​
5​	test 15		test 15	5​
6​	test 16		test 16	6​
7​	test 17		test 17	7​
8​	test 18		test 18	8​
9​	test 19		test 19	9​
10​	test 20		test 20	10​

Here is the same list, but twice. Can I with Vlookup search the list and exclude the value for the name I know is there?

So in this case, I want it to search for the value "1" and it should not display "test 1" but "test 11".

 

[Fluff]

How about

Fluff.xlsm
	A	B	C	D	E	F	G
1	Value	Name		Name	Value		Name with same value
2	1	test 1		test 1	1		test 11
3	2	test 2		test 2	2		test 12
4	3	test 3		test 3	3		test 13
5	4	test 4		test 4	4		test 14
6	5	test 5		test 5	5		test 15
7	6	test 6		test 6	6		test 16
8	7	test 7		test 7	7		test 17
9	8	test 8		test 8	8		test 18
10	9	test 9		test 9	9		test 19
11	10	test 10		test 10	10		test 20
12	1	test 11		test 11	1		test 1
13	2	test 12		test 12	2		test 2
14	3	test 13		test 13	3		test 3
15	4	test 14		test 14	4		test 4
16	5	test 15		test 15	5		test 5
17	6	test 16		test 16	6		test 6
18	7	test 17		test 17	7		test 7
19	8	test 18		test 18	8		test 8
20	9	test 19		test 19	9		test 9
21	10	test 20		test 20	10		test 10
Dashboard

Cell Formulas
Range	Formula
G2:G21	G2	=FILTER($B$2:$B$100,($A$2:$A$100=E2)*($B$2:$B$100<>B2))

 

[FrankieBoyDK]

How about

Fluff.xlsm
	A	B	C	D	E	F	G
1	Value	Name		Name	Value		Name with same value
2	1	test 1		test 1	1		test 11
3	2	test 2		test 2	2		test 12
4	3	test 3		test 3	3		test 13
5	4	test 4		test 4	4		test 14
6	5	test 5		test 5	5		test 15
7	6	test 6		test 6	6		test 16
8	7	test 7		test 7	7		test 17
9	8	test 8		test 8	8		test 18
10	9	test 9		test 9	9		test 19
11	10	test 10		test 10	10		test 20
12	1	test 11		test 11	1		test 1
13	2	test 12		test 12	2		test 2
14	3	test 13		test 13	3		test 3
15	4	test 14		test 14	4		test 4
16	5	test 15		test 15	5		test 5
17	6	test 16		test 16	6		test 6
18	7	test 17		test 17	7		test 7
19	8	test 18		test 18	8		test 8
20	9	test 19		test 19	9		test 9
21	10	test 20		test 20	10		test 10
Dashboard

Cell Formulas
Range	Formula
G2:G21	G2	=FILTER($B$2:$B$100,($A$2:$A$100=E2)*($B$2:$B$100<>B2))

Hi,

Yes this would work, however there might be more than 1 exact match. And it needs to display them all.

I tried this and it looks to be working?

Name	Value		Name with same value
test 1	1​		test 11
test 2	2​		test 2
test 3	3​		test 3, test 13
test 4	4​
test 5	5​
test 6	6​
test 7	7​
test 8	8​
test 9	9​
test 10	10​
test 11	1​
test 12	2​
test 13	3​
test 14	4​
test 15	5​
test 16	6​
test 17	7​
test 18	8​
test 19	9​
test 20	10​

Formula:
=TEXTJOIN(", ";TRUE;(IF(F4=$B$2:$B$21;C2:$C$21;"")))

 

[Fluff]

Glad you sorted it & thanks for the feedback.