VBA VLookup a number in a text string within cells range

Hello!

I am encountering an issue I can't solve. I spent few hours trying to figure out but still nothing.

I have a datatable in one tab with like column A list of numbers (123456; 789456; 456132; etc.) that are corresponding to a department name (purchasing, production, quality, etc.) column B.

I have in another tab "report" a list of those numbers but they are included in a serie of other numbers like for instance 001-123456-008; 001-789456-008; etc.).

I am trying to look up within the tab report the number in the middle and match them to my datatable. The 001 and the 008 does not interest me.

I tried
Range("B1:B20") = Application.WorksheetFunction.VLookup(Sheets("Report").Range("A1:A20"), Sheets("DataTableGL").Range("C1:D20"), 2, False) and it works when I remove manually the 001 at the beginning and the 008 at the end. Yet I have thousands of lines ...

So I tried to Vlookup the value in the middle ... but does not work.
Range("B1:B20") = Application.WorksheetFunction.VLookup(Sheets("Report").Range("A1:A20"), Sheets("DataTableGL").Range(Mid(Cells(1, 1), 5, 6), 2, False))

I tried to extract the text and separate everything in several columns but the file gets slow and heavy.

If someone has an idea, I would be more than happy to try things!

Thank you for your help,

Ronan

Attach a sample workbook. Make sure there is just enough data to demonstrate your need. Include a BEFORE sheet and an AFTER sheet in the workbook if needed to show the process you're trying to complete or automate. Make sure your desired results are shown, mock them up manually if necessary.

Remember to desensitize the data.

Click on GO ADVANCED and use the paperclip icon to open the upload window.

View Pic

Cant help with the VBA, but if your dept number is consistent with your sample, , this will extract it from that sample...
=--MID(A1,5,6)

You could then use that inside your vlookup (or index/match)

Give this (attached) a whirl

Or:
In Report Sheet Cell D2: =VLOOKUP(MID(C2,5,6)*1,DataTableGL!$C$2:$F$32,2)
In Report Sheet Cell E2: =VLOOKUP(MID(C2,5,6)*1,DataTableGL!$C$2:$F$32,3)
Change the Cell references as required.

Copy on down

Hello Ronanus43,

Welcome to the Forum!

The attached workbook has the macro below added to it. There is a button "Run" on the "Report" which executes the macro.

Please Login or Register  to view this content.