Hi Team, I have two excel files with tables. First Book contains below data: ItemsA,B,CD,E,FG,H,I Second Book contains below data : ItemsValuesX,Y,A,B,F,K1X,Y,A,B,C,F,K2L,M,N,D,E,X,Y3L,M,N,D,E,F,Z,F,K4X,Y,G,H,D,E5X,Y,G,H,I,L,M6 I want to search book one items in the book two and if all items in the cell matches then get the respective value like v lookup. Final output should looks like ItemsValuesA,B,C2D,E,F4G,H,I6 Kindly help me in resolving the scenario.

vlookup or searching comma separated values in another comma separated values

Pragnya

Hi Team,

I have two excel files with tables.

First Book contains below data:

Items

A,B,C

D,E,F

G,H,I

Second Book contains below data :

Items	Values
X,Y,A,B,F,K	1
X,Y,A,B,C,F,K	2
L,M,N,D,E,X,Y	3
L,M,N,D,E,F,Z,F,K	4
X,Y,G,H,D,E	5
X,Y,G,H,I,L,M	6

I want to search book one items in the book two and if all items in the cell matches then get the respective value like v lookup.

Final output should looks like

Items	Values
A,B,C	2
D,E,F	4
G,H,I	6

Kindly help me in resolving the scenario.

Developer

Developer Tools

Accepted answers

All comments

Jean-Balteryx

Hi @Pragnya ,

Here is a workflow doing what you need !

It assigns a RecordID to each row then splits them to get one character per row. It joins to your reference to check if the value is present then group to count the number of values present for each recordID. It then joins to the total count of value for each row and retrieve the row if all values are present in the reference.