Vlookup with multiple matches returns a different value
Linda asks in this post: How to return multiple values using vlookup in excel
I tried using the formula above but it didn't work for me and I can't figure out how to adjust it to accomodate my needs.
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.
Explaining formula in cell B1
Step 1 - Find value
A1=$E$1:$E$8
becomes
="Red={"Red"; "Green"; "Pink"; "Blue"; "Red"; "Yellow"; "Blue"; "Red"}
and returns
{TRUE; FALSE; FALSE; FALSE; TRUE; FALSE; FALSE; TRUE}
Step 2 - Replace TRUE with corresponding row number
The IF function has three arguments, the first one must be a logical expression. If the expression evaluates to TRUE then one thing happens (argument 2) and if FALSE another thing happens (argument 3).
IF(A1=$E$1:$E$8, ROW($E$1:$E$8)-MIN(ROW($E$1:$E$8))+1, "")
becomes
IF({TRUE; FALSE; FALSE; FALSE; TRUE; FALSE; FALSE; TRUE}, ROW($E$1:$E$8)-MIN(ROW($E$1:$E$8))+1, "")
becomes
IF({TRUE; FALSE; FALSE; FALSE; TRUE; FALSE; FALSE; TRUE}, {1;2;3;4;5;6;7;8}, "")
and returns
{1;"";"";"";5;"";"";8}
Step 3 - Extract k-th smallest row number
To be able to return a new value in a cell each I use the SMALL function to filter row numbers from smallest to largest based on corresponding value.
SMALL(IF(A1=$E$1:$E$8, ROW($E$1:$E$8)-MIN(ROW($E$1:$E$8))+1, ""), COUNTIF(A1:$A$1, A1))
becomes
SMALL({1;"";"";"";5;"";"";8}, COUNTIF(A1:$A$1, A1))
The COUNTIF function counts values based on a condition or criteria, the first argument contains an expanding cell reference, it grows when the cell is copied to cells below. This lets the formula count values.
SMALL({1;"";"";"";5;"";"";8}, 1)
and returns 1.
Step 4 - Return value based on row number
The INDEX function returns a value based on a cell reference and column/row numbers.
INDEX($F$1:$F$8, SMALL(IF(A1=$E$1:$E$8, ROW($E$1:$E$8)-MIN(ROW($E$1:$E$8))+1, ""), COUNTIF(A1:$A$1, A1)))
becomes
INDEX($F$1:$F$8, 1)
and returns 2 in cell B1.
Weekly Blog EMAIL
Welcome! I am Oscar and here to help you out.Feel free to comment and ask Excel questions.
Make sure you subscribe to my newsletter so you don't miss new blog articles.
Related articles
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.
Vlookup with 2 or more lookup criteria and return multiple matches
VLOOKUP and return multiple matches based on many criteria.
Vlookup across multiple sheets
This article demonstrates an array formula that searches two tables on two different sheets and returns multiple results. Sheet1 contains […]
VLOOKUP and return multiple values across columns
This article demonstrates a formula that lets you extract non-empty values across columns based on a condition. The image above […]
Use a drop down list to search and return multiple values
I will in this article demonstrate how to use a value from a drop-down list and use it to do […]
Search values distributed horizontally and return corresponding value
Question: Hi, The formula here works great but I can't figure out how to change it to work with data […]
Lookup multiple values in different columns and return multiple values
Jason C asks: I have a set of data, like the one you used in the original example that also […]
Vlookup a cell range and return multiple values
VLOOKUP a multi-column range 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 […]
9 Responses to “Vlookup with multiple matches returns a different value”
Leave a Reply to Azumi
How to comment
How to add a formula to your comment
<code>Insert your formula here.</code>
Convert less than and larger than signs
Use html character entities instead of less than and larger than signs.
< becomes < and > becomes >
How to add VBA code to your comment
[vb 1="vbnet" language=","]
Put your VBA code here.
[/vb]
How to add a picture to your comment:
Upload picture to postimage.org or imgur
Paste image link to your comment.
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 file
Vlookup-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.