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.
If the data is in cells A1 to D4 and is just the numbers, then
It's not the prettiest function but it should work.
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.