If cell value equals `n in table return value from list of cell to the right of it

[KyleJackMorrison]

Hello,

I have a document where it holds all the information about the companies assets. I need to re-do the asset barcodes with our new naming convention.
(AO-B001 = "Awesome Company" - "Office" - "Class B" - "001")

I'm trying to automate the asset numbers and require a formula to search the asset table > classification column, reference it to the classification types on a different page and return the cell next to it with the classification letter.

Asset Table:

Asset ID	Classification	Manufacture	Model
<Formula Needed>	PC	Dell	OptiPlex
<Formula Needed>	Phone	iPhone	6s
<Formula Needed>	Monitor	Dell	Wide Boii
<Formula Needed>	PC	Dell	OptiPlex 3040

Classifications List on sheet "Settings":

Class	Letter
Phone	A
PC	B
Monitor	C
Fridge	D

So the outcome for the asset ID's will be:

Asset ID	Classification	Manufacture	Model
AO-B001	PC	Dell	OptiPlex
AO-A001	Phone	iPhone	6s
AO-C001	Monitor	Dell	wide boii
AO-B002	PC	Dell	OptiPlex 3040

I just require the code to assign the classification letter. Don't worry about the Company name, Location or the numbering.

Hope this makes sense.

Kind regards,
Kyle

 

[jtakw]

Hi,

Do you mean this:

Book3.xlsx
	A	B	C	D
1	Asset ID	Classification	Manufacture	Model
2	AO-B001	PC	Dell	OptiPlex
3	AO-A001	Phone	iPhone	6s
4	AO-C001	Monitor	Dell	wide boii
5	AO-B002	PC	Dell	OptiPlex 3040
6
7
8	Class	Letter
9	Phone	A
10	PC	B
11	Monitor	C
12	Fridge	D
Sheet7

Cell Formulas
Range	Formula
A2:A5	A2	=IFERROR("AO-"&LOOKUP(2,1/SEARCH(" "&B2&" "," "&A$9:A$12&" "),B$9:B$12)&TEXT(COUNTIF(B$2:B2,B2),"000"),"")

 

[jtakw]

Alternate formula if your Lookup table contains Exact matches only:

Book3.xlsx
	A	B	C	D
1	Asset ID	Classification	Manufacture	Model
2	AO-B001	PC	Dell	OptiPlex
3	AO-A001	Phone	iPhone	6s
4	AO-C001	Monitor	Dell	wide boii
5	AO-B002	PC	Dell	OptiPlex 3040
6
7
8	Class	Letter
9	Phone	A
10	PC	B
11	Monitor	C
12	Fridge	D
Sheet7

Cell Formulas
Range	Formula
A2:A5	A2	=IFERROR("AO-"&VLOOKUP(B2,A$9:B$12,2,0)&TEXT(COUNTIF(B$2:B2,B2),"000"),"")

 

[KyleJackMorrison]

Alternate formula if your Lookup table contains Exact matches only:

Book3.xlsx
	A	B	C	D
1	Asset ID	Classification	Manufacture	Model
2	AO-B001	PC	Dell	OptiPlex
3	AO-A001	Phone	iPhone	6s
4	AO-C001	Monitor	Dell	wide boii
5	AO-B002	PC	Dell	OptiPlex 3040
6
7
8	Class	Letter
9	Phone	A
10	PC	B
11	Monitor	C
12	Fridge	D
Sheet7

Cell Formulas
Range	Formula
A2:A5	A2	=IFERROR("AO-"&VLOOKUP(B2,A$9:B$12,2,0)&TEXT(COUNTIF(B$2:B2,B2),"000"),"")

Perfect thank you very much! Even adds the numbers!

 

[jtakw]

You're welcome, thanks for the feedback.