Lookup returning multiple matches but removing duplicates

[c1nn4m0n]

Hello!

I am trying to create a formula to search the value in a static cell, searching a column in another sheet for a match and returning the value in the adjacent cell to the match on the same sheet. The matches will have duplicates, which I want to remove so that I end up with a list of values in my table. I have made many attempts, all of which have failed at some step, any help would be such a relief!

I have been able to use this formula to return the first result (but copying down returns the same exact result):
=IF(COUNTIF(Gallons!$C:$C,$B$2)>=ROWS($K6:K25),INDEX(Gallons!$B:$B,MATCH($B$2,Gallons!$C:$C,0)+ROWS($K6:K25)*0),"")

I have also found the following website which seems to detail my exact situation, but the result I get from the formula I have modified form it results in a blank:
Website: http://eimagine.com/how-to-return-multiple-match-values-in-excel-using-index-match-or-vlookup/
The formula I have modified: =IF(ISERROR(INDEX(Gallons!$B:$C,SMALL(IF(Gallons!$C:$C='Market Tour Report'!$K$5,ROW(Gallons!$C:$C)),ROW(5:5)),2)),"",INDEX(Gallons!$B:$C,SMALL(IF(Gallons!$C:$C='Market Tour Report'!$K$5,ROW(Gallons!$C:$C)),ROW(5:5)),2))

Example of Sheet 2 ("Gallons") where data is being retrieved from:
[TABLE="class: grid, width: 500"]
<tbody>[TR]
[TD]Misc
[/TD]
[TD]data to report back (gallons B)
[/TD]
[TD]Data to match to lookup value (gallons c)
[/TD]
[TD]misc
[/TD]
[TD]misc
[/TD]
[/TR]
[TR]
[TD][/TD]
[TD]report back if C2= sheet 1 lookup value
[/TD]
[TD]if matches lookup report back B2
[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD]more return values (to ~70,000)
[/TD]
[TD]more match values (to ~ 70,000)
[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]


Example of Sheet 1 ("market tour report") where the table I am trying to populate is:

[TABLE="class: grid, width: 500"]
<tbody>[TR]
[TD]lookup value
(K5)
[/TD]
[TD]misc
[/TD]
[TD]misc
[/TD]
[TD]misc
[/TD]
[TD]misc
[/TD]
[TD]misc
[/TD]
[TD]misc
[/TD]
[TD]misc
[/TD]
[TD]misc
[/TD]
[TD]misc
[/TD]
[/TR]
[TR]
[TD]formula cell 1
[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]formula cell 2 (on down for a total of 20 cells)
[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]

 

[c1nn4m0n]

So I think I have a formula that is working to report back the matches for my criteria, but I am still at a loss on how to remove duplicates.
H
ere is what I'm currently using:

=IFERROR(INDEX(Gallons!B:B,SMALL(IF(Gallons!C:C=$K$5,ROW(Gallons!C:C)-ROW(Gallons!C1)+1),ROWS(Gallons!$C1:Gallons!C1))),"")

Any help would be greatly appreciated!