vlookup list all duplicates

[jamesbrightwell]

Hello,

I have been searching all day to find an appropriate formula (I assume INDEX) to help me with my current issue with no luck. I am on a strict deadline so if anyone can help me that would be great. What I am trying to do is re-work my vlookup formula to allow for multiple "hits" (I will not ever know how many "duplicates") The data I have in the below chart is for columns B,C,D and E. I am using a vlookup to populate F,G,H and I.

B	C	D	E	F	G	H	I
Name	Type	Own %	Ownership of	Own % (2)	Ownership (2)	Own % (3)	Ownership (3)
Alphabet Inc	Prime Entity	100%	Alphabet Inc	100%	Alphabet Inc
ABC	Entity	30%	Alphabet Inc	100%	Alphabet Inc
XYZ	Entity	60%	Alphabet Inc	100%	Alphabet Inc
Matt	Individual	50%	ABC	30%	Alphabet Inc
Mark	Individual	50%	ABC	30%	Alphabet Inc
Lisa	Individual	60%	XYZ	70%	Alphabet Inc
Joan	Individual	40%	XYZ	70%	Alphabet Inc
EFG	Entity	10%	Alphabet Inc	100%	Alphabet Inc
ABC	Entity	100%	EFG	10%	Alphabet Inc
Matt	Individual	50%	ABC	100%	EFG	10%	Alphabet Inc
Mark	Individual	50%	ABC	100%	EFG	10%	Alphabet Inc

<tbody>
</tbody>

The vlookup I am using is =vlookup(E2,$B$1:$E$34,3,FALSE) to populate column F and to populate column G =vlookup(E2,$B$1:$E$34,4,FALSE)

The last three entries in bold is what it SHOULD say but my vlookup will not return the values as listed as it is the 2nd time the values in column E are used so it will just find the first result. Can someone please help me modify my vlookup formula into an index formula (if that is the way to go) and allow me to stretch the data across the columns like I have listed?

 

[Akuini]

I can’t understand your explanation.
What do you mean by ‘to populate column F and to populate column G’? your formula doesn’t get any data from F & G.
The last 3 row, that is the result that you want?
Using your sample, what is the complete result that you want?

 

[jamesbrightwell]

Hello Akuini,

Thank you for your response, you are right I guess I wasn't super clear. The formula I have written will be copied into H and I columns as well (it actually goes all the way to column BA in my data) What I am trying to do is show at what percentage each entity and individual owns the entity listed. In my example ABC owns 30% of Alphabet Inc. Matt owns 50% of ABC which owns 30% of Alphabet Inc. I need to show these "connections" so to speak so I can calculate (through the percentages owned) just how much of each entity the individual / entity actually owns. My raw data is only provided as listed from columns B,C,D and E - the rest is formula based.

The problem with Vlookup is that it only gives me the first result, so on the second last line where it has:

Matt Individual 50% ABC 100% EFG 10% Alphabet Inc. The Vlookup will actually give me the result of Matt Individual 50% ABC 30% Alphabet Inc. The formula will miss that ABC owns 100% of EFG and EFG owns 10% of Alphabet Inc. What I have in bold is what it SHOULD show but a vlookup won't get me that result. The formula will need to be in column F, G, H, I, J etc. Once I have the percentages listed out as above in each row I am able to complete an additional calculation (I don't need help on that one) that allows me to calculate the ownership percentages correctly.

 

[Akuini]

Just to be clear, so the last 3 lines (bold font) is not the raw data, right?
On the third last line:

ABC

Entity

100%

EFG

10%

Alphabet Inc

<tbody>
</tbody>

I read ‘ABC owns 100% EFG’, but I can’t find the data on the data above it (the raw data), where is it come from?

 

[jamesbrightwell]

Columns B, C, D and E is ALL RAW data. I have bolded the line for emphasis. F, G, H and I is data from the formula. The vlookup won't return the data I have shown in F, G, H and I as one of the names is repeated. What the formula is essentially doing is looking in the Ownership of column (E) and listing what percentage it owns OWN % column (D) for Name (column B) As the entity ABC is listed to own 30% of Alphabet Inc that is what the vlookup formula is showing. But in the first line that is bolded you also see that ABC owns 100% of EFG and in the line above you see that EFG owns 10% of Alphabet Inc. So the second bolded line in columns G, H and I need to show like I have in the data example. What actually happens is the formula finds the 1st instance of ABC owning 30% of Alphabet Inc and stops showing anything else after that. The vlookup isn't able to look past the first time ABC is listed and list the 2nd time to show to ownership connection with EFG. I need a formula that can show the right connections and not just duplicate the first result it finds (which I know that is all a vlookup can do)

 

[Akuini]

Ok, I understand what you want.
But unfortunately, after thinking for a while I still can not find a solution the problem.
Hope anyone else here can help.

 

[jamesbrightwell]

Thank you for your time, I certainly hope someone can help me. So far 85 views and only 1 response so I'm not feeling that confident...

 

[FDibbins]

VLOOKUP will always ONLY return the 1st "find" - after that, it stops looking.

You could maybe try this...
=vlookup(E2,$B1:$E$34,3,FALSE)
Note I removed a $ from $B$1

Or, if that doesnt work for you, I will work on an INDEX/SMALL/IFF array formula for you

 

[Marcelo Branco]

Maybe this...

F2
=LOOKUP(2,1/($B$2:$B2=$E2),D$2:D2)
copy down and across to column G

Hope this helps

M.

 

[jamesbrightwell]

Hi FDibbins,

That didn't seem to work but Marcelo just posted something that through my early testing IS working. Hold off on doing that index/small/iff array until I can confirm a few things with Marcelo. Thank you for your response