vlookup duplicate lookup value always only returns first value in column index

[Ken Soona]

The title might seem confusing, sorry. I hope the example data make it clear.

My data table always has duplicates in the lookup value, an ID numbers, but not always for the value in the column_index_number that I'm referencing. See below.

ID	Value
100	append process
100	2017 survey
101	append process
101
102
102	2017 survey

<tbody>
</tbody>

My vlookup table has a unique set of each ID, and two columns for vlookups.

ID	app	svy
100	1	(1)
101	1
102		(1)

<tbody>
</tbody>

I need to vlookup table to populate a 1 for each app n svy column. The problem, I think, is that the vlookup is looking at the first instance of the ID, and returning whatever is in the col reference, rather than looking at all of them. The 1's in parentheses are what I need that I can't get to derive.

For the App column, I have:

=IF(VLOOKUP(A2,data!$A$2:$B$6,2,FALSE)="append process",1,"")

...This is fine, it's getting me all the 1's my heart desires.

For the svy column, I have:
=IF(VLOOKUP(A2,data!$A$2:$B$6,2,FALSE="2017 survey",1,"")

I have seen formulas that can find multiple iterations of something in a vlookup, but I need to specify if I want the 2nd, 3rd, etc. That is unhelpful, b/c sometimes it's the 1st, or 3rd, or 7th. I need it to look across ALL instances, and find that match.

I think I'm close but for whatever reason I just can't seem to get it to work right.

Thank you for your help in advance!

 

[AhoyNC]

Maybe:
Copy formulas down as needed.

Excel Workbook
	A	B	C
1	ID	Value
2	100	append process
3	100	2017 survey
4	101	append process
5	101
6	102
7	102	2017 survey
8
9
10	ID	app	svy
11	100	1	1
12	101	1
13	102		1
Sheet

 

[Ken Soona]

"Maybe" in this case, worked great.

Thank you so much for your help!

 

[AhoyNC]

You're welcome. Thanks for the feedback.