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.
[TABLE="width: 200"]
<tbody>[TR]
[TD]ID[/TD]
[TD]Value[/TD]
[/TR]
[TR]
[TD]100[/TD]
[TD]append process[/TD]
[/TR]
[TR]
[TD]100[/TD]
[TD]2017 survey[/TD]
[/TR]
[TR]
[TD]101[/TD]
[TD]append process[/TD]
[/TR]
[TR]
[TD]101[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]102[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]102[/TD]
[TD]2017 survey[/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]
My vlookup table has a unique set of each ID, and two columns for vlookups.
[TABLE="width: 200"]
<tbody>[TR]
[TD]ID[/TD]
[TD]app[/TD]
[TD]svy[/TD]
[/TR]
[TR]
[TD]100[/TD]
[TD]1[/TD]
[TD](1)[/TD]
[/TR]
[TR]
[TD]101[/TD]
[TD]1[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]102[/TD]
[TD][/TD]
[TD](1)[/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]
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!
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.
[TABLE="width: 200"]
<tbody>[TR]
[TD]ID[/TD]
[TD]Value[/TD]
[/TR]
[TR]
[TD]100[/TD]
[TD]append process[/TD]
[/TR]
[TR]
[TD]100[/TD]
[TD]2017 survey[/TD]
[/TR]
[TR]
[TD]101[/TD]
[TD]append process[/TD]
[/TR]
[TR]
[TD]101[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]102[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]102[/TD]
[TD]2017 survey[/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]
My vlookup table has a unique set of each ID, and two columns for vlookups.
[TABLE="width: 200"]
<tbody>[TR]
[TD]ID[/TD]
[TD]app[/TD]
[TD]svy[/TD]
[/TR]
[TR]
[TD]100[/TD]
[TD]1[/TD]
[TD](1)[/TD]
[/TR]
[TR]
[TD]101[/TD]
[TD]1[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]102[/TD]
[TD][/TD]
[TD](1)[/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]
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!
Last edited: