jamesbrightwell
New Member
- Joined
- Feb 23, 2015
- Messages
- 11
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.
[TABLE="width: 500"]
<tbody>[TR]
[TD]B[/TD]
[TD]C[/TD]
[TD] D[/TD]
[TD]E[/TD]
[TD] F[/TD]
[TD]G[/TD]
[TD]H[/TD]
[TD]I[/TD]
[/TR]
[TR]
[TD]Name[/TD]
[TD]Type[/TD]
[TD]Own %[/TD]
[TD]Ownership of[/TD]
[TD]Own % (2)[/TD]
[TD]Ownership (2)[/TD]
[TD]Own % (3)[/TD]
[TD]Ownership (3)[/TD]
[/TR]
[TR]
[TD]Alphabet Inc[/TD]
[TD]Prime Entity[/TD]
[TD]100%[/TD]
[TD]Alphabet Inc[/TD]
[TD]100%[/TD]
[TD]Alphabet Inc[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]ABC[/TD]
[TD]Entity[/TD]
[TD]30%[/TD]
[TD]Alphabet Inc[/TD]
[TD]100%[/TD]
[TD]Alphabet Inc[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]XYZ[/TD]
[TD]Entity[/TD]
[TD]60%[/TD]
[TD]Alphabet Inc[/TD]
[TD]100%[/TD]
[TD]Alphabet Inc[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Matt[/TD]
[TD]Individual[/TD]
[TD]50%[/TD]
[TD]ABC[/TD]
[TD]30%[/TD]
[TD]Alphabet Inc[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Mark[/TD]
[TD]Individual[/TD]
[TD]50%[/TD]
[TD]ABC[/TD]
[TD]30%[/TD]
[TD]Alphabet Inc[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Lisa[/TD]
[TD]Individual[/TD]
[TD]60%[/TD]
[TD]XYZ[/TD]
[TD]70%[/TD]
[TD]Alphabet Inc[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Joan[/TD]
[TD]Individual[/TD]
[TD]40%[/TD]
[TD]XYZ[/TD]
[TD]70%[/TD]
[TD]Alphabet Inc[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]EFG[/TD]
[TD]Entity[/TD]
[TD]10%[/TD]
[TD]Alphabet Inc[/TD]
[TD]100%[/TD]
[TD]Alphabet Inc[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]ABC[/TD]
[TD]Entity[/TD]
[TD]100%[/TD]
[TD]EFG[/TD]
[TD]10%[/TD]
[TD]Alphabet Inc[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Matt[/TD]
[TD]Individual[/TD]
[TD]50%[/TD]
[TD]ABC[/TD]
[TD]100%[/TD]
[TD]EFG[/TD]
[TD]10%[/TD]
[TD]Alphabet Inc[/TD]
[/TR]
[TR]
[TD]Mark[/TD]
[TD]Individual[/TD]
[TD]50%[/TD]
[TD]ABC[/TD]
[TD]100%[/TD]
[TD]EFG[/TD]
[TD]10%[/TD]
[TD]Alphabet Inc[/TD]
[/TR]
</tbody>[/TABLE]
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?
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.
[TABLE="width: 500"]
<tbody>[TR]
[TD]B[/TD]
[TD]C[/TD]
[TD] D[/TD]
[TD]E[/TD]
[TD] F[/TD]
[TD]G[/TD]
[TD]H[/TD]
[TD]I[/TD]
[/TR]
[TR]
[TD]Name[/TD]
[TD]Type[/TD]
[TD]Own %[/TD]
[TD]Ownership of[/TD]
[TD]Own % (2)[/TD]
[TD]Ownership (2)[/TD]
[TD]Own % (3)[/TD]
[TD]Ownership (3)[/TD]
[/TR]
[TR]
[TD]Alphabet Inc[/TD]
[TD]Prime Entity[/TD]
[TD]100%[/TD]
[TD]Alphabet Inc[/TD]
[TD]100%[/TD]
[TD]Alphabet Inc[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]ABC[/TD]
[TD]Entity[/TD]
[TD]30%[/TD]
[TD]Alphabet Inc[/TD]
[TD]100%[/TD]
[TD]Alphabet Inc[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]XYZ[/TD]
[TD]Entity[/TD]
[TD]60%[/TD]
[TD]Alphabet Inc[/TD]
[TD]100%[/TD]
[TD]Alphabet Inc[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Matt[/TD]
[TD]Individual[/TD]
[TD]50%[/TD]
[TD]ABC[/TD]
[TD]30%[/TD]
[TD]Alphabet Inc[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Mark[/TD]
[TD]Individual[/TD]
[TD]50%[/TD]
[TD]ABC[/TD]
[TD]30%[/TD]
[TD]Alphabet Inc[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Lisa[/TD]
[TD]Individual[/TD]
[TD]60%[/TD]
[TD]XYZ[/TD]
[TD]70%[/TD]
[TD]Alphabet Inc[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Joan[/TD]
[TD]Individual[/TD]
[TD]40%[/TD]
[TD]XYZ[/TD]
[TD]70%[/TD]
[TD]Alphabet Inc[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]EFG[/TD]
[TD]Entity[/TD]
[TD]10%[/TD]
[TD]Alphabet Inc[/TD]
[TD]100%[/TD]
[TD]Alphabet Inc[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]ABC[/TD]
[TD]Entity[/TD]
[TD]100%[/TD]
[TD]EFG[/TD]
[TD]10%[/TD]
[TD]Alphabet Inc[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Matt[/TD]
[TD]Individual[/TD]
[TD]50%[/TD]
[TD]ABC[/TD]
[TD]100%[/TD]
[TD]EFG[/TD]
[TD]10%[/TD]
[TD]Alphabet Inc[/TD]
[/TR]
[TR]
[TD]Mark[/TD]
[TD]Individual[/TD]
[TD]50%[/TD]
[TD]ABC[/TD]
[TD]100%[/TD]
[TD]EFG[/TD]
[TD]10%[/TD]
[TD]Alphabet Inc[/TD]
[/TR]
</tbody>[/TABLE]
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?