5 easy ways to VLOOKUP and return multiple values
The VLOOKUP function is designed to return only a corresponding value of the first instance of a lookup value, from a column you choose. But there is a workaround to identify multiple matches.
The array formulas demonstrated below are smaller and easier to understand and troubleshoot than the useful VLOOKUP function.
However you are not limited to array formulas, Excel also has builtin features that work very well, you will be amazed at how easy it is to filter values in a data set.
Table of Contents
 VLOOKUP  Return multiple values [vertically]
 VLOOKUP  Return multiple values [horizontally]
 VLOOKUP  Extract multiple records based on a condition
 Lookup and return multiple values [AutoFilter]
 Lookup and return multiple values [Advanced Filter]
 Lookup and return multiple values [Excel Defined Table]
 Return multiple values vertically or horizontally [UDF]
 How to count VLOOKUP results
 Lookup and return multiple values in one cell
I have made a formula, demonstrated in a separate article, that allows you to VLOOKUP and return multiple values across worksheets, there is also an AddIn that makes it even easier to accomplish this task.
Now, if you only need one instance of each returned value then check this article out: Vlookup – Return multiple unique distinct values It lets you specify a condition and the formula is not even an array formula.
I have also written an article about searching for a string (wildcard search) and return corresponding values, it requires a somewhat more complicated formula but don't worry, you will find an explanation there, as well.
Did you know that it is also possible to VLOOKUP and return multiple values distributed over several columns, the formula even ignores blanks.
VLOOKUP  Return multiple values vertically
Can VLOOKUP return multiple values? It can, however the formula would become huge if it needs to contain the VLOOKUP function. The formula presented here does not contain that function, however, it is more versatile and smaller.
The image above shows you an array formula that extracts adjacent values based on a lookup value in cell D10.
Another great thing with this array formula is that it allows you to lookup and return values from whatever column you like contrary to the VLOOKUP function that lets you only do a lookup in the leftmost column, in a given range.
Array formula in D10:
This Youtube video explains how to VLOOKUP and return multiple matching values:
The array formula in cell G3 looks in column B for "France" and return adjacent values from column C. The array formula in cell G3 filters values unsorted, if you want to sort returning values alphabetically, read this:
Vlookup with 2 or more lookup criteria and return multiple matches
How to create an array formula
 Copy array formula above. (Ctrl + c)
 Doubleclick on a cell.
 Paste (Ctrl + v) array formula.
 Press and hold Ctrl + Shift simultaneously.
 Press Enter once.
 Release all keys.
Read more
 How to enter an array formula
 Convert array formula to a regular formula
 How to enter array formulas in merged cells
The array formula above filters only values with one condition, the following article explains how to filter based on multiple criteria: Vlookup with 2 or more lookup criteria and return multiple matches
If you don't like array formulas, try this regular but more complicated formula in cell D10:
Explaining array formula (Return values vertically)
You can easily follow along as I explain the formula, select cell D10. Go to tab "Formulas" on the ribbon and click "Evaluate Formula" button.
Click "Evaluate" button shown above to move to next step.
Step 1  Identify cells equal to the criterion
= (equal sign) is a comparison operator and checks if criterion (E3) is equal to values in array ($B$3:$B$7). This operator is not case sensitive.
$E$3=$B$3:$B$7
becomes
"France"={"Germany";"Italy";"France";"Italy";"France"}
and returns
{FALSE, FALSE, TRUE, FALSE, TRUE}
Step 2  Create array containing corresponding row numbers
The ROW function returns the row number based on a cell reference, we are using a cell reference that points to a cell range containing multiple rows so the ROW function returns an array of row numbers.
MATCH(ROW($B$3:$B$7), ROW($B$3:$B$7))
The MATCH function finds the relative position of a value in a cell range or array, however, I am using multiple values so this step returns an array of numbers.
MATCH(ROW($B$3:$B$7), ROW($B$3:$B$7))
becomes
MATCH({3, 4, 5, 6, 7}, {3, 4, 5, 6, 7})
and returns {1,2,3,4,5}
Step 3  Filter row numbers equal to a condition
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(($B$10=$B$3:$B$7), MATCH(ROW($B$3:$B$7), ROW($B$3:$B$7)), "")
becomes
IF(FALSE, FALSE, TRUE, FALSE, TRUE}, MATCH(ROW($B$3:$B$7), ROW($B$3:$B$7)), "")
becomes
IF(FALSE, FALSE, TRUE, FALSE, TRUE},{1, 2, 3, 4, 5}, "")
and returns {"", "", 3, "", 5}
Step 4  Return the kth 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.
The ROWS function keeps track of the numbers based on an expanding cell reference. It will expand as the formula is copied to the cells below.
SMALL(IF(($E$3=$B$3:$B$7),ROW($B$3:$B$7)MIN(ROW($B$3:$B$7))+1,""),ROWS($A$1:A1))
becomes
SMALL({"", "", 3, "", 5}, ROWS($A$1:A1))
This part of the formula returns the kth smallest number in the array {"", "", 3, "", 5}
To calcualte the kth smallest value I am using ROWS($A$1:A1) to create the number 1.
When the formula in cell D10 is copied to cell D11, ROWS($A$1:A1) changes to ROWS($A$1:A2). ROWS($A$1:A2) returns 2.
In Cell D10: =INDEX($C$3:$C$7, SMALL({"", "", 3, "", 5}, ROWS($A$1:A1))
=INDEX($C$3:$C$7, SMALL({"", "", 3, "", 5}, 1))
The smallest number in array {"", "", 3, "", 5} is 3.
In Cell D11: =INDEX($C$3:$C$7, SMALL({"", "", 3, "", 5}, ROWS($A$1:A2)))
=INDEX($C$3:$C$7, SMALL({"", "", 3, "", 5}, 2))
The second smallest number in array {"", "", 3, "", 5} is 5.
Step 4  Return value based on row number
The INDEX function returns a value based on a cell reference and column/row numbers.
In Cell D10:
=INDEX($C$3:$C$7, 3)
becomes
=INDEX({"Pear", "Orange", "Apple", "Banana", "Lemon"}, 3)
and returns "Apple" in cell D10.
In Cell D11:
=INDEX($C$3:$C$7, 5) returns "Lemon"
This article demonstrates how to filter an Excel defined table programmatically based on a condition using event code and a macro.
How to remove #num errors
The picture above shows you the array formula copied down to cell D12 however there are only two values shown, the remaining cells show nothing not even an error.
Array formula in cell D10:
The IFERROR function lets you convert error values to blank cells or really in whatever value you want. In this case it returns blank cells.
Recommended articles
 How to use the IFERROR function
 How to use the ISERROR function
 How to use the ERROR.TYPE function
 How to find errors in a worksheet
 Delete blanks and errors in a list
Count matching values
The following image shows you a data set in column B and C. The lookup value in cell E3 is used for identifying matching cell values in column B.
Formula in cell G3:
Alternative formula in cell G3:
Recommended articles
 Count a given pattern in a cell value
 Count cells containing text from list
 Count cells between specified values
 Count entries based on date and time
 Count unique distinct values
 Count unique distinct records
Return multiple values horizontally
This array formula is entered in cell C9. Then copy cell C9 and paste to the right.
Array formula in C9:
Enter the formula as an array formula or use this regular but more complicated formula:
Recommended articles
 Search values distributed horizontally and return corresponding values
 Resize a range of values
 Rearrange values
 Rearrange cells in a cell range to vertically distributed values
 Rearrange values based on category(VBA)
 Normalize data (VBA)
 Normalize data, part 2
Extract multiple records based on a condition
The formula in cell A10 extracts records based on the value in cell B9.
Array formula in cell A10:
To enter an array formula, type the formula in a cell then press and hold CTRL + SHIFT simultaneously, now press Enter once. Release all keys.
The formula bar now shows the formula with a beginning and ending curly bracket telling you that you entered the formula successfully. Don't enter the curly brackets yourself.
Copy cell A10 and paste to cell range B10:C10. Then copy A10:C10 and paste to cell range A11:C12.
Watch a video where I explain how to use the array formula and how it works
Enter the formula above as an array formula or use this regular but more complicated formula:
Recommended reading
 Extract all rows from a range that meet criteria in one column
 Match two criteria and return multiple records
 Extract records where all criteria match
 Search for a text string in a data set using an array formula
 Filter unique distinct records
Lookup and return multiple values [AutoFilter]
The AutoFilter is a builtin feature in Excel that allows you to quickly filter data. The following video shows you how to quickly filter a data set, I don't think you can do it more quickly than this.
Instructions on how to filter a data set [AutoFilter]
 Rightclick on a cell value that you want to filter

Click on "Filter" and then "Filter by Selected Cell's Value"
 That's it!
How to remove a filter

Click on filter button next to header, shown in picture below

Click on "Clear Filter From "Country""

The AutoFilter buttons next to each header are still there.

If you want to remove those as well, go to tab "Home" on the ribbon and click on "Sort & Filter" button, then on "Filter"

The data set now looks like this:
Lookup and return multiple values [Advanced Filter]
The Advanced Filter is a tool in Excel that allows you to filter a dataset using complicated criteria combinations like AND  OR logic that the regular AutoFilter tool can't accomplish.
In this case I am only going to filter based on a single condition so this will be an easy introduction to the Advanced Filter in Excel.
 Copy the dataset headers and place them above or below your dataset, this to avoid confusion if the conditions disappear when a filter is applied.
Rows will be hidden and if a condition is on the same row it will be hidden as well. I created headers on row 2, see image above.  Enter the condition below the correct header you want to apply a filter to, I entered my condition in cell B3.
 Select cell range B5:C10.
 Go to tab "Data" on the ribbon.
 Click "Advanced" button.

Click in Criteria range: field and select cell range B2:C3
 Click OK button.
The image above shows the dataset filtered based on the condition used in cell B3. To clear the filter simply go to tab "Data" on the ribbon and click "Clear" button.
Lookup and return multiple values [Excel Defined Table]
The image above shows you a dataset converted to an Excel Defined Table and filtered based on item "France" in column B.
 Select a cell in your data set.
 Press CTRL + T (shortcut for creating an Excel Defined Table).

A dialog box appears, click the checkbox if your data set contains headers.
 Click OK button.
To filter the table follow these simple steps:

Click the black arrow next to a header name.
 Make sure the checkbox next to the value you want to use as a condition is selected.
 Click OK button.
So why use an Excel defined Table? An Excel defined Table contains many more useful features.
 Enter a formula in one cell and Excel automatically enters the formula in the remaining Excel Table cells on the same column.

Cell references are converted to structured references, for example a cell reference to column "Country" might look like this: Table[Country].
This is beneficial because you don't need to adjust cell references if your table grows or shrinks, the cell reference is the same no matter what. You don't need to use dynamic named ranges either.  Easy to filter and sort data.
 Easy to add or delete data, simply type your data below the last table row and the Excel defined Table will automatically expand.
 Use as data source for a chart and the chart will display what is filtered.
Return multiple values vertically or horizontally [UDF]
Make sure you have copied the vba code below into a standard module before entering the array formula.
User defined Function Syntax
vbaVlookup(lookup_value, table_array, col_index_num, [h])
Arguments
lookup_value  Required. 
table_array  Required. A cell reference to the data table you want to search. 
col_index_num  Required. A number representing the column in the table_array. 
[h]  Optional. Return values horizontally. 
Array formula in cell C14:D14:
Watch a video that explains how to use the User Defined Function
How to enter custom function array formula
 Select cell range C9:C11
 Type above custom function
 Press and hold Ctrl + Shift
 Press Enter once
 Release all keys
Array formulas allows you to do advanced calculations not possible with regular formulas.
How to enter custom function array formula
 Select cell range C14:D14
 Type above custom function
 Press and hold Ctrl + Shift
 Press Enter once
 Release all keys
How to copy array formula to the next row
 Select cell range C14:D14
 Copy cell range
 Select cell range C15:D15
 Paste
Vba code
 Copy vba code below.
 Press Alt + F11 to open the visual basic editor.
 Rightclick on your workbook in the project explorer.
 Click on "Insert".

Click on "Module".
 Paste code to code module.
 Exit vb editor and return to Microsoft Excel
'Name User Defined Function and arguments Function vbaVlookup(lookup_value As Range, tbl As Range, col_index_num As Integer, Optional layout As String = "v") 'Declare variables and data types Dim r As Single, Lrow, Lcol As Single, temp() As Variant 'Redimension array variable temp ReDim temp(0) 'Iterate through cells in cell range For r = 1 To tbl.Rows.Count 'Check if lookup_value is equal to cell value If lookup_value = tbl.Cells(r, 1) Then 'Save cell value to array variable temp temp(UBound(temp)) = tbl.Cells(r, col_index_num) 'Add anoher container to array variable temp ReDim Preserve temp(UBound(temp) + 1) End If Next r 'Check if variable layout equals h If layout = "h" Then 'Save the number of columns the user has entered this User Defined Function in. Lcol = Range(Application.Caller.Address).Columns.Count 'Iterate through each container in array variable temp that won't be populated For r = UBound(temp) To Lcol 'Save a blank to array container temp(UBound(temp)) = "" 'Increase the size of array variable temp with 1 ReDim Preserve temp(UBound(temp) + 1) Next r 'Decrease the size of array variable temp with 1 ReDim Preserve temp(UBound(temp)  1) 'Return values to worksheet vbaVlookup = temp 'These lines will be executed if variable layout is not equal to h Else 'Save the number of rows the user has entered this User Defined Function in Lrow = Range(Application.Caller.Address).Rows.Count 'Iterate through empty cells and save nothing to them in order to avoid an error being displayed For r = UBound(temp) To Lrow temp(UBound(temp)) = "" ReDim Preserve temp(UBound(temp) + 1) Next r 'Decrease the size of array variable temp with 1 ReDim Preserve temp(UBound(temp)  1) 'Return temp variable to worksheet with values rearranged vertically vbaVlookup = Application.Transpose(temp) End If End Function
Recommended reading
 Lookup multiple values in one cell [UDF]
 Fuzzy lookups [UDF]
 Filter an Excel defined Table based on selected cell [VBA]
 Filter words containing a given string in a cell range [UDF]
 Filter an Excel defined Table programmatically [VBA]
 How to save custom functions and macros to an AddIn
 Add your personal Excel Macros to the ribbon
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 nonempty 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 dropdown 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 […]
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 […]
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 multicolumn 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 […]
Ok  I absolutely MUST comment on this! I've spent my entire day looking all over the web for help on doing a VLOOKUP to look up one value and return multiple corresponding values and have not found anything that has helped me as much as you have! =D You've made my day. Thank you for posting this!
~kenbra
I am happy you found this post, but my advice is to take a look at this post instead: Using array formula to look up multiple values in a list. It has an array formula not as complicated as this one.
Thank you for your comment!
I am trying to use your code for a calendar. the file template that you have shown I have downloaded. In place of the values in Column B, I would like to put dates. In Column C, for the $ values I would replace with addresses
When I try modifying the table and expanding it past three columns it tells me I am out of the array range.
Can you assist?
Thanks!
Jim,
Which formula and template?
Oscar,
I downloaded the file lookupvba3.
I think I can use this to help me populate a calendar.
I substituted dates for Pen, Paper, and Eraser. I then had locations substituted for $ values.
Where I have a date of say, 11/27/12, I have 10 locations delivering that day. Using the template as shown in the screen shot under "Retun multiple values horizontally or vertically (vba)" I cannot expand past column "C" to return multiple values.
I think it is in the array code but I cannot figure out how to return values past column C.
If you can help, greatly appreciated!
Thanks,
Jim
Jim,
The function procedure can not return values from multiple columns.
This array formula returns values from a range, in cell B10:
I wish I could return all values to single a column.
Jim,
Now I know how to return all values to a single column.
Read this post:
Lookup and return multiple values from a range excluding blanks
Hi. I used this formula and it works great. However I like to know how the formulas I use work. I have spent a lot of time on the internet trying to break it down but this one has me stumped. I understand part, like the VLOOKUP and INDEX but I don't know how the rest fits in. Are you able to break this down for the dummies? If you have time it would be greatly appreciated.
Hi,
Continuing your example, is there a way to "Eraser" and "Paper clip"? and keep goign down? It returns #NUM because Row is set to 3:3 after the two Pen entries. Is there a way to reset the row to ROW(1:1) after a new vlookup search string?
Thanks
Excel user, did you ever figure out how to accomplish this? I have been searching all over for this exact question and cannot find the answer. The equation works great but I need to use it thousands of times, and resetting the Row to 1:1 for every new search string is too cumbersome. Thanks.
Excel User,
I am not sure I understand but I think I covered your question in this post: https://www.getdigitalhelp.com/2009/12/29/vlookupwith2ormorelookupcriteriaandreturnmultiplematchesinexcel/
Very helpful posts. 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
Data Range Col. A Col B
Red 2
Green 6
Pink 3
Blue 9
Red 7
Yellow 11
Blue 4
Red 14
Thank you for your very helpful posts. 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
Linda,
read this post: Vlookup with multiple matches returns a different value in excel
looking for a formula that will take a part number from one column and go and look for all related vehicle applications per that part number and return the vehicle applications to a single cell related back to the part number
HI,
Thanks for the same.
I Tried a lot but i didnt get. i want to know how to use the same.
Please requesting you kindly help me on this.
Regards,
Chandra
Chandra,
Did you download the excel example file?
Hi there, I think I understand the logic behind these formula but when I try to amend it it doesn't work. I downloaded the example file.
This formula below works
INDEX(tbl,SMALL(IF($E$8=$B$2:$B$6,ROW($B$2:$B$6)MIN(ROW($B$2:$B$6))+1,""),ROW(A1)),2)
Originally tbl is referencing B2:C6
I change it so that it is referencing B2:D6
Pen $1.50 Red
Eraser $2.00 Blue
Paper $1.00 Green
Pen $1.70 Yellow
Paper clip $3.00 Black
and then update the formula to
INDEX(tbl,SMALL(IF($E$8=$B$2:$B$6,ROW($B$2:$B$6)MIN(ROW($B$2:$B$6))+1,""),ROW(A1)),3)
I would expect 'Red' and 'Yellow' to be returned instead of 1.50 and 1.70 but instead the formula will not calculate.
Any suggestions?
Ignore last. Realised it was because I hadn't pressed CTRL + SHIFT + ENTER!
Thanks very much for your post. It has really helped me!
Ignore last. Realised I hadn't hit CTRL + SHIFT + ENTER!
Thanks for your post. It really helped me.
Richard,
Read this post: Excel udf: Lookup and return multiple values concatenated into one cell
This is just awesome. Thank you. Had to do a big tweek to do a less than if statement. But wow this worked great. Thank you
Joe,
Thanks for your comment!
For the life of me I cannot get this to work on my spreadsheet. So frustrating.
Jim,
Post your formula here and I´ll see what I can do.
Thanks very much for this help. The information is very well presented and explanied.
I am attemting to do what is described above however rather than actually listing the mutiple values in different cells I just want to add them all together and find the total in one cell. Do you know if this is possible or is there an easy method.
Thanks
Henry
Henry Nichols,
SUMIF(range, critera, [sum_range])
Adds the cells specified by a given condition or criteria.
HI,
Nice one, just a little mistake
Here is the correct one
=INDEX($C$2:$C$6, SMALL(IF($B$8=$B$2:$B$6, ROW($B$2:$B$6)MIN(ROW($B$2:$B$6))+1, ""), ROW($A$1:$A$6)))
At the end it is not Row(A1), but has to be a vector for an increasing increment.
cybou,
It is not a mistake, ROW(A1) is a relative cell reference.
Download the example file and check it out.
I like this  but would like the #num not to be displayed if the value is not found. Sometimes I have 3 values and at other times may be 5
Elwil,
IFERROR() function filters errors.
Just wanted to say thanks, what an awesome bit of exceling
You are most welcome! Thanks for commenting!
Oscar,
Thanks I'm using Excel 2003 and used the VLookup array successfully. However I'm been unsuccessful adding the IFERROR() function to clean up the sheet. Could you provide further explanation on using IFERROR for Excel 2003.
IFERROR is a function in excel 2007 and later versions.
Excel 2007
IFERROR(value, value_if_error)
Excel 2003 and earlier versions:
IF(ISERROR(formula), value_if_error, formula)
Hi,
First off thanks for the help ur site is great.
My question is in addition to the formula mentioned is there any way I can get the formula to return unique values only?
For the example above, if Pen had 4 prices ($1.5,$1.7,$1.5 and $1.7) is there a way to get only one 1.5 and 1.7?
I hope my question is clear...
Thanks!
Gi99a,
In my blog post example above, array formula in cell C8:
=INDEX(tbl, SMALL(IF(($B$8=$B$2:$B$6)*(COUNTIF($C$7:C7, $C$2:$C$6)=0), ROW($B$2:$B$6)MIN(ROW($B$2:$B$6))+1, ""), ROW(A1)), 2) + CTRL + SHIFT + ENTER.
Hey,
Thanks for ur reply.
However this still gives me all values of "pen" so cell C8 onwards would give me repeated values for pen if there are any in "tbl"...
Gi99a
Download example file
When I change the data of the above example as below, it cannot output correctly. Could you solve it?

Data:
Pen $1.50
Pen $1.50
Pen $5.00
Pen $18.00
Paper clip $3.00
Output:
Pen $1.50
$18.00
#NUM!

AY,
You are right! I uploaded a new file. Thanks for commenting!
I'm not even sure if this website is still active or if you would even receive this message but I will ask anyways!
I am needing to produce the same results that Gi99a needed but is there a way to produce them in a single cell instead of down the column?
Thanks in advance!
Thank you for this, it was really helpful.
ErikB,
Thanks for your comment, I appreciate it!
How to return multiple values using vlookup in excel and removing duplicates?
my sheet is setup as follows
A B C D E
1 Section Category item flavor size
2 food Coffee Espresso none Single
3 food Coffee Espresso none double
4 food Coffee Americano none Single
5 food Coffee Americano none double
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?
appreciate your help
Ahmed Ali,
Read this post: Vlookup – Return multiple unique distinct values in excel
Hey there, I'd just like to drop a bomb of thanks, this helped reduce my fear of ARRAY formulas man. This post was a BIG help!
Dear Oscar,
First of all thank you for sharing your expertise in excel. I am one who benefited from all your comments and formula here. I have some difficulties though from the below formula. Basically I'm using xcelsius 2008, and the problem is ROW function is not recognize by xcelsius, can please help me with the same concept of the below formula without using the row function? I'm using the below formula because i want the multiple results using vlookup. please help. Thanks
Marlon
=VLOOKUP($Y$1, INDEX($AA$38:$AO$51, SMALL(IF($Y$1=INDEX($AA$38:$AO$51, , 1), ROW($AA$38:$AO$51)MIN(ROW($AA$38:$AO$51))+1, ""), ROW(7:7)), , 1), $AB$36, FALSE)
Marlon,
Sorry, I have no knowledge about xcelsius 2008.
Duuuude, you have no idea how helpful this was
Munir,
thanks!
Hi Oscar,
I am trying to implement this formual in my spreadsheet but it is just returning the first row and the rest of the rows are #num.
I have data in A2:B602. I entered the name to look up in cell c2. I want it to return all the data corresponding to that name in column D. Could you please suggest some work around or why the formula is not working
=INDEX($B$2:$B$602,SMALL(IF(($C$2=$A$2:$A$602),ROW($A$2:$A$602)MIN(ROW($A$2:$A$602))+1,""),ROW(A2)))
Thanks
Ramya,
A minor change to your formula.
How to create an array formula
Copy array formula (Ctrl + c)
Double click on a cell
Paste (Ctrl + v) array formula.
Press and hold Ctrl + Shift simultaneously.
Press Enter once.
Release all keys.
How to copy formula in cell D2
Select cell D2
Copy (Ctrl + c)
Select cell range D2:D10
Paste (Ctrl + v)
(Extend formula further down if range D2:D10 is too small)