vlookup with conditions and return from different text length.

[buroh]

Dear all,

bare with me.

I have a spreadsheet with two sheets:
Sheet 1: called Data (where data is input into column A. column B will return a value from vlookup of sheet 2
Sheet 2: called roles.

Column B in Sheet 1 will need to return the value of column B in Sheet 2 based on a lookup condition in Sheet 1, so vlookup should be simple right? Nope, not for my data. The criteria is to find the sample of text within the document number (column A of Sheet 1), the text will not always be in the same position in the document number because the document number maybe different lengths, hence the use of MID formula won't always work properly.

My current formula is: =IF($A2="","",IF(ISNUMBER(FIND("-",MID($A2,25,3))),VLOOKUP(MID($A2,25,1),roles!$A$1:$C$63,2,FALSE),VLOOKUP(MID($A2,25,3),roles!$A$1:$C$63,2,FALSE)))

Sample document numbers:
DG-NCD-400-0000-AYS-DWG-AVS-000-0209002
DG-NCD-411-0000-BHD-DWG-FP-2L0-0310202
FSE-BHE-CT-B1-DR-U-100023

The text in bold and red is what the formula would be looking for to match and vlookup.
So the length of text would be different as well.

Sample of roles from Sheet 2 (roles) columns A and B:
-AVS- AV/IT
-FP- FIRE PROTECTION
-ICT- TELECOMMUNICATIONS
-P- PH
-U- SECURITY

So any ideas on how can I accomplish a return result from column C of Sheet 2?

 

[Fluff]

How about

+Fluff 1.xlsm
	A	B	C	D	E
1
2	-AVS-	AV/IT		DG-NCD-400-0000-AYS-DWG-AVS-000-0209002	AV/IT
3	-FP-	FIRE PROTECTION		DG-NCD-411-0000-BHD-DWG-FP-2L0-0310202	FIRE PROTECTION
4	-ICT-	TELECOMMUNICATIONS		FSE-BHE-CT-B1-DR-U-100023	Security
5	-P-	PH		FSE-BHE-CT-B1-DR-P-100024	PH
6	-U-	Security		FSE-BHE-CT-B1-DR-Z-100025	n/a
Main

Cell Formulas
Range	Formula
E2:E6	E2	=LET(Mx,MAX(IFNA(MATCH("-"&FILTERXML("<k><m>"&SUBSTITUTE(D2,"-","</m><m>")&"</m></k>","//m")&"-",$A$2:$A$6,0),"")),IF(Mx=0,"n/a",INDEX($B$2:$B$6,Mx)))

 

[buroh]

How about

+Fluff 1.xlsm
	A	B	C	D	E
1
2	-AVS-	AV/IT		DG-NCD-400-0000-AYS-DWG-AVS-000-0209002	AV/IT
3	-FP-	FIRE PROTECTION		DG-NCD-411-0000-BHD-DWG-FP-2L0-0310202	FIRE PROTECTION
4	-ICT-	TELECOMMUNICATIONS		FSE-BHE-CT-B1-DR-U-100023	Security
5	-P-	PH		FSE-BHE-CT-B1-DR-P-100024	PH
6	-U-	Security		FSE-BHE-CT-B1-DR-Z-100025	n/a
Main

Cell Formulas
Range	Formula
E2:E6	E2	=LET(Mx,MAX(IFNA(MATCH("-"&FILTERXML("<k><m>"&SUBSTITUTE(D2,"-","</m><m>")&"</m></k>","//m")&"-",$A$2:$A$6,0),"")),IF(Mx=0,"n/a",INDEX($B$2:$B$6,Mx)))

Thank you, that worked for me.

Best Wishes.

 

[Fluff]

You're welcome & thanks for the feedback.