Index Match Formula

[nikhil0311]

Formula.xlsx
	A	B	C	D	E	F	G	H	I	J	K	L
1				Output
2	Sr No.	ID	Name	Card	Receivables	Payables
3	1	1456; 6678; 7654; 6590	Mikel A; Harry k; Henry C; Lauren T	Mikel A	Harry K	-				Mapping Table
4	2	2234;14567; 2780	Thomas P; Declan R; Pankaj J	Declan R; Pankaj J	-	Thomas P				ID	Name	Product
5	3	4563; 8532; 76558	Emile R	-	Emile R	-				1456	Mikel A	Card
6	4	77943	Ricky P	-	-	-				6678	Harry K	Receivables
7										7654	Henry C	Receivables
8										2234	Thomas P	Payables
9										14567	Declan R	Card
10										4563	Emile R	Receivables
11										2780	Pankaj J	Card
Sheet1

Hello People, Looking for your expertise in creating an excel formula.

1. Column A,B & C has raw data. Column D,E & F is the final output I am looking for based on the mapping table present in column J,K & L
2. So if you look at Sr. no 1, here we have 4 IDs in column B and their respective names in column C. Based on the name & product mapping present in column J, K & L, I want to segregate the names as per respective products in Output column D,E, F.
3. If any name and product combination is not present in mapping table, it should put "-" in the output columns.
4. If multiple names are present for the same product, it should keep both names in output (refer sr.no 2 Card example where (Declan R & Pankaj J) are present.

Thank you in advance for your help

 

[Georgiboy]

Are you using Excel 2013 or have you updated to 365?

 

[nikhil0311]

Hello,
I am using Microsoft Excel 2013

 

[nikhil0311]

Hello team,
Just wondering if anyone got a solution for this requirement please!!

 

[Georgiboy]

If you are willing to use a UDF, then the below may help:

Function GetData(idRng As Range, nameRng As Range, typeRng As Range, tblRng As Range) As Variant
    Dim idVar As Variant, x As Long, z As Long
    Dim nameVar As Variant
    Dim oVar() As Variant
    
    idVar = Split(Replace(idRng, " ", ""), ";")
    nameVar = Split(Replace(nameRng, " ", ""), ";")
    
    For x = 0 To UBound(idVar)
        If Application.CountIfs(tblRng.Columns(1), idVar(x), tblRng.Columns(3), typeRng) > 0 Then
            ReDim Preserve oVar(z)
            oVar(z) = nameVar(x)
            z = z + 1
        End If
    Next x
    GetData = Join(oVar, "; ")
End Function

Used in the sheet as below:

Book1
	A	B	C	D	E	F	G	H	I	J	K	L
1				Output
2	Sr No.	ID	Name	Card	Receivables	Payables
3	1	1456; 6678; 7654; 6590	Mikel A; Harry k; Henry C; Lauren T	MikelA	Harryk; HenryC					Mapping Table
4	2	2234;14567; 2780	Thomas P; Declan R; Pankaj J	DeclanR; PankajJ		ThomasP				ID	Name	Product
5	3	4563; 8532; 76558	Emile R		EmileR					1456	Mikel A	Card
6	4	77943	Ricky P							6678	Harry K	Receivables
7										7654	Henry C	Receivables
8										2234	Thomas P	Payables
9										14567	Declan R	Card
10										4563	Emile R	Receivables
11										2780	Pankaj J	Card
Sheet1

Cell Formulas
Range	Formula
D3:F6	D3	=GetData($B3,$C3,D$2,$J$5:$L$11)

 

[nikhil0311]

This is great! I really appreciate your help, Georgiboy.

 

[Georgiboy]

You are welcome, thanks for the feedback.