Returning multiple names in Excel

I'm working on some portfolio data and trying to return a series of names from a look up. I was thinking about using INDEX/MATCH or some kind of nested IF statements. In the spreadsheet, portfolio names are the rows and loan numbers are in the columns although after closer examination I should probably switch this). I've allocated these loans to each portfolio based on pre-selected criteria, and I'm trying to return all of the portfolio names if they've had a loan allocated to them, eg:

   Port 1         Port 2      Port 3    Portfolio with appetite
Loan 1  $10M        $0M        $5M         1, 3

Loan 2  $0M         $20M       $15M        2, 3

Loan 3  $2M         $7M        $0M         1, 2

Any help is appreciated. Thanks.

2 answers

  • answered 2018-10-11 20:01 Solar Mike

    If the data is in cells A1 to D4 and is just the numbers, then

    =INDEX(B$1:D$1,MATCH(LARGE(B2:D2,1),B2:D2,0))&", "&INDEX(B$1:D$1,MATCH(LARGE(B2:D2,2),B2:D2,0))
    

    should do entered in E2, and just drag down. enter image description here

  • answered 2018-10-11 22:53 Brent

    It's not the prettiest function but it should work.

    =SUBSTITUTE(SUBSTITUTE((COLUMNS($B$8:B8)*(B9>0))&","&(COLUMNS($B$8:C8)*(C9>0))&","&(COLUMNS($B$8:D8)*(D9>0)),"0,",""),",0","")
    

    This will cover all scenarios, including if all three portfolios have a balance.

    You'll have to expand it for more columns adding additional "(COLUMNS(...)*(...))" pieces for each column. If you have a lot of columns, you might be better to do this via VBA.