Vlookup and Match-Need VBA solution

ravik

New Member
Joined
May 18, 2020
Messages
5
Office Version
2016
Platform
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
 

Some videos you may like

Excel Facts

Excel Can Read to You
Customize Quick Access Toolbar. From All Commands, add Speak Cells or Speak Cells on Enter to QAT. Select cells. Press Speak Cells.

mikerickson

MrExcel MVP
Joined
Jan 15, 2007
Messages
23,769
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.
 

jasonb75

Well-known Member
Joined
Dec 30, 2008
Messages
10,629
Office Version
2019
Platform
Windows
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.
 

mikerickson

MrExcel MVP
Joined
Jan 15, 2007
Messages
23,769
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.
 

ravik

New Member
Joined
May 18, 2020
Messages
5
Office Version
2016
Platform
Windows

ADVERTISEMENT

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
 

mikerickson

MrExcel MVP
Joined
Jan 15, 2007
Messages
23,769
" 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:

ravik

New Member
Joined
May 18, 2020
Messages
5
Office Version
2016
Platform
Windows

ADVERTISEMENT

" 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,
 

jasonb75

Well-known Member
Joined
Dec 30, 2008
Messages
10,629
Office Version
2019
Platform
Windows
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.
 

ravik

New Member
Joined
May 18, 2020
Messages
5
Office Version
2016
Platform
Windows
Thank You. But Please support for my issue and show me an alternative in VBA Function
 

jasonb75

Well-known Member
Joined
Dec 30, 2008
Messages
10,629
Office Version
2019
Platform
Windows
All you need to do is change $H2:$H100 to $H$2:$H$100 in the formulas, then you can drag them down properly.
 

Subscribe on YouTube

Watch MrExcel Video

Forum statistics

Threads
1,106,178
Messages
5,509,630
Members
408,745
Latest member
YaroslavZ

This Week's Hot Topics

  • Turn fraction around
    Hello I need to turn a fraction around, for example I have 1/3 but I need to present as 3/1
  • TIme Clock record reformatting to ???
    Hello All, I'd like some help formatting this (Tbl-A)(Loaded via Power Query) [ATTACH type="full" width="511px" alt="PQdata.png"]22252[/ATTACH]...
  • TextBox Match
    hi, I am having a few issues with my code below, what I need it to do is when they enter a value in textbox8 (QTY) either 1,2 or 3 the 3 textboxes...
  • Using Large function based on Multiple Criteria
    Hello, I can't seem to get a Large formula to work based on two criteria's. I can easily get a oldest value based one value, but I'm struggling...
  • Can you check my code please
    Hi, Im going round in circles with a Compil Error End With Without With Here is the code [CODE=rich] Private Sub...
  • Combining 2 pivot tables into 1 chart
    Hello everyone, My question sounds simple but I do not know the answer. I have 2 pivot tables and 2 charts that go with this. However I want to...
Top