VLOOKUP w/ Duplicate Values

J

jakulski

New Member
Joined
Jan 17, 2017
Messages
15
Hello, and thanks in advance for your help!
I have a workbook with many worksheets. Some worksheets have 25-30 columns and up to 45,000 rows of data. My dilemma follows...

In column "I" on the ENTRIES worksheet, I have a VLOOKUP formula I use, but I doesn't work properly if there is more than one occurrence of the data I'm comparing against. For example, the following array in I4 to retrieve text from P4 in "ALL" works, but only if the text in P is in the first row that the ID number occurs: =IFERROR(VLOOKUP($A4,ALL!$D:$Z,13,0),""). In other words, the array I have works just fine unless there are multiple occurrences of the ID# (column D in "Entries"). In other words, the formula captures what's in the P column only for the first occurrence of the matching ID#. If, for example, RFHO, is in column P of the eighth occurrence of the ID#, the corresponding cell in the I column in the "Entries" column will be blank. I need a formula that will look through column P in its entirety for the selected ID# (A4) and if there is an instance of text in P (RFHO, CCRE, EXCL), add it to corresponding row in Entries, in column I.

I don't see where I can upload anything so I'm including below a portion of each of the two worksheets: ENTRIES and ALL. The personally identifiable information of the individuals has been changed. Thank you in advance for your help!


A sample of the ALL worksheet
A
Lev		B
SchID		C
SchName		D
ID		E
FName		F
LName		G
Grade		H
Gender		I
Ethnic		J
Elig		K
GT_Stat		L
ELPLvl		M
RefDate		N
IncDate		O
IncCode		P
HOCase		Q
DaysOSS		R
DaysISSP		S
HrsAIA		T
PriOff		U
SecOff1		V
SecOff2		W
PriDispo		X
SecDispo		Y
Interven		Z
PriDispo & Interven
xxx100Sleepy Hollow High1671977GuillermoGuillermo11MWhite109/07/1609/07/16411429GA1PARNPARN
xxx100Sleepy Hollow High1378674Dylandylan09MAsian1009/13/1609/13/16411542DC3CSRVCSRV
xxx100Sleepy Hollow High1629869PragnaPragna12FAsian1009/20/1609/20/16411881CF1CONFCONF
xxx100Sleepy Hollow High1685014MarlonMarlon10MHispanic/ Latino209/30/1609/30/16412777DC5DC8WARNWARN
xxx100Sleepy Hollow High1685014MarlonMarlon10MHispanic/ Latino210/12/1610/12/164142071AT5AT2ISSPISSP
xxx100Sleepy Hollow High1711170LuisLuis-Luis10MHispanic/ Latino110/17/1610/16/164142513DC1DC2AIAAIA
xxx100Sleepy Hollow High1685014MarlonMarlon10MHispanic/ Latino210/27/1610/26/164156002AT5AT2ISSPISSP
xxx100Sleepy Hollow High1711170LuisLuis-Luis10MHispanic/ Latino110/26/1610/26/164160042DC1DC5AIAAIA
xxx100Sleepy Hollow High1405381LuisLuis09MBlack/ African American6d10/28/1610/28/164156261DC2AT2AT3ISSPISSP
xxx100Sleepy Hollow High1630478Fritz NicoleFritz Nicole09FBlack/ African American11/01/1611/01/16416063SC5PARNPARN
xxx100Sleepy Hollow High1711170LuisLuis-Luis10MHispanic/ Latino111/15/1611/15/164175233DC1DC2DC5AIAAIA
xxx100Sleepy Hollow High1711170LuisLuis-Luis10MHispanic/ Latino111/21/1611/21/164182001SC1ISSPISSP
xxx100Sleepy Hollow High1549105ArielAriel09MAsianLD311/29/1611/29/1641876521DC2DC5AT2ISSPAIAISSP
xxx100Sleepy Hollow High1685014MarlonMarlon10MHispanic/ Latino211/29/1611/29/16418890DC8WARNWARN
xxx100Sleepy Hollow High1685014MarlonMarlon10MHispanic/ Latino212/02/1612/02/164195902TI2ISSPISSP
xxx100Sleepy Hollow High1685014MarlonMarlon10MHispanic/ Latino212/05/1612/02/16419755DC9PARNWARNPARN
xxx100Sleepy Hollow High1405381LuisLuis09MBlack/ African American6d12/09/1612/09/164212132AT2AT3ISSPISSP
xxx100Sleepy Hollow High1405381LuisLuis09MBlack/ African American6d12/09/1612/09/164212162DC2DC3SUSTSUST
xxx100Sleepy Hollow High1702234DenisDenis10MBlack/ African American112/09/1612/09/164209892TI2DC2SUSTSUST
xxx100Sleepy Hollow High1405381LuisLuis09MBlack/ African American6d12/15/1612/13/16421679RFHO11BA7WP5SUSTSUST
xxx100Sleepy Hollow High1549105ArielAriel09MAsianLD312/15/1612/13/16421679RFHO13BA7SUSTSUST
xxx100Sleepy Hollow High1378674Dylandylan09MAsian1012/14/1612/14/16421343RFHO9BA6ISSPPARNISSP
xxx100Sleepy Hollow High1422469MariaMaria10FNative Hawaiian/ Other Pacific IslanderLD312/14/1612/14/164213861DC3DC6ISSPISSP
xxx100Sleepy Hollow High1711170LuisLuis-Luis10MHispanic/ Latino101/04/1701/04/17421773DC5WARNWARN
xxx100Sleepy Hollow High1711170LuisLuis-Luis10MHispanic/ Latino101/10/1701/09/174223242DC2DC5ISSPISSP
xxx100Sleepy Hollow High1685014MarlonMarlon10MHispanic/ Latino201/10/1701/10/17422318GA1SC1WARNWARN
xxx100Sleepy Hollow High1422469MariaMaria10FNative Hawaiian/ Other Pacific IslanderLD301/12/1701/12/174227753DC1DC2DC5AIAAIA
xxx100Sleepy Hollow High1702234DenisDenis10MBlack/ African American101/24/1701/24/17423697RFHO7DR8DP1TB2ISSPISSP
xxx100Sleepy Hollow High1711170LuisLuis-Luis10MHispanic/ Latino101/25/1701/25/174238512DC5AT5AIAAIA
xxx100Sleepy Hollow High1711170LuisLuis-Luis10MHispanic/ Latino101/25/1701/25/17423853AT5DETNDETN
xxx100Sleepy Hollow High1228770WilliamWilliam12MWhiteID01/30/1701/30/174247851SX0ISSPISSP
xxx100Sleepy Hollow High1685014MarlonMarlon10MHispanic/ Latino202/02/1702/01/17424973RFHO12BA7TI2GA1SUSTSUST
xxx100Sleepy Hollow High1702234DenisDenis10MBlack/ African American102/08/1702/08/17425364AT1AT3PARNWARNPARN
xxx100Sleepy Hollow High1685014MarlonMarlon10MHispanic/ Latino202/09/1702/09/17425498TR1POLNPARNPOLN
xxx100Sleepy Hollow High1531904LexLex12MWhite6a02/14/1702/10/174263301TR1AT5AT2ISSPISSP
xxx100Sleepy Hollow High1422469MariaMaria10FNative Hawaiian/ Other Pacific IslanderLD302/14/1702/14/1742605011DC1DC5CO2ISSPAIAISSP
xxx100Sleepy Hollow High1405381LuisLuis09MBlack/ African American6d01/25/1702/15/17426150CCRE0CICSUSTSUST
xxx100Sleepy Hollow High1711170LuisLuis-Luis10MHispanic/ Latino102/22/1702/22/174269101DC1DC2DC6ISSPISSP
xxx100Sleepy Hollow High1447387RicardoRicardo09MBlack/ African American303/03/1703/03/174285151AT3ISSPISSP
xxx100Sleepy Hollow High1711170LuisLuis-Luis10MHispanic/ Latino103/07/1703/03/174289131DC5SUSTSUST
xxx100Sleepy Hollow High1721462JulianJulian10MBlack/ African American03/08/1703/07/1742898712AT2ISSPISSP
xxx100Sleepy Hollow High1422469MariaMaria10FNative Hawaiian/ Other Pacific IslanderLD303/09/1703/09/174293543DC3AIAAIA
xxx100Sleepy Hollow High1431231NajelyNajely09FTwo or More Races403/03/1703/13/17429678CIC0
xxx100Sleepy Hollow High1711170LuisLuis-Luis10MHispanic/ Latino103/16/1703/16/174301361DC5SUSTSUST
xxx100Sleepy Hollow High1721462JulianJulian10MBlack/ African American03/21/1703/21/17430726RFHO8DR8DP1ISSPSEMISSP
xxx100Sleepy Hollow High1711170LuisLuis-Luis10MHispanic/ Latino103/22/1703/22/17431038SC5PARNPARN
xxx100Sleepy Hollow High1391366RosaRicardo11FHispanic/ Latino1003/23/1703/23/174310441AT5ISSPISSP
xxx100Sleepy Hollow High1447387RicardoRicardo09MBlack/ African American303/23/1703/23/174310502DC5AT2ISSPISSP
xxx100Sleepy Hollow High1422469MariaMaria10FNative Hawaiian/ Other Pacific IslanderLD303/30/1703/30/174321551DC1DC6ISSPISSP
xxx100Sleepy Hollow High1711170LuisLuis-Luis10MHispanic/ Latino103/29/1703/30/174319433DC3DC5ISSPISSP
xxx100Sleepy Hollow High1711170LuisLuis-Luis10MHispanic/ Latino104/18/1704/18/1743339514DC5DC6SUSTISSPSUST
xxx100Sleepy Hollow High1447387RicardoRicardo09MBlack/ African American304/20/1704/20/17433620AT1AT2DETNDETN

<tbody>
</tbody><colgroup><col style="text-align: center;"><col style="text-align: center;"><col style="text-align: center;"><col style="text-align: center;"><col style="text-align: center;"><col style="text-align: center;"><col style="text-align: center;"><col style="text-align: center;"><col style="text-align: center;"><col style="text-align: center;"><col style="text-align: center;"><col style="text-align: center;"><col style="text-align: center;"><col style="text-align: center;"><col style="text-align: center;"><col style="text-align: center;"><col style="text-align: center;"><col style="text-align: center;"><col style="text-align: center;"><col style="text-align: center;"><col style="text-align: center;" span="2"><col style="text-align: center;"><col style="text-align: center;"><col style="text-align: center;"><col style="text-align: center;"></colgroup>

​

A sample of the ENTRIES worksheet
A
ID#		B
# of Entries 		C
LName		D
FName		E
Gr		F
Ethnicity		G
EL Lev		H
SpecEd		I
HO?		J
Days ISS		K
Days OSS		L
Total ISS & OSS
171117014Luis-LuisLuis10White1 011314
16850149MarlonMarlon10Black/ African American2 051217
14053815LuisLuis09Asian6d 031316
14224695MariaMaria10Native Hawaiian/ Other Pacific Islander3LD0303
17022343DenisDenis10American Indian/ Alaska Native1 0729
14473873RicardoRicardo09Hispanic/ Latino3 0303
15491052ArielAriel09Hispanic/ Latino3LD021315
13786742dylanDylan09White10 0909
17214622JulianJulian10Native Hawaiian/ Other Pacific Islander 020020
16304781Fritz NicoleFritz Nicole09Black/ African American 0000
16719771GuillermoGuillermo11Black/ African American1 0000
15319041LexLex12White6a 0101
14312311NajelyNajely09Asian4 0000
16298691PragnaPragna12Asian10 0000
13913661RicardoRosa11Two or More Races10 0101
12287701WilliamWilliam12Two or More Races ID0101

<tbody>
</tbody><colgroup><col><col><col><col><col><col><col><col><col><col span="3"></colgroup>
 

Excel Facts

Format cells as time
Select range and press Ctrl+Shift+2 to format cells as time. (Shift 2 is the @ sign).
Sort by date Sort by votes
Upvote 0
Thank you, SeaTransplant. I tried it and it works perfectly for counting the number of occurrences of text in column P, matched to the ID#; however, what I need is to know which of the three was in the cell (RFHO, CCRE, or EXCL). On rare occasions there will be two occurrences for the one ID#, in which case I'm fine with whichever one Excel finds first. Most important to me is to know not the frequency, but the "what" (RFHO, CCRE, or EXCL). Does that make sense?
 
Upvote 0
Hi,

The easiest way is to create an additional concatenate column that combines a few columns so that all your rows are unique. Then use that to vlookup / index match. You want this column on both where you want to take from and where you're matching from.

Good luck!
 
Upvote 0
You must log in or register to reply here.

Similar threads

N
  • Solved
Find duplicate value on a set of criterias
Replies
7
Views
417
Fluff
Fluff
H
  • Solved
Grouping rows with the same values into columns "B" and "C"
Replies
5
Views
411
harzer
H
W
  • Question
Selection of data in the table that is furthest in time, but correct.
Replies
1
Views
186
Wojciech
W
S
  • Question
Clear cell contents of non highlighted cells in a range
Replies
1
Views
677
Serafin54
S
skyh3ck
  • Question
1st match column header, 2nd lookup a value in same column, 3rd return corresponding value from the very first column ?? need help
Replies
2
Views
286
skyh3ck
skyh3ck

Forum statistics

Threads
1,215,219
Messages
6,123,689
Members
449,117
Latest member
Aaagu

Share this page

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top