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?

 

[jamesbrightwell]

Hi Marcelo,

I have tested this through my actual data set and it appears to be WORKING. I am very happy. I just have one question so I can understand what it is that the formula is doing. In the formula =LOOKUP(2,1/($B$2:$B2=$E2),D$2:D2) Is the first part =LOOKUP(2,1/ meaning this is looking for the SECOND instance? Is that what the 2,1 is referring to? Would the same formula work if I had say 10 entities repeated with 10 different variations so it would need to find all 10 variables? I have copied the formula 50 times across the columns so it should pick up 50 variations if I can correct in how it works. This might seem silly but if you could explain how the formula is doing what it is that would help me. I know what it IS doing just not HOW it is doing it.

 

[Marcelo Branco]

No, no, the 2 in this construct acts as a BIGNUM (big number) since 1/($B$2:$B2=$E2) yields an array of 1s and errors.

Aladin Akyurek has provided an excellent explanation about this kind of formula. Take a look at
http://www.mrexcel.com/forum/excel-questions/99621-lookup-value-unsorted-data.html#post492425

M.

 

[jamesbrightwell]

Thank you so much, and also for providing the link to a detailed explanation. Consider this one solved people.

 

[Marcelo Branco]

You are welcome. Glad to help

M.