Vlookup and Match-Need VBA solution

[ravik]

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
	A	B	C	D	E	F	G	H	I	J
1	Title	Match Values-Based on Coulmn H	Sub Gr1	Sub Gr2				Look up values	Sub Gr1	Sub Gr2
2	Implementing Business Intelligence with SQL Server 2019	Business Intelligence,sql Server	A,B	C-11001,C-11009				Business Intelligence	A	C-11001
3	Hands-On Natural Language Processing with Pytorch	Natural Language Processing, Py torch	B,A	C-11002,C-11010				Natural Language Processing	B	C-11002
4	Natural Language Processing in Practice	Natural Language Processing	B	C-11002				jetpack	A	C-11003
5	Android Jetpack Architecture Components	jetpack,andriod	A,C	C-11003,C-11013				Raspberry	B	C-11004
6	Real-World Projects with Flutter	flutter-projects	A	C-11008				with R	C	C-11005
7	Learn Qlik Sense Development	Qlik sense-development	C	C-11004				NLP	C	C-11006
8	Raspberry Pi Bootcamp	Raspberry	B	C-11004				Qlik sense,development	C	C-11007
9	Quantitative Finance with R	with R	C	C-11005				flutter,Projects	A	C-11008
10	Natural Language Processing (NLP) in Practice	Natural Language Processing,NLP	B,C	C-11002,C-11006				sql server	B	C-11009
11	Learn Qliksense Fundamentals	Qlik sense	A	C-11014				py torch	A	C-11010
12	Learn C and C++ Fundamentals	C,c++	A,C	C-11011,C-11012				C	A	C-11011
13								c++	C	C-11012
14								andriod	C	C-11013
15								Qlik sense	A	C-11014
Sheet1

 

[ravik]

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

@mikerickson Please help with the above changes to the code

 

[jasonb75]

You missed something in Mike's earlier reply, which is effectively saying that you want the impossible.

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

By editing the code to do what you want with 'qlik sense', you will be creating additional matches that shouldn't exist becasue 'C' in H12 will be a match for the c in the word Intelligence.

You will need to fix your list in column H as Mike suggested in order to make the function work properly.

 

[mikerickson]

About A6 "Real-World Projects with Flutter"

I don't understand how it is to match with "flutter, Projects" since (after removing spaces) "flutterprojects" is not part of the string in A6.
Neither do I understand how "flutter-projects" is supposed to be returned in Column C.

 

[mikerickson]

This is a relatively straight forward routine, once the data, both the data in column A and especially the data in the right hand column are clean.