Excel vLookup Formula

Help Activities
1

I need to use vLookup formula in Column 3 to get the Column 1 data in Column3. Please note that the column 2 data patterns are dynamic. I have provided some sample patterns for your understanding. Can anyone help me to find the dynamic vlookup formula to handle all such patterns?

|Column 1 | Column 2 | Column 3 |
|10-111-222-55 | 10-111-222-55.abc.test.com | 10-111-222-55 |
|10-111-222-55 | abc.test.com.10-111-222-55 | 10-111-222-55 |
|10-111-222-55 | 10-111-222-55\abc.test.com | 10-111-222-55 |
|abcd1234 | abcd1234 | abcd1234 |
|xyz | abc_telecom\xyz | xyz |
|abc | abc.API.com | abc |

2

Hi @choudhuryanowar

=> Read Range Workbook
Output → dt

=> Use the below code in Invoke Code:

dt.AsEnumerable().ToList().ForEach(Sub(row)
    row("Column 3") = dt.AsEnumerable().FirstOrDefault(Function(r) row("Column 2").ToString.Contains(r("Column 1").ToString))("Column 1").ToString
End Sub)

Invoke Code Arguments
Name: dt
Direction: In/Out
Type: System.Data.DataTable

=> Write Rangae Workbook dt back to the same sheet.

Hope it helps!!