Multi variable Large/Small

[coinbank]

This gets a bit complex to explain but i will try. I have also attached a sample spreadsheet.

In the data tab i would like to have the data validation for Column D based on the results of Column I:N, which is ultimately decided by colum A which is input by the user. The goal is that the user enters which company they are attempting to do a partner distribution to and column D only gives them a list of the partners of THAT company. In columns I:N i have started the formula to determine how many partners there are for each company. The "new partner" text is the spot in the formula to put formula i am asking yall for.


The goal is to resolve the partner's name by looking up which company the user is distributing for and any person who owns greater than 0% of the company. However this can yield anywhere from 1 - 6 names, hence the need for a multi variable Large or small. If you look on the row 1 of the ownership tab i added a unique number above each partner so i can get the number from the large formula and then use that to hlookup the partner's name

On the "Ownership" tab i have each company listed, which is the Unique ID, no duplicates (Column A). Row 3 signifies the partners and where there is a number >0 represents that partner's ownership of that entity. Here is the what i am looking for:

in the Data Tab
Cell I2 "Mike Smith"
Cell J2 "Bob Jones"
Cell K2 [null]
Cell I4 "Mary"
Cell J4 "Jane"
Cell K4 "Kenny"
Cell L4 ""Jeff"
Cell M4 "Mady"

[coinbank]

i got my answer.....

{LARGE(IF(('Ownership Schedule'!$A$4:$A$107=Data!$A3)*('Ownership Schedule'!$D$4:$AB$107>0),'Ownership Schedule'!$D$1:$AB$1),Data!J$2)}

I just messed up on the syntax but now it works....

thanks

[Tsjallie]

Try this one in I2 and copy right and down.

Please Login or Register  to view this content.

[coinbank]

Thanks Tsjallie,

I haven't checked to see if your worked, however mine above did. I just had the syntax wrong on the array Large If. Once i hit myself in the head the syntax became obvious.....the issue was that no matter how i narrowed it down i was left with at least 2 choices that were "identical" and therefore any formula wouldn't know which variable to put in the cell, plus it wouldn't be able to select the next one in the adjacent cell, which is why i added the unique numbers for the partners and then used an hlookup to resolve for the name.

I appreciate the help but offsets and index/matches have never made sense in my brain, seem to have a mental block with them