How to combine a lookup table with a search with multiple entries?

[jturner900]

I'm trying to combine a lookup table:

Team  , Player 
A          , Malone
            , Stockton
B          , Jordan 
             ,Pippen

with a search:

Player,Mascots
Malone ,Jazz
                      ,Kings
                      ,Warriors
Stockton       ,Jazz
                      ,Lakers
Jordan           ,Bulls
                      , Heat
Pippen          , Bulls

I'd like the result to look like

Team ,  Player      ,     Mascot
A      ,     Malone     ,   Jazz
                            ,    ,   Kings
                            ,     ,  Warriors
A   ,      Stockton    ,  Jazz
                          ,       , Lakers
B      ,     Jordan     ,      Bulls
                         ,          ,  Heat
B       ,    Pippen         ,  Bulls

I normally use the following search: |inputlookup teamtable |join player [search index=playertable| table player], but it says 'No results found'. I think is has something to do with the fact that there are multiple row entries. Please help!!!

[somesoni2 · Accepted solution]

Give this a try (check field names, as they are case sensitive)

 |inputlookup teamtable | mvexpand player |join player [search index=playertable| table player]

If you've control over how the teamtable lookup is written, write it with single values of the players and teams, so that you could this much efficient version.

index=playertable| table player | lookup teamtable player