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​

 

[Fluff]

In that case you need to check that the values in A2 & BQ1 actually exist in the other workbook, bearing in mind that if you change the format of a cell you are not changing the underlying value. So if you input a number & then format the cell as text, it is still a number. Also check that there are no leading/trailing spaces

 

[reberryjr]

In that case you need to check that the values in A2 & BQ1 actually exist in the other workbook, bearing in mind that if you change the format of a cell you are not changing the underlying value. So if you input a number & then format the cell as text, it is still a number. Also check that there are no leading/trailing spaces

I did that by entering the formula in a cell where I know I should return a value.

 

[jasonb75]

the "names" are actually account numbers formatted as text

Is that the same for both the source data and the row headings in the output table? 12345 formatted as general / number is not equal to 12345 formatted as text.
Either you have used an incorrect range in the formula or the data types are not matching, there are no other reasons for the aggregate formula to fail.

edit:-

Just noticed 2 missing brackets in the formula, see if this works.

=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)

 

[reberryjr]

Is that the same for both the source data and the row headings in the output table? 12345 formatted as general / number is not equal to 12345 formatted as text.
Either you have used an incorrect range in the formula or the data types are not matching, there are no other reasons for the aggregate formula to fail.

edit:-

Just noticed 2 missing brackets in the formula, see if this works.

=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)

Well, that appears to be working. I won't know until the formulas are done evaluating, which is going to take a while on this stone age machine I have. Thanks for the help!

 

[Fluff]

Good spot Jason, missed that completely.