vlookup with conditions and return from different text length.

buroh

New Member
Joined
Jul 14, 2020
Messages
34
Office Version
  1. 365
Platform
  1. Windows
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?
 

Excel Facts

Enter current date or time
Ctrl+: enters current time. Ctrl+; enters current date. Use Ctrl+: Ctrl+; Enter for current date & time.

Fluff

MrExcel MVP, Moderator
Joined
Jun 12, 2014
Messages
73,430
Office Version
  1. 365
Platform
  1. Windows
How about
+Fluff 1.xlsm
ABCDE
1
2-AVS- AV/ITDG-NCD-400-0000-AYS-DWG-AVS-000-0209002 AV/IT
3-FP- FIRE PROTECTIONDG-NCD-411-0000-BHD-DWG-FP-2L0-0310202 FIRE PROTECTION
4-ICT- TELECOMMUNICATIONSFSE-BHE-CT-B1-DR-U-100023Security
5-P- PHFSE-BHE-CT-B1-DR-P-100024 PH
6-U-SecurityFSE-BHE-CT-B1-DR-Z-100025n/a
Main
Cell Formulas
RangeFormula
E2:E6E2=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)))
 
Solution

buroh

New Member
Joined
Jul 14, 2020
Messages
34
Office Version
  1. 365
Platform
  1. Windows
How about
+Fluff 1.xlsm
ABCDE
1
2-AVS- AV/ITDG-NCD-400-0000-AYS-DWG-AVS-000-0209002 AV/IT
3-FP- FIRE PROTECTIONDG-NCD-411-0000-BHD-DWG-FP-2L0-0310202 FIRE PROTECTION
4-ICT- TELECOMMUNICATIONSFSE-BHE-CT-B1-DR-U-100023Security
5-P- PHFSE-BHE-CT-B1-DR-P-100024 PH
6-U-SecurityFSE-BHE-CT-B1-DR-Z-100025n/a
Main
Cell Formulas
RangeFormula
E2:E6E2=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

MrExcel MVP, Moderator
Joined
Jun 12, 2014
Messages
73,430
Office Version
  1. 365
Platform
  1. Windows
You're welcome & thanks for the feedback.
 
Master Excel Bundle

Excel contains over 450 functions, with more added every year. That’s a huge number, so where should you start? Right here with this bundle.

Forum statistics

Threads
1,164,586
Messages
5,838,230
Members
430,535
Latest member
krazykram

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top