Vlookup can return multiple values but that may contain duplicates. I have a formula that is useful to exclude duplicates in Vlookup Array Result in Google Sheets. The formula will simply ignore the duplicate search keys.

I mean I have search keys in an array that may contain duplicates. I want the Vlookup to only lookup the first appearance/occurrence of the search key.

Here in the above example, I’ve one Vlookup array formula in cell B2 (that formula you can get later). The search keys are in A2:A which contain duplicates.

The Vlookup returns blank against duplicates. If you use a normal Vlookup formula as below, other than the existing values in B2:B, it would return $4.00 in B6, $3.50 in B7, $3.75 in B8 and $3.00 in B10.

=ArrayFormula(VLOOKUP(A2:A10,F2:G6,2,0))

Note: Want to know more about how to use Vlookup in an Array? Follow my earlier tutorial on that – How to Use Vlookup to Return An Array Result in Google Sheets.

The above formula can’t exclude duplicates. Then how to exclude duplicates in Vlookup array result in Google Sheets?

For this purpose, I am making use of the MATCH function together with Vlookup in a logical test. See how I am going to do that.

Exclude Duplicates in Vlookup Array Result in Google Sheets

The Master Vlookup Formula That Skips Duplicates

Here is that formula that I’ve used in Cell B2.

=ArrayFormula(IFERROR(if(len(A2:A),if(row($A$1:$A)-match(A2:A,A2:A,0)=0,VLOOKUP(A2:A,F2:G6,2,FALSE),),)))

The function MATCH is the soul of the above formula that excludes duplicates in the Vlookup multiple value result.

The above formula supports an unlimited number of search keys in Column A. For the current range, the below formula would be enough. I’ve stripped all the unwanted elements from the above master formula here.

=ArrayFormula(if(row($A$1:$A9)-match(A2:A10,A2:A10,0)=0,VLOOKUP(A2:A10,F2:G6,2,FALSE),))

I am going to explain this formula so that you can easily understand. See that explanation.

Formula Explanation

To exclude duplicates in Vlookup Array result, I am doing an IF logical test. See the IF syntax first.

IF(logical_expression, value_if_true, value_if_false)

In this, I am using a MATCH formula as the logical_expressioin.

The value_if_true is a normal Vlookup formula that returns multiple values (array result) that including duplicates.

Then the value_if_false is left as blank.

So you only want to learn the logical_expression. What’s that?

See the logical expression.

=row($A$1:$A9)-match(A2:A10,A2:A10,0)=0

If you enter this formula independently in your sheet for testing, it won’t work. You should either enter this formula as an array formula by using Ctrl+Shift+Enter or you can wrap the above formula with the ARRAYFORMULA function.

The Role of Match Function to Omit Duplicate Vlookup Search Keys

The Match formula returns the relative position of the search keys. If the same key repeats, it repeats the first relative position.

For example, see the search_key “Mango”

The relative position of duplicates will be the same. For example, the item “Mango” retains its relative position in its second occurrence.

The relative position of the first item will be 1, the second item will be 2 and so on. There are 10 search keys and there would be 10 relative positions including duplicates.

So when you deduct (minus) the relative positions from serial numbers 1 to 10 (I’ve used the ROW function to return the serial numbers 1 to 10), you will get 0 for all the search keys for its first occurrences.

That means if the logical expression returns 0, that is the first occurrences. Now time to see my formula again to understand how to exclude duplicates in Vlookup array result in Google Sheets.

=ArrayFormula(if(row($A$1:$A9)-match(A2:A10,A2:A10,0)=0,VLOOKUP(A2:A10,F2:G6,2,FALSE),))

If the logical expression returns 0, IF executes the Vlookup (value_if_true) else returns blank (value_if_false). That’s all.

Additional Vlookup Resources:

1. Using Keyword Combinations in Vlookup in Sheets.
2. Vlookup Last Record in Each Group in Google Sheets.
3. How to Vlookup Importrange in Google Sheets.
4. Vlookup from Bottom to Top in Google Docs Sheets.
5. Vlookup Result Plus Next ‘n’ Rows in Google Sheets.
6. Create Hyperlink to Vlookup Output Cell in Google Sheets.
7. Vlookup Skips Hidden Rows in Google Sheets.