New Contributor

# 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

Use FILTER().

# Re: VLOOKUP to return unique values on subsequent matched records

Hi, Detlef.

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

Thanks,
Simone

# Re: VLOOKUP to return unique values on subsequent matched records

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!

# Re: VLOOKUP to return unique values on subsequent matched records

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")