Author: Oscar Cronquist Article last updated on October 12, 2018

Ahmed Ali asks:

How to return multiple values using vlookup in excel and removing duplicates?
I have tried the formula to return multiple values using the index example and worked fine with none duplicate item but how can i list them without the duplicate?

Answer:

The following array formula is easier to understand than a VLOOKUP formula.

Update, 2017-08-16! New smaller regular formula.

Formula in cell G3:

=LOOKUP(2, 1/((COUNTIF($G$2:G2, $C$3:$C$10)=0)*($E$3=$B$3:$B$10)), $C$3:$C$10)

Array formula in cell G3:

=INDEX($C$3:$C$10,MATCH(0,COUNTIF($G$2:G2,$C$3:$C$10)+($B$3:$B$10<>$E$3),0))

The formulas above do not sort the unique distinct list.

Watch I video where I explain the formula above

https://www.youtube.com/watch?v=a3PgCzV53OE

Recommended articles

5 easy ways to extract Unique Distinct Values

First, let me explain the difference between unique values and unique distinct values, it is important you know the difference […]

5 easy ways to extract Unique Distinct Values

5 easy ways to VLOOKUP and return multiple values

This post explains how to lookup a value and return multiple values. No array formula required.

5 easy ways to VLOOKUP and return multiple values