Forum Discussion
VLOOKUP to return unique values on subsequent matched records
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
| Violation | My Transaction Amount | My Commission Amount | XYZ Transaction Date | XYZ Transaction Amt | XYZ rnd Commission Amount |
| 134883599 | $26.66 | $2.52 | 5/6/2021 | $ 26.66 | $ 2.53 |
| 134883599 | $86.05 | $8.15 | 5/6/2021 | $ 26.66 | $ 2.53 |
| 134883599 | $88.94 | $8.42 | 5/6/2021 | $ 26.66 | $ 2.53 |
5 Replies
- 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") Hi, Detlef.
I don't understand how to use "FILTER()" with Vlookup - could you please share the syntax?
Thanks,
Simone- I meant use FILTER() instead of VLOOKUP().
https://support.microsoft.com/en-us/office/filter-function-f4f7cb66-82eb-4767-8f7c-4877ad80c759?ui=en-US&rs=en-US&ad=US
