Using Vlookup, If statements??

[Dprobasco]

I have a spreadsheet with different worksheets in it.

Using two worksheets (Sheet 1 and Sheet 2). Sheet 1 has a list of names with unique ID numbers, and the service name(s) they can provide. You will see a person and their unique ID number can and does provide more than one service but the person and ID number are shown only once for each service they can perform.

Sheet 2 has a list of names and unique ID numbers of the people listed in sheet 1 but each name and ID number are listed once.

What I am trying to do is have Excel take each unique ID number in sheet 1, find that unique number in sheet two and every time it finds that unique number, place the associated service name in the proper column in sheet 2.

As an example, for Dennis A, when it finds the ID number on row 3, it will take the service name next to that ID number(in this case Fundraising) and place a Yes in the Fundraising column (Column J), then move to row 4 and do a similar action by placing a yes in the Transportation column (column k)

Any ideas?

Dale

 

[hagia_sofia]

Hello, would this work for you (in G3):

=DROP(REDUCE("",F3:F5,LAMBDA(x,y,VSTACK(x,IF(ISTEXT(XLOOKUP(y&G1:L1,A3:A13&C3:C13,C3:C13)),"Yes","")))),1)

 

[DanteAmor]

Try this formula on Sheet2

Dante Amor
	A	B	C	D	E	F	G	H
1
2	Name	ID	Chor	Fri	Homemaker	Fundraising	Tran Vol	Tel
3	Den A	E421				Yes	Yes
4	Pau B	E425			Yes	Yes		Yes
5
Sheet2

Cell Formulas
Range	Formula
C3:H4	C3	=IF(SUMPRODUCT((Sheet1!$A$1:$A$100=$B3)*(Sheet1!$C$1:$C$100=C$2)),"Yes","")

 

[Dprobasco]

Try this formula on Sheet2

Dante Amor
	A	B	C	D	E	F	G	H
1
2	Name	ID	Chor	Fri	Homemaker	Fundraising	Tran Vol	Tel
3	Den A	E421				Yes	Yes
4	Pau B	E425			Yes	Yes		Yes
5
Sheet2

Cell Formulas
Range	Formula
C3:H4	C3	=IF(SUMPRODUCT((Sheet1!$A$1:$A$100=$B3)*(Sheet1!$C$1:$C$100=C$2)),"Yes","")

Try this formula on Sheet2

Dante Amor
	A	B	C	D	E	F	G	H
1
2	Name	ID	Chor	Fri	Homemaker	Fundraising	Tran Vol	Tel
3	Den A	E421				Yes	Yes
4	Pau B	E425			Yes	Yes		Yes
5
Sheet2

Cell Formulas
Range	Formula
C3:H4	C3	=IF(SUMPRODUCT((Sheet1!$A$1:$A$100=$B3)*(Sheet1!$C$1:$C$100=C$2)),"Yes","")

[/RANGE

Cell Formulas
Range	Formula

Try this formula on Sheet2

Dante Amor
	A	B	C	D	E	F	G	H
1
2	Name	ID	Chor	Fri	Homemaker	Fundraising	Tran Vol	Tel
3	Den A	E421				Yes	Yes
4	Pau B	E425			Yes	Yes		Yes
5
Sheet2

Cell Formulas
Range	Formula
C3:H4	C3	=IF(SUMPRODUCT((Sheet1!$A$1:$A$100=$B3)*(Sheet1!$C$1:$C$100=C$2)),"Yes","")

sorry for the delay in responding to your ideas.

Since I am more of a novice

Try this formula on Sheet2

Dante Amor
	A	B	C	D	E	F	G	H
1
2	Name	ID	Chor	Fri	Homemaker	Fundraising	Tran Vol	Tel
3	Den A	E421				Yes	Yes
4	Pau B	E425			Yes	Yes		Yes
5
Sheet2

Cell Formulas
Range	Formula
C3:H4	C3	=IF(SUMPRODUCT((Sheet1!$A$1:$A$100=$B3)*(Sheet1!$C$1:$C$100=C$2)),"Yes","")

sorry for the delay in responding to your ideas.

Since I am more of a novice, I decided to use the If sumproduct solution.

It worked great. Had never used that before.

I will likely try the other solution just to gain more knowledge.

So grateful for your ideas and MrExcel. What a resource.

Dale