Multiple IF statements in single column

[Afro_Cookie]

I am starting a defect log that will catalogue multiple item classes (4 so far) and I want to group their part numbers into a single column.
I can write a nested If statement that will do the calculations I need to pull data from other reference files, but it's ugly and if someone messes with the code, it will affect a lot of data.
Is there a way in VBA to write a statement that will pull relevant data from different files, depending on the value in a single column?

Examples

=IF(COUNTIF(C2,"L*"),"YES",XLOOKUP($C2,'database'!$A$1:$A$65536,'database'!$Q$1:$Q$65536))

Date	Formula	Item #
1-Jan-22	2108009C	26440721
2-Jan-22	YES	LAB12645
3-Jan-22	YES	LAB12345
4-Jan-22	2106011C	26393028

 

[Afro_Cookie]

So as an update. I've populated a large IFS() statement but left out the references to other files I'd have to pull to make it work. The formula is long and ugly, but functional. Can someone think of a cleaner solution either in vba or directly in my sheet?

"Sticker", "Lid", "Box", "Number" will all be replaced with references to other workbooks and sheets to pull the correct data.

Defect Report playground.xlsm
	B	C
1	A	B
2	Number	1
3	Sticker	Lab
4	Box	CON
5	Lid	LID
Data

Cell Formulas
Range	Formula
B2:B5	B2	=IFS(COUNTIF(C2,"LAB*"),"Sticker",COUNTIF(C2,"LI*"),"Lid",COUNTIF(C2,"CON*"),"Box",C2>0,"Number")