VLOOKUP with multiple data sets/outputs

[Sa1l0rJ3rry]

Good morning. I don't even really know if VLOOKUP is the formula I need but here's what I'm trying to do.

On sheet 1 I have a list of names in column A. In columns B through T I have a list of training courses. Something like this:

<tbody> [TD="width: 72, bgcolor: transparent"] [/TD]
[TD="width: 69, bgcolor: transparent"] Course 1 [/TD]
[TD="width: 69, bgcolor: transparent"] Course 2 [/TD]
[TD="width: 69, bgcolor: transparent"] Course 3 [/TD]
[TD="width: 69, bgcolor: transparent"] Course 4 [/TD]
[TD="width: 69, bgcolor: transparent"] Course 5 [/TD]
[TD="width: 69, bgcolor: transparent"] Course 6 [/TD]
[TD="width: 69, bgcolor: transparent"] Course 7 [/TD]
[TD="width: 69, bgcolor: transparent"] Course 8 [/TD]
[TD="width: 72, bgcolor: transparent"] Adams [/TD]
[TD="width: 69, bgcolor: transparent"] [/TD]
[TD="width: 69, bgcolor: transparent"] [/TD]
[TD="width: 69, bgcolor: transparent"] [/TD]
[TD="width: 69, bgcolor: transparent"] [/TD]
[TD="width: 69, bgcolor: transparent"] [/TD]
[TD="width: 69, bgcolor: transparent"] [/TD]
[TD="width: 69, bgcolor: transparent"] [/TD]
[TD="width: 69, bgcolor: transparent"] [/TD]
[TD="width: 72, bgcolor: transparent"] Anderson [/TD]
[TD="width: 69, bgcolor: transparent"] [/TD]
[TD="width: 69, bgcolor: transparent"] [/TD]
[TD="width: 69, bgcolor: transparent"] [/TD]
[TD="width: 69, bgcolor: transparent"] [/TD]
[TD="width: 69, bgcolor: transparent"] [/TD]
[TD="width: 69, bgcolor: transparent"] [/TD]
[TD="width: 69, bgcolor: transparent"] [/TD]
[TD="width: 69, bgcolor: transparent"] [/TD]
[TD="width: 72, bgcolor: transparent"] Borse [/TD]
[TD="width: 69, bgcolor: transparent"] [/TD]
[TD="width: 69, bgcolor: transparent"] [/TD]
[TD="width: 69, bgcolor: transparent"] [/TD]
[TD="width: 69, bgcolor: transparent"] [/TD]
[TD="width: 69, bgcolor: transparent"] [/TD]
[TD="width: 69, bgcolor: transparent"] [/TD]
[TD="width: 69, bgcolor: transparent"] [/TD]
[TD="width: 69, bgcolor: transparent"] [/TD]
[TD="width: 72, bgcolor: transparent"] Calloway [/TD]
[TD="width: 69, bgcolor: transparent"] [/TD]
[TD="width: 69, bgcolor: transparent"] [/TD]
[TD="width: 69, bgcolor: transparent"] [/TD]
[TD="width: 69, bgcolor: transparent"] [/TD]
[TD="width: 69, bgcolor: transparent"] [/TD]
[TD="width: 69, bgcolor: transparent"] [/TD]
[TD="width: 69, bgcolor: transparent"] [/TD]
[TD="width: 69, bgcolor: transparent"] [/TD]
[TD="width: 72, bgcolor: transparent"] Fanta [/TD]
[TD="width: 69, bgcolor: transparent"] [/TD]
[TD="width: 69, bgcolor: transparent"] [/TD]
[TD="width: 69, bgcolor: transparent"] [/TD]
[TD="width: 69, bgcolor: transparent"] [/TD]
[TD="width: 69, bgcolor: transparent"] [/TD]
[TD="width: 69, bgcolor: transparent"] [/TD]
[TD="width: 69, bgcolor: transparent"] [/TD]
[TD="width: 69, bgcolor: transparent"] [/TD]
</tbody>

On sheet 2 I have a report which lists every course thosepeople have ever completed and looks something like this:

<tbody> [TD="width: 95, bgcolor: transparent"] Adams
[/TD]
[TD="width: 126, bgcolor: transparent"] Course 11132
[/TD]
[TD="width: 84, bgcolor: transparent"] 1/1/2019
[/TD]
[TD="width: 95, bgcolor: transparent"] Adams
[/TD]
[TD="width: 126, bgcolor: transparent"] Course 21657
[/TD]
[TD="width: 84, bgcolor: transparent"] 1/1/2019
[/TD]
[TD="width: 95, bgcolor: transparent"] Adams
[/TD]
[TD="width: 126, bgcolor: transparent"] Course 21565
[/TD]
[TD="width: 84, bgcolor: transparent"] 1/1/2019
[/TD]
[TD="width: 95, bgcolor: transparent"] Anderson
[/TD]
[TD="width: 126, bgcolor: transparent"] Course 11132
[/TD]
[TD="width: 84, bgcolor: transparent"] 1/1/2019
[/TD]
[TD="width: 95, bgcolor: transparent"] Anderson
[/TD]
[TD="width: 126, bgcolor: transparent"] Course 21657
[/TD]
[TD="width: 84, bgcolor: transparent"] 1/1/2019
[/TD]
[TD="width: 95, bgcolor: transparent"] Anderson
[/TD]
[TD="width: 126, bgcolor: transparent"] Course 55525
[/TD]
[TD="width: 84, bgcolor: transparent"] 1/1/2019
[/TD]
[TD="width: 95, bgcolor: transparent"] Borse
[/TD]
[TD="width: 126, bgcolor: transparent"] Course 11132
[/TD]
[TD="width: 84, bgcolor: transparent"] 1/1/2019
[/TD]
</tbody>

What I’m trying to do is insert a formula on sheet 1 next toany given name that checks sheet 2 for that name, then when it finds it, checksfor that course. If it finds the courseit displays the date of completion. If it does not find it, it displayssomething like “Not Completed”


As you can see from the table, multiple people will have thesame courses listed, so I need the formula to correlate the name to the coursenumber somehow without returning a false positive.

 

[Fluff]

As long as the courses on sheet2 match the headers on sheet1 try

Excel 2013/2016
	A	B	C	D	E	F	G	H	I
1		Course 1	Course 2	Course 3	Course 4	Course 5	Course 6	Course 7	Course 8
2	Adams	01/01/2019	01/01/2019	None	None	None	None	None	01/01/2019
3	Anderson	None	None	None	01/01/2019	None	01/01/2019	01/01/2019	None
4	Borse	None	None	01/01/2019	None	None	None	None	None
5	Calloway	None	None	None	None	None	None	None	None
6	Fanta	None	None	None	None	None	None	None	None
Sheet1

Cell Formulas
Range	Formula
B2	{=IFERROR(INDEX(Sheet2!$C$2:$C$8,MATCH(1,(Sheet2!$A$2:$A$8=$A2)*(Sheet2!$B$2:$B$8=B$1),0)),"None")}
Press CTRL+SHIFT+ENTER to enter array formulas.

 

[Sa1l0rJ3rry]

Thank you for the reply. I can add a row on sheet 1 that lists the course exactly as it appears on sheet 2 to make this work. I am super new to excel formulas, could you break this formula down a bit for easier understanding?

 

[Fluff]

The part in red is the value to return if a match is found.
The first part in purple returns a value of true or false if the value in sheet2 matches A2 on sheet1
The second part in purple does the same but looking to match the course rather than the name.
The * is the equivalent of AND, which also turns the true or false into 1 or 0

If you goto select a cell containing the formula & select the formula tab > Evaluate formula, you can step through the calculation to see what happens.

 

[Sa1l0rJ3rry]

So this is where I messed up. I overestimated my abilities. I tried to keep my example as simple as possibleand didn’t use the exact layout of my particular excel sheet. I figured I couldjust modify your answer and apply it, but my lack of knowledge about excelfunctions is apparently very extensive. Lol.

So for clarity, here are the exact boxes in which each piece of information islisted:

Where I need the formula: Sheet 1, Columns D through Q. Each column is a different course, each rowis a new name. (254 Rows)
Where the data is being pulled from: Sheet 2, Column C(Course name) and Column D (Course completion date).

I’ve made a new row (Sheet 1 Row 2, Columns D though Q) inwhich to reference the exact course name to match it from sheet 2.

Hopefully, I haven’t made this more confusing. Thank you somuch for your time.

 

[Fluff]

Where are the names in sheet1?

 

[Sa1l0rJ3rry]

Apologies. That part was accurate. They are listed in Column 1

 

[Fluff]

With sheet2 like

Excel 2013/2016
	A	B	C	D
1	Name		Course	Date
2	Adams		Course 1	01/01/2019
3	Adams		Course 2	01/01/2019
4	Adams		Course 8	01/01/2019
5	Anderson		Course 4	01/01/2019
6	Anderson		Course 6	01/01/2019
7	Anderson		Course 7	01/01/2019
8	Borse		Course 3	01/01/2019
Sheet2

and sheet1 like

Excel 2013/2016
	A	B	C	D	E	F	G	H	I	J	K
1
2				Course 1	Course 2	Course 3	Course 4	Course 5	Course 6	Course 7	Course 8
3	Adams			01/01/2019	01/01/2019	None	None	None	None	None	01/01/2019
4	Anderson			None	None	None	01/01/2019	None	01/01/2019	01/01/2019	None
5	Borse			None	None	01/01/2019	None	None	None	None	None
6	Calloway			None	None	None	None	None	None	None	None
7	Fanta			None	None	None	None	None	None	None	None
Sheet1

Cell Formulas
Range	Formula
D3	{=IFERROR(INDEX(Sheet2!$D$2:$D$8,MATCH(1,(Sheet2!$A$2:$A$8=$A3)*(Sheet2!$C$2:$C$8=D$2),0)),"None")}
Press CTRL+SHIFT+ENTER to enter array formulas.

Put the formula in D3 confirm with CSE to get the {} then fill down & across

 

[Sa1l0rJ3rry]

Awesome, thank you for all the help.

 

[Fluff]

You're welcome & thanks for the feedback