Help needed in collating two lookup data

[Saurabh_S]

I have two lookup user.csv and roles.csv, I'm trying to collate both tables and make a table which shows indexname and username which are using these index. It's getting difficult for me to get accurate data as "splunk_user" is common for all the username and it shows up while I run below query - 
|inputlookup user.csv | lookup roles.csv roles outputnew indexes | table indexes , username

Can someone please help in getting this query right? OR any alternate solution to find all the index and users using those index.

user.csv - "splunk_user" is common for all the username.

username,roles
abc,"splunk_user index2_user"
def,"splunk_user"
xyz,"splunk_user index1_power"
klm,"splunk_user"
pqr,"splunk_user index2_power"

roles.csv

roles,indexes
"splunk_user","index_all index_3 index_4 index_5"
"index1_user","index_1"
"index1_power","index_1"
"index2_user","index_2"
"index2_power","index_2"

[renjith_nair · Accepted solution]

Try this

|inputlookup users.csv|makemv roles|mvexpand roles|lookup roles.csv roles OUTPUT indexes|stats values(indexes)  by username

[Saurabh_S]

@renjith_nair Thanks for the quick help.