Nesting IF and VLOOKUP formulas

[reberryjr]

I have a worksheet with 13K+ records, structured similarly to the first underlying table.

I'm trying to figure out how I can get to something that looks like the second underlying table. I was thinking that I could nest some IF statements in with some VLOOKUPs, but my brain isn't working with me today.

Name	Grade	Class	Date
Steve	10​	B01	12/1/2019​
Steve	10​	B14	12/8/2019​
Steve	10​	D42	12/7/2019​
Tom	12​	B01	8/7/2020​
Tom	12​	B75	8/7/2020​
Name	Grade	B01	B14	B75	D42
Steve	10​	12/1/2019​	12/8/2019​		12/7/2019​
Tom	12​	8/7/2020​		8/7/2020​

 

[jasonb75]

If this what you need?

Book1
	A	B	C	D	E	F
1	Name	Grade	Class	Date
2	Steve	10	B01	12/01/2019
3	Steve	10	B14	12/08/2019
4	Steve	10	D42	12/07/2019
5	Tom	12	B01	08/07/2020
6	Tom	12	B75	08/07/2020
7
8
9	Name	Grade	B01	B14	B75	D42
10	Steve	10	12/01/2019	12/08/2019		12/07/2019
11	Tom	12	08/07/2020		08/07/2020
Sheet6

Cell Formulas
Range	Formula
C10:F11	C10	=IFERROR(1/(1/MINIFS($D$2:$D$6,$C$2:$C$6,C$9,$A$2:$A$6,$A10)),"")

 

[Fluff]

Please update your account details to show which version of Excel you are using, as this affects what functions you can use.

Two other options depending on your version

+Fluff v2.xlsm
	A	B	C	D	E	F
1	Name	Grade	Class	Date
2	Steve	10	B01	12/01/2019
3	Steve	10	B14	12/08/2019
4	Steve	10	D42	12/07/2019
5	Tom	12	B01	08/07/2020
6	Tom	12	B75	08/07/2020
7
8
9	Name	Grade	B01	B14	B75	D42
10	Steve	10	12/01/2019	12/08/2019		12/07/2019
11	Tom	12	08/07/2020		08/07/2020
12	Name	Grade	B01	B14	B75	D42
13	Steve	10	12/01/2019	12/08/2019		12/07/2019
14	Tom	12	08/07/2020		08/07/2020
15
Summary

Cell Formulas
Range	Formula
C10:F11	C10	=IFERROR(AGGREGATE(15,6,$D$2:$D$6/($A$2:$A$6=$A10)/($C$2:$C$6=C$9),1),"")
C13:F14	C13	=FILTER($D$2:$D$6,($A$2:$A$6=$A13)*($C$2:$C$6=C$12),"")

 

[reberryjr]

Please update your account details to show which version of Excel you are using, as this affects what functions you can use.

Two other options depending on your version

+Fluff v2.xlsm
	A	B	C	D	E	F
1	Name	Grade	Class	Date
2	Steve	10	B01	12/01/2019
3	Steve	10	B14	12/08/2019
4	Steve	10	D42	12/07/2019
5	Tom	12	B01	08/07/2020
6	Tom	12	B75	08/07/2020
7
8
9	Name	Grade	B01	B14	B75	D42
10	Steve	10	12/01/2019	12/08/2019		12/07/2019
11	Tom	12	08/07/2020		08/07/2020
12	Name	Grade	B01	B14	B75	D42
13	Steve	10	12/01/2019	12/08/2019		12/07/2019
14	Tom	12	08/07/2020		08/07/2020
15
Summary

Cell Formulas
Range	Formula
C10:F11	C10	=IFERROR(AGGREGATE(15,6,$D$2:$D$6/($A$2:$A$6=$A10)/($C$2:$C$6=C$9),1),"")
C13:F14	C13	=FILTER($D$2:$D$6,($A$2:$A$6=$A13)*($C$2:$C$6=C$12),"")

I've updated my account to show that I'm working off of MS2013....sorry about that. As far as the solutions, I've tried both of them, and neither worked. I believe that's my fault for not posting the issue correctly, so allow me to try again.

I have a workbook entitled PS. On that workbook are 842 unique names. Each name could have taken up to 144 classes. Each class has a Scheduled and Actual date. Unfortunately, the way I was provided the data, each class for each person, is listed as a record. So, if Steve took 3 classes, he has 3 records. I'm trying to add the class scheduled and actual dates as column headers (288 in total), then fill the dates in. In my mind, that would just allow me to delete duplicates, and I would end up with 842 records, with all of the class scheduled and actual completion dates filled in, in the appropriate class ID.

In the example I provided above, the 2nd table is actually a separate worksheet, as I thought nesting IF and VLOOKUP statements was going to be the route to go. My apologies for not calling out the other worksheet, as it probably impacted the solutions offered.

Hopefully that helps.

 

[Fluff]

If the data is on another sheet, then just add the sheet name like

=IFERROR(AGGREGATE(15,6,Sheet1!$D$2:$D$6/(Sheet1!$A$2:$A$6=$A11)/(Sheet1!$C$2:$C$6=C$9),1),"")

 

[reberryjr]

If the data is on another sheet, then just add the sheet name like

=IFERROR(AGGREGATE(15,6,Sheet1!$D$2:$D$6/(Sheet1!$A$2:$A$6=$A11)/(Sheet1!$C$2:$C$6=C$9),1),"")

Still no joy. I triple checked the formula, and don't see where I've gone off at.

=IFERROR(AGGREGATE(15,6,'[passportSheet_120851.xlsx]Rows 1 - 94145'!$BD$2:$BD$13229/('[passportSheet_120851.xlsx]Rows 1 - 94145'!$A$2:$A$13229=$A2/'[passportSheet_120851.xlsx]Rows 1 - 94145'!$BB$2:$BB$13229=BQ$1),1),"")

 

[Fluff]

If you remove the iferror part of the formula, what do you get?

 

[reberryjr]

If you remove the iferror part of the formula, what do you get?

This results in a #NUM error. The dates are formatted as dates (MM/DD/YY), the "names" are actually account numbers formatted as text, and the "classes" are formatted as general.

=AGGREGATE(15,6,'[passportSheet_120851.xlsx]Rows 1 - 94145'!$BD$2:$BD$13229/('[passportSheet_120851.xlsx]Rows 1 - 94145'!$A$2:$A$13229=$A2/'[passportSheet_120851.xlsx]Rows 1 - 94145'!$BB$2:$BB$13229=BQ$1),1)

 

[Fluff]

Change the format of col BD from date to general, do you see 6 figure numbers?

 

[reberryjr]

Change the format of col BD from date to general, do you see 6 figure numbers?

I changed the formatting to general, and see 5 digit numbers. I reevaluated the formula, and the #NUM issue remained.