How to do Double VLOOKUP

[Seifeddine87]

HI
im looking for a functions or a way how i can mark "X" under staff ID Matching the Department ID
example:

A

B

C

D

C

D

E

F

G

H

I

J

K

L

M

N

O

1

Department ID

001-SZ

002-SZ

003-NL

004-JW

005-GW

Staff ID

2

T01

X

X

=?????

001-SZ

T01

T02

T07

T08

T10

T12

T14

T15

3

T09

X

002-SZ

T02

T05

T06

T09

4

T04

X

X

003-NL

T01

T03

T04

5

T14

X

X

004-JW

T07

T11

T04

6

005-GW

T13

T14

T15

 

[Eric W]

Try:

Book1 (version 1).xlsb
	A	B	C	D	E	F	G	H	I	J	K	L	M	N	O	P	Q
1	Department ID	001-SZ	002-SZ	003-NL	004-JW	005-GW			Staff ID
2	T01	X		X					001-SZ	T01	T02	T07	T08	T10	T12	T14	T15
3	T09		X						002-SZ	T02	T05	T06	T09
4	T04			X	X				003-NL	T01	T03	T04
5	T14	X				X			004-JW	T07	T11	T04
6									005-GW	T13	T14	T15
Sheet5

Cell Formulas
Range	Formula
B2:F5	B2	=IF(SUMPRODUCT(($J$2:$Q$6=$A2)*($I$2:$I$6=B$1)),"X","")

 

[Toadstool]

A less elegant way:

Book1
	A	B	C	D	E	F	G	H	I	J	K	L	M	N	O	P	Q
1	Department ID	001-SZ	002-SZ	003-NL	004-JW	005-GW			Staff ID
2	T01	X		X					001-SZ	T01	T02	T07	T08	T10	T12	T14	T15
3	T09		X						002-SZ	T02	T05	T06	T09
4	T04			X	X				003-NL	T01	T03	T04
5	T14	X				X			004-JW	T07	T11	T04
6									005-GW	T13	T14	T15
Sheet1 (2)

Cell Formulas
Range	Formula
B2:F5	B2	=IF(ISNUMBER(MATCH($A2,OFFSET($I$1,MATCH(B$1,$I$2:$I$9999,0),1,,8),0)),"X","")

 

[Seifeddine87]

Try:

Book1 (version 1).xlsb
	A	B	C	D	E	F	G	H	I	J	K	L	M	N	O	P	Q
1	Department ID	001-SZ	002-SZ	003-NL	004-JW	005-GW			Staff ID
2	T01	X		X					001-SZ	T01	T02	T07	T08	T10	T12	T14	T15
3	T09		X						002-SZ	T02	T05	T06	T09
4	T04			X	X				003-NL	T01	T03	T04
5	T14	X				X			004-JW	T07	T11	T04
6									005-GW	T13	T14	T15
Sheet5

Cell Formulas
Range	Formula
B2:F5	B2	=IF(SUMPRODUCT(($J$2:$Q$6=$A2)*($I$2:$I$6=B$1)),"X","")

works perfect thank you very much

 

[Seifeddine87]

A less elegant way:

Book1
	A	B	C	D	E	F	G	H	I	J	K	L	M	N	O	P	Q
1	Department ID	001-SZ	002-SZ	003-NL	004-JW	005-GW			Staff ID
2	T01	X		X					001-SZ	T01	T02	T07	T08	T10	T12	T14	T15
3	T09		X						002-SZ	T02	T05	T06	T09
4	T04			X	X				003-NL	T01	T03	T04
5	T14	X				X			004-JW	T07	T11	T04
6									005-GW	T13	T14	T15
Sheet1 (2)

Cell Formulas
Range	Formula
B2:F5	B2	=IF(ISNUMBER(MATCH($A2,OFFSET($I$1,MATCH(B$1,$I$2:$I$9999,0),1,,8),0)),"X","")

thank you

 

[Eric W]

Glad we could help!