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:

Misc	data to report back (gallons B)	Data to match to lookup value (gallons c)	misc	misc
	report back if C2= sheet 1 lookup value	if matches lookup report back B2
	more return values (to ~70,000)	more match values (to ~ 70,000)

<tbody>
</tbody>

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

lookup value (K5)	misc	misc	misc	misc	misc	misc	misc	misc	misc
formula cell 1
formula cell 2 (on down for a total of 20 cells)

<tbody>
</tbody>

 

[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!