## Vlookup with multiple matches returns a different value

**Linda asks in this post:** How to return multiple values using vlookup in excel

Here is what I have: Data Range is in $E$1:$F$8, I would like my results in Col. B. Lookup value in column A and return the value in Col F that matches.

Since there are duplicates in Col. A I want Col. B to return the next matching value from col. F.

Essentially this is a Vlookup with multiple matches that would return a different value. Thanks for any help you can provide.

Data Range Col. A Col B

Red 2 Red

Green 6 Red

Pink 3 Red

Blue 9 Yellow

Red 7 Blue

Yellow 11 Blue

Blue 4

Red 14

**Answer:**

**Array Formula in cell B1:**

**How to enter an array formula**

- Select cell B2
- Type the formula above
- Press and hold CTRL + SHIFT
- Press Enter
- Release all keys

If you did it right, the formula now has curly brackets before and after, like this: {=array_formula}.

Copy cell B1 and paste it down as far as needed.

### Download excel sample file for this tutorial.

Vlookup-next-matching-item.xls

(Excel 97-2003 Workbook *.xls)

### Functions in this article:

**IF(logical_test,[value_if_true],[value_if_false])**

Checks whether a condition is met, and returns one value if TRUE, and another value if FALSE

**SMALL(array,k)**

Returns the k-th smallest number in this data set

**ROW(reference)**

Returns the rownumber of a reference

**INDEX(array,row_num,[column_num])**

Returns a value or reference of the cell at the intersection of a particular row and column, in a given range

**COUNTIF(range,criteria)**

Counts the number of cells within a range that meet the given condition

**MIN(number1,[number2])**

Returns the smallest number in a set of values. Ignores logical values and text

### Category: Vlookup

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

Improve your VLOOKUP formula and return multiple values

Question: How do i return multiple results for one lookup value? I have tried to use multiple vlookups, with no […]Comments(157) Filed in category: Excel, VLOOKUP and return multiple values

Comments(74) Filed in category: Excel, Vlookup

Vlookup – Return multiple unique distinct values

Ahmed Ali asks: How to return multiple values using vlookup in excel and removing duplicates? I have tried the formula […]Comments(45) Filed in category: Excel, Unique distinct values, VLOOKUP and return multiple values

Vlookup with 2 or more lookup criteria and return multiple matches

VLOOKUP and return multiple matches based on many criteria.Comments(38) Filed in category: Excel, VLOOKUP and return multiple values

Lookup multiple values in different columns and return a single value

S.Babu asks: Dear Oscar, I m working on the below table. ORDER MODEL MATERIAL QTY STATUS BOM a s6 1 […]Comments(31) Filed in category: Excel, Vlookup

Vlookup across multiple sheets in excel

This blog post describes how to search two tables on two sheets and return multiple results. Sheet1 contains table1 and […]Comments(27) Filed in category: Add-in, Excel, Vlookup

Use multiple conditions in Vlookup

Table of contents VLOOKUP and a condition VLOOKUP and a table VLOOKUP - Select a column with a drop down […]Comments(26) Filed in category: Excel, Vlookup

How to return a value if lookup value is in range

Question: Hi, What type of formula could be used if you weren't using a date range and your data was […]Comments(23) Filed in category: Excel, Lookup function, Vlookup

Lookup values in a range using two or more criteria and return multiple matches in excel, part 2

In this post we are going to extract multiple text values. We are looking for names and the criteria are […]Comments(21) Filed in category: Excel, Vlookup

### 9 Responses to “Vlookup with multiple matches returns a different value”

### Leave a Reply

**How to add a formula to your comment:**

<code>your formula</code>

**How to add VBA code to your comment:**

[vb 1="vbnet" language=","]

VBA code

[/vb]

**How to add a picture to your comment:**

Upload picture to postimage.org

Add picture link to comment.

**Contact Oscar**

You can contact me through this webpage

this is good, but wouldn't this be much better to use COLUMN(A1)instead of countif for Horizontal result and ROW(A1)for vertical result Vertical?

What could be the difference between using Countif or Row(a1)?

Jamil,

No, how would the function know which instance of the value to get?

Is that possible if the formula add the 2nd criteria?

Azumi

Azumi,

Array formula in cell C1:=INDEX($H$1:$H$8,SMALL(IF((B1=$G$1:$G$8)*(A1=$F$1:$F$8),ROW($G$1:$G$8)-MIN(ROW($G$1:$G$8))+1,""),COUNTIFS($B$1:B1,B1,$A$1:A1,A1)))

Download excel *.xlsx fileVlookup-next-matching-item-second-critera.xlsx

can you write the formula add 2 criteria by VBA ?

because the formula is too long for me to remember

thanks

Okay, beautiful formula, thanks.....

Azumi

Azumi,

thank you

How do we include formula to remove duplicates? ie. if the data included Red 7 Red 7 Red 7 but we only want the Index to return Red 7 once.