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

Recommended articles

5 easy ways to extract unique distinct values

Update: 30 Aug, 2017h You have quite a few options to choose from if you are looking for a way […]

Comments(149) Filed in category: Advanced filter, Excel, Unique distinct values, Unique 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.

Comments(445) Filed in category: Excel, VLOOKUP and return multiple values

Use VLOOKUP and return multiple values sorted from A to Z

The array formula in column G filters values in column C using a condition in cell E3, comparing it with […]

Comments(2) Filed in category: Excel, VLOOKUP and return multiple values

How to enter an array formula

  1. Double click on cell G3
  2. Copy (Ctrl + c) and paste (Ctrl + v) array formula to cell
  3. Press and hold Ctrl + Shift simulatenously
  4. Press Enter once
  5. Release all keys

Learn the basics of Excel arrays

Array formulas allows you to do advanced calculations not possible with regular formulas.

Comments(2) Filed in category: Count values, Excel

Copy array formula

  1. Select cell G3
  2. Copy cell ( Ctrl + c)
  3. Select cell range G4:10
  4. Paste (Ctrl - v)

Explaining array formula in cell E8

Step 1 - Compare cell value in E3 with column Category and return a boolean array

$B$3:$B$10<>$E$3

becomes

{1;2;1;1;1;1;1;2}<>1

and returns {FALSE; TRUE; FALSE; FALSE; FALSE; FALSE; FALSE; TRUE}

Step 2 - Check earlier values above current cell

COUNTIF($G$2:G2,$C$3:$C$10)

becomes

COUNTIF("Unique distinct list",{"A";"A";"A";"A";"B";"B";"D";"C"})

and returns {0;0;0;0;0;0;0;0}

COUNTIF function

Counts the number of times a value exists in a cell range.

Comments(5) Filed in category: Excel

Step 3 - Add arrays

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

becomes

{FALSE; TRUE; FALSE; FALSE; FALSE; FALSE; FALSE; TRUE} + {0;0;0;0;0;0;0;0}

and returns {0;1;0;0;0;0;0;1}

Step 4 - Match first zero value in array and return relative position

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

becomes

MATCH(0,{0;1;0;0;0;0;0;1},0)

and returns 1.

MATCH function

Identify the position of a value in an array.

Comments(12) Filed in category: Excel

Step 5 - Return a value or reference of the cell at the intersection of a particular row and column

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

becomes

INDEX($C$3:$C$10,1)

becomes

INDEX({"A";"A";"A";"A";"B";"B";"D";"C"},1)

and returns "A" in cell G3.

INDEX function explained

Fetch a value in a data set based on coordinates.

Comments(14) Filed in category: Cross reference table, Excel, INDEX function

Download excel file

Vlookup - Return multiple unique distinct valuesv2.xlsx
(Excel 97-2003 Workbook *.xls)

 

Recommended blog articles