VLOOKUP to return unique values on subsequent matched records

New Contributor

Hello, Microsoft Excel Community.

I'm not sure if there is a VLOOKUP formula that will do what I need, but there must be something out in MS formula land to return accurate results for duplicative information.


Here's an example: 

I have the same violation in three subsequent records in my spreadsheet because there were three separate payments on the violation and three separate commission amounts on the violation.  But the VLOOKUP only returns the match on the first record.  A simple vlookup can't look at the same violation number on records 2 and 3 to return the unique values under the columns beginning with "My..."  on records 2 and 3.  It just pulls in the same data from record 1.  


Is there a way to edit the formula to have it look at the violation number on records 2 and 3 and have it return the values in the data


ViolationMy Transaction AmountMy  Commission AmountXYZ Transaction Date XYZ Transaction Amt  XYZ rnd Commission Amount 
134883599$26.66$2.525/6/2021 $ 26.66 $  2.53
134883599$86.05$8.155/6/2021 $ 26.66 $  2.53
134883599$88.94$8.425/6/2021 $ 26.66 $  2.53
5 Replies

@Detlef Lewin 


Hi, Detlef.

I don't understand how to use "FILTER()" with Vlookup - could you please share the syntax?



I appreciate that filtering formula, but I don't want to filter the data to get a subset. It's okay. I was playing around with "countif" and just attached a unique suffix to my violation. As long as I'm sorting in both the source and target tables, I should be associating the correct suffix on each record. Thanks for the quick reply!
Dear Mr. Simone
Please use "Filter" function, it's very useful function, where you can find duplicate and unique value as you need

Exp. =Filter("What Range you want","Where should lookup your logical value"="logical value","Optional")
In your case
Logical Value = 134883599
=Filter(a2:g4,a2:a3=a2,"data not found")