Vlookup and Match-Need VBA solution

ravik

New Member
Joined
May 18, 2020
Messages
5
Office Version
  1. 2016
Platform
  1. Windows
I am facing a problem with vlookup and i think could not solve using the Excel formula

The column H values to be checked in Column A and if the words in a cell match, then it should collect the adjacent values and display in Column C and D with comma seperated.

The column H values to be matched with multiple words in the cell aganist Column A, all matched words should be populated in column B.


Lookup and match.xlsx
ABCDEFGHIJ
1TitleMatch Values-Based on Coulmn HSub Gr1Sub Gr2Look up valuesSub Gr1Sub Gr2
2Implementing Business Intelligence with SQL Server 2019 Business Intelligence,sql ServerA,BC-11001,C-11009Business IntelligenceAC-11001
3Hands-On Natural Language Processing with Pytorch Natural Language Processing, Py torchB,AC-11002,C-11010Natural Language ProcessingBC-11002
4Natural Language Processing in Practice Natural Language ProcessingBC-11002jetpackAC-11003
5Android Jetpack Architecture Components jetpack,andriodA,CC-11003,C-11013RaspberryBC-11004
6Real-World Projects with Flutter flutter-projectsAC-11008with RCC-11005
7Learn Qlik Sense Development Qlik sense-developmentCC-11004NLPCC-11006
8Raspberry Pi BootcampRaspberryBC-11004Qlik sense,developmentCC-11007
9Quantitative Finance with R with RCC-11005flutter,ProjectsAC-11008
10Natural Language Processing (NLP) in Practice Natural Language Processing,NLPB,CC-11002,C-11006sql serverBC-11009
11Learn Qliksense Fundamentals Qlik senseAC-11014py torchAC-11010
12Learn C and C++ FundamentalsC,c++A,CC-11011,C-11012CAC-11011
13c++CC-11012
14andriodCC-11013
15Qlik senseAC-11014
Sheet1
 

Excel Facts

How to show all formulas in Excel?
Press Ctrl+` to show all formulas. Press it again to toggle back to numbers. The grave accent is often under the tilde on US keyboards.
I think this will do what you want.

In B2, put the formula =MultiSubstringVLOOKUP(B2, $H2:$H100, 1, ",")
in C2, put =MultiSubstringVLOOKUP(B2, $H2:$H100, 2, ",")
In D2, put =MultiSubstringVLOOKUP(B2, $H2:$H100, 3, ",") and drag these down

or in B2, put =MultiSubstringVLOOKUP(B2, $H2:$H100, Column(A1), ",") and drag right and down

VBA Code:
Function MultiSubstringVLOOKUP(lookup_value As String, table_array As Range, returnColumn As Long, Optional Delimiter As String = " ") As String
    Dim oneCell As Range
   
    With table_array
        Set table_array = Application.Intersect(.Parent.UsedRange, .Cells)
    End With
    lookup_value = " " & lookup_value & " "
   
    For Each oneCell In table_array.Columns(1).Cells
        If 0 < InStr(1, lookup_value, " " & oneCell.Text & " ", vbTextCompare) Then
            MultiSubstringVLOOKUP = MultiSubstringVLOOKUP & Delimiter & oneCell.Cells(1, returnColumn).Text
        End If
    Next oneCell
    MultiSubstringVLOOKUP = Mid(MultiSubstringVLOOKUP, Len(Delimiter) + 1)
End Function

One issue to take care of is to insure that the data is clean.
There is no entry in column H that matches A6, since "flutter,project" is not a sub-string of A6.
If you want to trigger for either flutter or project you would have to have two lines in the table_array, one for flutter, one for projects.
 
Upvote 0
One issue to take care of is to insure that the data is clean.
There is no entry in column H that matches A6, since "flutter,project" is not a sub-string of A6.
If you want to trigger for either flutter or project you would have to have two lines in the table_array, one for flutter, one for projects.
I was looking at the data sample provided and also noticed some inconsistency with 'Qlik sense' having a space in some cells and not in others. I haven't analysed Mike's code but I expect that this will also need to be corrected in the lookup values list.
 
Upvote 0
In column H, "Qlik sense, development" is in one cell and "Qlik sense" in another. Interpreting a comma as "or" doesn't resolve that issue.
The OP would have to clarify what is going on. Perhaps accepting wildcards in column H might be an approach.
 
Upvote 0
Thank so much for your effort.You are almost near to a solution

- The words in each cell should match by ignoring spaces and with non case sensitive.
Ex: Colum H row 15 "qlik sense" should match Column A row 11 "Qliksense"
- All the words with comma seperated in a each cell in column H should match with each cell Column A
- All words match aganist a cell,then it should suffix with a dash
ex: Qliksense-development (Column A with looking up value Column H Row 8)
Flutter-Projects(Colmn A Row 6 with looking up value Column H Row 9)
- Multiple words at different rows should be shown with comma seperated - values within Bracket missed in your function
ex:Natural Language Processing,NLP (Column H Row 3 and Row 7 aganist Column A Row 10)
ex:jetpack,andriod (Column H Row 4 and Row 14 aganist Column A Row 5)
- If no words are matched - Just display a text "Not found" in column B
 
Upvote 0
" The words in each cell should match by ignoring spaces and with non case sensitive. Ex: Colum H row 15 "qlik sense" should match Column A row 11 "Qliksense" "

Should "Implementing Business Intelligence with SQL Server 2019" in B2 match with "C" in H12?

Should A7 "Learn Qlik Sense Development " match with H8 "Qlik sense,development" or H15 "Qlik sense" or both?
 
Last edited:
Upvote 0
" The words in each cell should match by ignoring spaces and with non case sensitive. Ex: Colum H row 15 "qlik sense" should match Column A row 11 "Qliksense" "
- Yes, Your code already working on this solution

Should A7 "Learn Qlik Sense Development " match with H8 "Qlik sense,development" or H15 "Qlik sense" or both?
- Its my mistake.Sorry my OP is wrong.
- It should reflect Both "Qlik sense-development","Qlik sense"
- The code has to handle fixing "-" in between "Qlik sense and development"


I checked this formula i found its not giving expected output. Please consider unique values while populating it


If Possible Please help me a macro, While draging formula the cell references are not working properly.I am manually copy pasting in each row,
 
Upvote 0
Please help me a macro, While draging formula the cell references are not working properly.

'Macro' is not an excel panacea. You can fix the formula problem by making the rows absolute instead of relative.
 
Upvote 0
Thank You. But Please support for my issue and show me an alternative in VBA Function
 
Upvote 0
All you need to do is change $H2:$H100 to $H$2:$H$100 in the formulas, then you can drag them down properly.
 
Upvote 0

Forum statistics

Threads
1,213,543
Messages
6,114,238
Members
448,555
Latest member
RobertJones1986

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
Back
Top