Share information between sheets to show item age

[lauralizalfos]

I'm trying to track product returns and determine the year a unit was built based off of it's serial number.
I have a sheet that lists the serial number of the first product shipped in a given year, and another sheet with the product and serial number that has been returned. I would like a way for excel to quickly output the production year for the returned item, so I don't have to look it all up manually.

For example we have Sheet 1:

And Sheet 2:

I've tried using a series of "=IF" statements, which almost got me where I wanted, but I want it to be able to know to look at the Hookshot row and pull the data that way.

If there's a better way to organize my sheets I can adapt.

 

[Peter_SSs]

Welcome to the MrExcel board!

For the future, please investigate XL2BB for providing sample data to make it easier for helpers by not having to manually type out sample data to test with.

It depends on how realistic your sample data is. Do the Serial numbers actually have a rigid format of two letters (always the same for the same product) followed by exactly 4 digits? If so, you could try something like this. Check the table name in the formula.

lauralizalfos.xlsm
	A	B	C	D	E	F
1	Item	2019	2020	2021	2022	2023
2	Hookshot	AA1001	AA1052	AA1105	AA1220	AA1345
3	Boomerang	BB1001	BB1210	BB1501	BB1698	BB1785
4	Ocarina		CC1001	CC1036	CC1108	CC1175
5	Slingshot		DD1001	DD1075	DD1154	DD1222
Sheet2

lauralizalfos.xlsm
	A	B	C
1	Returned Item	Serial Num	Year
2	Hookshot	AA1024	2019
3	Boomerang	BB1442	2020
4	Slingshot	DD1223	2023
5	Arrow	XX1234	#N/A
6	Boomerang	BB0900	#N/A
Sheet1

Cell Formulas
Range	Formula
C2:C6	C2	=XLOOKUP([@[Serial Num]],FILTER(tblShipped,tblShipped[Item]=[@[Returned Item]],NA()),tblShipped[#Headers],,-1)

 

[lauralizalfos]

Thanks for your help, Peter, I've added XL2BB for future use!

I'm still running into an issue, using the formula you've shared I am presented with a syntax error

And when I click OK it highlights the first part of the formula

I made sure to name the table on Sheet 2 to "Shipped," do I need to rename anything on sheet1? This has all been tested with the same data you shared, copy/pasted into my excel.

Also, to answer your question, yes, the serial number format is rigid for all the products. Some combination of 2 letters (AA, FN, HG) followed by 5 digits. So "AA10243" or "BQ10054" are both valid serial numbers

Serial Num Year Test.xlsx
	A	B	C
1	Returned Item	Serial Num	Year
2	Hookshot	AA1024	=XLOOKUP([@[Serial Num]],FILTER(tblShipped,tblShipped[Item]=[@[Returned Item]],NA()),tblShipped[#Headers],,-1)
3	Boomerang	BB1442	=XLOOKUP([@[Serial Num]],FILTER(tblShipped,tblShipped[Item]=[@[Returned Item]],NA()),tblShipped[#Headers],,-1)
4	Slingshot	DD1223	=XLOOKUP([@[Serial Num]],FILTER(tblShipped,tblShipped[Item]=[@[Returned Item]],NA()),tblShipped[#Headers],,-1)
5	Arrow	XX1234	=XLOOKUP([@[Serial Num]],FILTER(tblShipped,tblShipped[Item]=[@[Returned Item]],NA()),tblShipped[#Headers],,-1)
6	Boomerang	BB0900	=XLOOKUP([@[Serial Num]],FILTER(tblShipped,tblShipped[Item]=[@[Returned Item]],NA()),tblShipped[#Headers],,-1)
Sheet1

Serial Num Year Test.xlsx
	A	B	C	D	E	F
1	Item	2019	2020	2021	2022	2023
2	Hookshot	AA1001	AA1052	AA1105	AA1220	AA1345
3	Boomerang	BB1001	BB1210	BB1501	BB1698	BB1785
4	Ocarina		CC1001	CC1036	CC1108	CC1175
5	Slingshot		DD1001	DD1075	DD1154	DD1222
Sheet2

 

[Peter_SSs]

And when I click OK it highlights the first part of the formula

That seems to be indicating that there is a discrepancy between the serial number heading in the Sheet1 table and the serial number heading used in the formula in Sheet1. Try removing the space between the two words in the heading and in the formula as shown here

 

[lauralizalfos]

That seems to be indicating that there is a discrepancy between the serial number heading in the Sheet1 table and the serial number heading used in the formula in Sheet1. Try removing the space between the two words in the heading and in the formula as shown here

View attachment 102797

We're getting warmer!

I changed a few header names so everything was happy, and I also found that the latter 2 "tblShipped" in the formula had to be condensed to just "Shipped", or it would pop up the same syntax error I shared before.
I no longer get any pop-up syntax warnings when I input the formula, but it's still not outputting the year, and just giving me a #NAME? error.
I've tried referencing Shipping[ItemName] and Shipping[@ItemName] to see if either of those would get it working, but it seems like that's not doing the trick.

Book1
	A	B	C
1	ReturnedItem	SerialNum	Year
2	Hookshot	AA1024	#NAME?
3	Boomerang	BB1442	#NAME?
4	Slingshot	DD1223	#NAME?
5	Arrow	XX1234	#NAME?
6	Boomerang	BB0900	#NAME?
Sheet1

Cell Formulas
Range	Formula
C2	C2	=XLOOKUP([@SerialNum],FILTER(tblShipped,Shipped[ItemName]=[@ReturnedItem],NA()),Shipped[#Headers],,-1)
C3:C6	C3	=XLOOKUP([@SerialNum],FILTER(tblShipped,Shipped[@ItemName]=[@ReturnedItem],NA()),Shipped[#Headers],,-1)

Book1
	A	B	C	D	E	F
1	ItemName	2019	2020	2021	2022	2023
2	Hookshot	AA1001	AA1052	AA1105	AA1220	AA1345
3	Boomerang	BB1001	BB1210	BB1501	BB1698	BB1785
4	Ocarina		CC1001	CC1036	CC1108	CC1175
5	Slingshot		DD1001	DD1075	DD1154	DD1222
Sheet2

 

[lauralizalfos]

That seems to be indicating that there is a discrepancy between the serial number heading in the Sheet1 table and the serial number heading used in the formula in Sheet1. Try removing the space between the two words in the heading and in the formula as shown here

View attachment 102797

Good news! I kept playing with the formula, and I removed the first "tbl" and the system works! I used the code with my actual existing product serial number table and it works flawlessly.

Thank you so much for your help!

Here's the code that ended up working:
=XLOOKUP([@SerialNum],FILTER(Shipped,Shipped[ItemName]=[@ReturnedItem],NA()),Shipped[#Headers],,-1)

 

[Peter_SSs]

Since you didn't originally indicate what the table names were, I did mention in post 2 to check the table name.

Check the table name in the formula.

I used the code with my actual existing product serial number table and it works flawlessly.

Here's the code that ended up working:
=XLOOKUP([@SerialNum],FILTER(Shipped,Shipped[ItemName]=[@ReturnedItem],NA()),Shipped[#Headers],,-1)

Hmm, that doesn't see quite right to me since in that formula there is no space in 'SerialNum' but in your original image there clearly is.

Anyway, the main thing is that you got it working in the end. Thanks for letting us know.