How to return multiple values using vlookup in excel
The VLOOKUP function is designed to return only the corresponding value of the first instance of a lookup value. But there is a work-around to identify multiple matches. Although VLOOKUP is not used in this array formula, these array formulas are easier to understand and troubleshoot.
Table of Contents
- Return multiple values vertically
- Explaining formula (Return values vertically)
- Return multiple values horizontally
- Return multiple records
- How to remove #num errors
- Return multiple values vertically or horizontally (vba)
- How to create an array formula
- Lookup across multiple sheets
- Vlookup – Return multiple unique distinct values in excel
- Search for a text string and return multiple adjacent values
- Lookup and return multiple values from a range excluding blanks
Return multiple values vertically
This array formula is entered in cell C8. Then copy cell C8 and paste to cells below.
Array formula in C8:
or use this regular formula:
How to create an array formula
Download excel file
Vlookup.xls
Vlookup - dynamic named range.xls
(Excel 97-2003 Workbook *.xls)
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:
or use this regular formula:
How to create an array formula
Download excel file
Return multiple values horizontally.xls
Return multiple records
Array formula in cell A10:
Copy cell A10 and paste to cell range B10:C10. Then copy A10:C10 and paste to cell range A11:C12.
or use this regular formula:
How to create an array formula
Download excel file
Vlookup - return multiple records.xlsx
(Excel 2007 Workbook *.xlsx)
How to create an array formula
- Copy (Ctrl + c) and paste (Ctrl + v) array formula to formula bar.
- Press and hold Ctrl + Shift.
- Press Enter once.
- Release all keys.
How to remove #num errors
Array formula:
Explaining array formula (Return values vertically)
Step 1 - Identify cells equal to the criterion
=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(A1)))
= (equal sign) is a comparison operator and checks if criterion ($B$8) is equal to values in array ($B$2:$B$6). This operator is not case sensitive.
$B$8=$B$2:$B$6
becomes
"Pen"={"Pen", "Eraser", "Paper", "Pen", "Paper Clip"}
becomes
{"Pen"="Pen", "Pen"="Eraser", "Pen"="Paper"; "Pen"="Pen", "Pen"="Paper Clip"}
becomes
{TRUE, FALSE, FALSE, TRUE, FALSE}
Step 2 - Create array containing row numbers
=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(A1)))
ROW($B$2:$B$6)-MIN(ROW($B$2:$B$6))+1
becomes
{2,3,4,5,6} - MIN({2,3,4,5,6})+1
becomes
{2,3,4,5,6} - 2+1
becomes
{2,3,4,5,6} - 1
becomes
{1,2,3,4,5}
Step 3 - Filter row numbers equal to criterion
=INDEX($C$2:$C$6, SMALL(IF({TRUE,FALSE,FALSE,TRUE,FALSE},{1,2,3,4,5} , ""), ROW(A1)))
becomes
=INDEX($C$2:$C$6, SMALL({1,"","",4,""}, ROW(A1)))
Step 4 - Return the k-th smallest row number
=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(A1)))
SMALL(IF($B$8=$B$2:$B$6, ROW($B$2:$B$6)-MIN(ROW($B$2:$B$6))+1, ""), ROW(A1))
becomes
SMALL({1,"","",4,""}, ROW(A1))
This part of the formula returns the k-th smallest number in the array (1,"","",4,"")
To calcualte the k-th smallest value I am using ROW(A1) to create the number 1.
When the formula in cell C8 is copied to cell C9, ROW(A1) changes to ROW(A2). ROW(A2) is 2.
In Cell C8: =INDEX($C$2:$C$6, SMALL((1,"","",4,""), ROW(A1)))
=INDEX($C$2:$C$6, SMALL((1,"","",4,""), 1))
The smallest number in array (1,"","",4,"") is 1.
In Cell C9: =INDEX($C$2:$C$6, SMALL((1,"","",4,""), ROW(A2)))
=INDEX($C$2:$C$6, SMALL((1,"","",4,""), 2))
The second smallest number in array (1,"","",4,"") is 4.
Step 5 - Return value in range
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
In Cell C8:
=INDEX($C$2:$C$6,1)
becomes
=INDEX({1.5,2,1,1.7,3}, 1)
and returns $1.50
In Cell C9:
=INDEX($C$2:$C$6,4) is $1,70
Functions in this article:
VLOOKUP(lookup_value; table_array; col_index_num; [range_lookup])
Looks for a value in the leftmost column of a table, and then returns a value in the same row from a column you specify. By default, the table must be sorted in ascending order.
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.
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
MIN(number1,[number2])
Returns the smallest number in a set of values. Ignores logical values and text
ROW(reference)
Returns the rownumber of a reference
Return multiple values vertically or horizontally (vba)
Make sure you have copied the vba code below into a standard module before entering the array formula.
=vbaVlookup(lookup_value, table_array, col_index_num, [h])
h is optional, h= return values horizontally
Array formula in cell C9:C11:
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 formula in cell C14:D14:
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
- Insert a module in project explorer
- Paste into code window
- Return to Microsoft Excel
Function vbaVlookup(lookup_value As Range, tbl As Range, col_index_num As Integer, Optional layout As String = "v") Dim r As Single, Lrow, Lcol As Single, temp() As Variant ReDim temp(0) For r = 1 To tbl.Rows.Count If lookup_value = tbl.Cells(r, 1) Then temp(UBound(temp)) = tbl.Cells(r, col_index_num) ReDim Preserve temp(UBound(temp) + 1) End If Next r If layout = "h" Then Lcol = Range(Application.Caller.Address).Columns.Count For r = UBound(temp) To Lcol temp(UBound(temp)) = "" ReDim Preserve temp(UBound(temp) + 1) Next r ReDim Preserve temp(UBound(temp) - 1) vbaVlookup = temp Else Lrow = Range(Application.Caller.Address).Rows.Count For r = UBound(temp) To Lrow temp(UBound(temp)) = "" ReDim Preserve temp(UBound(temp) + 1) Next r ReDim Preserve temp(UBound(temp) - 1) vbaVlookup = Application.Transpose(temp) End If End Function
Download excel file
Recommended articles
Check out these posts and learn more about vlookup.
- Excel udf: Lookup and return multiple values concatenated into one cell
- Match two criteria and return multiple rows in excel
- Vlookup with 2 or more lookup criteria and return multiple matches in excel
- Using array formula to look up multiple values in a list
- Search for multiple text strings in multiple cells in excel
- Vlookup – Return multiple unique distinct values in excel
- Fuzzy vlookup (excel array formula)
- Lookup using two criteria in excel
- Search case sensitive and return multiple values in excel
- Vlookup a range in excel
- Vlookup with multiple matches returns a different value in excel
- Vlookup of three columns to pull a single record
Related posts:
Vlookup visible data in a table and return multiple values in excel
Vlookup – Return multiple unique distinct values in excel
Vlookup with 2 or more lookup criteria and return multiple matches in excel
Array formula to look up a value and return multiple values in excel
Vlookup with multiple matches returns a different value in excel
Recommended categories
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 lookup-vba3.
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,
I am not sure I understand but I think I covered your question in this post: http://www.get-digital-help.com/2009/12/29/vlookup-with-2-or-more-lookup-criteria-and-return-multiple-matches-in-excel/
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 excel-ing
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!
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 (Ctrl + c) and paste (Ctrl + v) array formula into formula bar.
Press and hold Ctrl + Shift.
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)
This is really great! Thank you so much!
I had another question for you though. Would it be possible to get the output in a row??
For instance I want the results to be displayed on Row A rather than column D?
Thanks again
Ramya,
Array formula in cell A1:
Copy cell A1 and paste to A1:A10
Really brill.....not very good at excel, but find this function amazing.
One question, instead of searching for words (such as pen, or eraser), how can i get it to search for a number - such as 124356.
Help much appreciated.
Matt
Sorry to bother you again...just wondering - how do i use the IFERROR function with this formula?
I need to add all the results of the above formula, but can not do so when it returns a #NUM value!
Thanks,
Matt
Matt Lyons,
The formula works for numbers also.
Example,
Array formula in cell C10:
How to create an array formula
Copy (Ctrl + c) and paste (Ctrl + v) array formula into formula bar.
Press and hold Ctrl + Shift.
Press Enter once.
Release all keys.
I Have figured out the iferror one, sorry.
Matt
thanks Oscar,
But i still can not get it to work for my numbers. example, table something like 12347 $1.70 , and looking to return the value $1.70, when it finds 12347, but just returns a #NUM
Matt
Sorry Oscar...i have it
thanks a million.
matt
Oscar,
Very sorry to bother you again, but on using this formula, along with the iferror function, i find that if the number i am looking for appears only once in the list - it returns a "0" value.
This is the formula i am using:
{=IFERROR(INDEX($B$1:$D$84,SMALL(IF(($B99=$B$1:$B$84),ROW($B$1:$B$84)-MIN(ROW($B$1:$B$84))+1,""),COLUMN(B11)),2),"0"}
Have you any idea why it is doing so?
regards,
Matt
Matt Lyons,
The bolded cell reference is a relative cell reference. It must have a cell reference to column A. When the formula is copied, the relative cell reference changes. Your formula has to be copied horisontally.
Array Formula:
How to create an array formula
Copy (Ctrl + c) and paste (Ctrl + v) array formula into formula bar.
Press and hold Ctrl + Shift.
Press Enter once.
Release all keys.
Thanks Carlos
So, would i be right in saying - when i copy my formula Horizontally,it needs to go from COLUMN(A1) to COLUMN(B1))to COLUMN(C1))etc? is this correct?
Matt
Sorry, I mean Oscar!!! its been a long day at EXCEL!!
Matt Lyons,
Yes, that is correct.
How to copy array formula
Select cell
Copy (Ctrl + c)
Select cell or cells to the right
Paste (Ctrl + v)
A relative cell reference changes automatically when the cell is copied.
Hi Oscar,
Did you write these guides? Just wanted to say they are pretty awesome. It is good that you are still hanging around after 2 years since it was published to help others.
Thanks!
Dan
Daniel,
Yes, I wrote these guides. Thank you and thanks for commenting!!
Hello,
So, I tried using this formula, but I think I ran into a problem. I have one table with text strings in it. From another table I want to find the rows that match with this text string from the first table and display that, but occasionally there are multiple rows in the second table that have this text string, and I want to see all the outcomes per text string. It seems to work similar to what you do here, but then my search is with text strings, and therefore the SMALL function does not seem to work. I looked at the other page on working with text strings, but the application I am working on is not the one displayed there, but the one displayed here. Can you help me to make this work?
Jessica
great job. i got mine working perfectly Oscar - thanks a million .
I was wondering, do you know how i can move 2 excel sheets to a different folder , without upsetting the formulas which are used linking both sheets ? (ie: each sheet uses the other sheet to extract data, but when you move them , neither work?)
Matt
Jessica,
The array formula on this page works with both numbers and text. If I understood your question, the array formula should do the job.
Try to explain in greater detail or send your workbook.
Matt Lyons,
I found this: Mass changing Excel links??
I have not tried it.
I am trying to look up multiple rows at one time. What formula do I use?
T.C,
I have changed this post,I hope you will find the answer more easily now. If not, explain your problem in greater detail and I will try to find or create the formula.
Hi oscar,
Please help me. What formula of vlookup i used to look up more value in another sheet if ever i enter id no.
Ex.Sheet 1
Id no Name Age
123 kia 30
234 ana 45
123 liza 65
879 meg 34
435 greg 13
I like this output in Sheet 2;
Id:______(if ever i choose 123)
output:Id no Name Age
123 kia 30
123 liza 65
thank you
anna,
read arnelias question
Thank you so much Oscar
Hi ocscar,
I try the formula you give to arnelia, but It does not work. Please help me. Please.. Send me a excel format please
thank you
anna,
Download excel file
Vlookup - return multiple records.xlsx
(Excel 2007 Workbook *.xlsx)
Hi Oscar,
Thank you so much....This is really great.
Tried using your formula on my project but am having some difficulties. First off had to change the "" to zero to get it to display a value, but now when it searches the price list it displays the first entry even though it doesn't match. It also won't display on the other cells when I copy and paste. If you can help in anyway I would greatly appreciate it.
=INDEX('Price List'!$A$3:$C$57000, SMALL(IF('Price Sheet'!$B$4='Price List'!$A$3:$A$57000, ROW('Price List'!$A$3:$A$57000)-MIN(ROW('Price List'!$A$3:$A$57000))+1, 0), ROW(A1)),COLUMN(A1))
BPV,
1, You can´t change "" to 0, then the formula returns the first record in your pricelist.
2, I am guessing here.. Maybe the value in 'Price Sheet'!$B$4 contains an additional space somewhere? Compare the values.
Hi oscar,
Thank you so much for help. But i notice the sample you send me, the database and output in one one sheet only, i try to separate the database and output using this formula:
=INDEX($A$2:$C$7, SMALL(IF($B$9=$A$2:$A$7, ROW($A$2:$A$7)-MIN(ROW($A$2:$A$7))+1, ""), ROW(A1)),COLUMN(A1)
But It does not work again. Please help me. Kindly send me again the excel sample that the output & database in separate sheet please please....Thank you
anna,
Download excel file
Vlookup - return multiple records.xlsx
(Excel 2007 Workbook *.xlsx)
1, You can´t change "" to 0, then the formula returns the first record in your pricelist.
2, I am guessing here.. Maybe the value in 'Price Sheet'!$B$4 contains an additional space somewhere? Compare the values
If I leave the "" in the formula I only returns a #VALUE!. I double checked and there is no additional spaces in the cell. Still not quite sure what the problem is.
Hi
This guide has gone directly to the top of my favorites. I've been loogin for this for so long!
But I still have a question.
I use this formula to put in all overdue invoices for my customers in a payment reminder letter.
So sometimes it is 1 invoice and sometimes it is 20 invoices or more.
Below the rows, where I am using your formula, I need to put in some plain text, but right now I has te be ind the very bottom of the page, and it doesn't look good when only one line is returned from the formula. Though it does look good when it is several rows.
Have you got any tips on how to make the area with formula flexible in how many rows it "occupies".
Hi Oscar....
THANK YOU SO MUCH.....
Hi,
is it possible to have the results in the same cell using a variation of your formula?..
I mean like:
John | x22 | John,Mark
John | x23 | John
John | x24 | John, George
Mark | x22 | John, Mark
George|x24 | John, George
The results separated by the comma in a single cell?...
And thanks a lot for your usefull code...
mcholst,
It is hard for me to give you advice without having seen your sheet.
anna,
you are welcome!
Ivan,
I believe this post describes what you are asking for: Excel udf: Lookup and return multiple values concatenated into one cell
VBA code:
Hello,
thank you for your fast reply...
I'm not very good in excel and udf, anyway i followed your adivice, took your code and recreate a module.
The strange thing is that it gives me a blank cell (I took off the name of the sheet cause i want the results in the same one where the data are)..
Did i miss something?
Ivan,
Download excel file
Lookup and return multiple values concatenated into one cell.xls
(Excel 97-2003 Workbook *.xls)
thank you a lot..
It works perfectly
Ok, in excel I want to lookup the value of column B where the value in column F equals "1" exactly. I then want to add up the value in B for each match until the table column F8:F202 has been checked for matches :
For example
Col B ----- Col F
10 ----- 1
1 ----- 1
0 ----- 0
1 ----- 10
2 ----- 1
I would expect the formula to return 13, due to "10 x 1", "1 x 1" and "2 x 1" matching = 10 + 1 + 2 = 13
To me, the formula should contain a LOOKUP and SUMIF statement.
I've tried several formulaes, but without joy. Your help would be appreciated.
Sean,
Hi oscar,
It possible can use vlookup only and will not use the index to get the output on this:
Sheet1
id name status
123 ANNA active
124 jhun separated
125 liza active
129 roy separated
789 mary separated
123 anna separated
Sheet2:
Enter id:123
123 ANNA active
123 anna separated
Hi Oscar,
Do you know whats wrong in my formula?
=INDEX(deletion,SMALL(IF($A$7=DELETION!$B$3:$B$500,ROW(DELETION!$B$3:$B$500)-MIN(ROW(DELETION!$B$3:$B$500))+1,""),ROW(B11)),COLUMN(B11)
Thank you.....
ANA,
Question 1
Sorry, I don´t know how.
Question 2
=INDEX(deletion,SMALL(IF($A$7=DELETION!$B$3:$B$500,ROW(DELETION!$B$3:$B$500)-MIN(ROW(DELETION!$B$3:$B$500))+1,""),ROW(A1),COLUMN(A1))
The cell references in column and row functions always start with A1.
hi Oscar
Thank you so much for your help. One question again if ever my output like this and i would like to hide #num. What formula i can used?
Enter id:123
123 ANNA active
123 anna separated
#num #num #num
Here's my use formula.
=INDEX(deletion,SMALL(IF($A$7=DELETION!$B$1:$B$499,ROW(DELETION!$B$1:$B$499)-MIN(ROW(DELETION!$B$1:$B$499))+1,""),ROW(B1)),COLUMN(B1))
Thank you in advance
ANA,
Excel 2007:
Excel 2003:
Hi Oscar,
THANK YOU SO MUCH.......
Oscar,
Only just got an email saying there is a reply.
Tried the formulae out and it worked a treat.
Is there a particular book on excel that would be an ideal guide on some of the questions you are asked, or is it a case of trial and error ?
Very much appreciated.
Séan
Oscar,
Now for another puzzle. I have a datasheet which I produce several pivot tables on worksheets within the excel document. One column is a hyperlink to an internal website :
http://companyweb/Lists/Sales%20Product%20Information/DispForm.aspx?ID=1
This is kept on a worksheet called "Pivot Data". To update this data, I cut and paste the values over the top of the current data, so on effect, the number of rows will vary.
Is there a way of making this cell automatically recognises the hyperlink and updates accordingly ?
When you refresh the data, so that each of the pivot tables are updated with the new data, is it then possible to make sure that the data of the hyperlink is shown as a valid hyperlink which can be clicked on and redirected., i.e. The worksheet "Customer Sales" is a pivot table based on the "Pivot Data" worksheet and the column title for the hyperlink data is "Notes".
I know I'm asking a bit much, but I'm sure there is a solution and at the moment I'm struggling to find one.
Regards
Séan
Hi Oscar,
First time here so please bear with me. I've searched just about everywhere for a formula that can help me. I'm using the
=INDEX($A$2:$C$7, SMALL(IF($B$9=$A$2:$A$7, ROW($A$2:$A$7)-MIN(ROW($A$2:$A$7))+1, ""), ROW(A1)),COLUMN(A1))
formula to return multiple records. I have tried to modify it but to no avail. Where your formula matches the cell in $B$9 exactly, I would want to simply put part of the value in $B$9 and let the formula return all that match it e.g. you use 123 and it shows you all the 123 and adjacent values but I would want to enter e.g. only 12 and would want it to return all values that contain 12.
The above is basically to be used to look for product codes. I have an array of 4 columns with product codes in 1st column and need e.g. all products starting with "CP-" and also displaying all adjacent values that come with the particular product code. Not sure if I'm being clear enough.
example:
product code...column2...column3...column4
MB-DKJ3475.....1122......N.........product description
CH-UYI2938.....125.......N.........product description
CP-DLK4378.....4258......C.........product description
CC-DDD3429.....26553.....C.........product description
CP-LKL4344.....33........C.........product description
I would want all the product lines starting with "CP-":
CP-DLK4378.....4258......C.........product description
CP-LKL4344.....33........C.........product description
Greatfull for any help that you might be able to provide.
Many thanks in advance
Regards
Coenraad
Sean,
Is there a particular book on excel that would be an ideal guide on some of the questions you are asked, or is it a case of trial and error ?
As far as I know, there is not much written about array formulas.
I got interested in array formulas when I discovered Dennis Wallentin website: http://xldennis.se/ . It is in swedish.
I am inspired by comments on my website, other forums and excel sites.
There is also a lot of trial and error.
Sean,
Now for another puzzle. I have a datasheet which I produce several pivot tables on worksheets within the excel document. One column is a hyperlink to an internal website :
http://companyweb/Lists/Sales%20Product%20Information/DispForm.aspx?ID=1
This is kept on a worksheet called "Pivot Data". To update this data, I cut and paste the values over the top of the current data, so on effect, the number of rows will vary.
Is there a way of making this cell automatically recognises the hyperlink and updates accordingly ?
Yes, import data from the web using web queries.
Here are some resources:
http://office.microsoft.com/en-us/excel-help/get-and-analyze-data-from-the-web-in-excel-HA001054848.aspx
http://www.openjason.com/2008/01/25/3-steps-to-scrape-the-web-with-microsoft-excel/
http://www.vertex42.com/News/excel-web-query.html
How to recognize url in sheet1 cell A1
Function Identifyaddress() As Boolean If Sheets("Sheet1").Range("A1").Value = "http://companyweb/Lists/Sales%20Product%20Information/DispForm.aspx?ID=1" Then Identifyaddress = True Else Identifyaddress = False End If End FunctionWhen you refresh the data, so that each of the pivot tables are updated with the new data,
Sure, use tables. They are dynamic. You need vba to refresh pivot tables automatically: Auto refresh a pivot table in excel
is it then possible to make sure that the data of the hyperlink is shown as a valid hyperlink which can be clicked on and redirected.,
You need a macro to accomplish this.
i.e. The worksheet "Customer Sales" is a pivot table based on the "Pivot Data" worksheet and the column title for the hyperlink data is "Notes".
Coenraad,
I believe you are looking for this post:
Lookup with multiple criteria and display multiple search results using excel formula
Array formula in cell A10
Cell $A$8 contains the search value.
How to create an array formula
Copy (Ctrl + c) and paste (Ctrl + v) array formula into formula bar.
Press and hold Ctrl + Shift.
Press Enter once.
Release all keys.
How to copy array formula
Select cell A10
Copy (Ctrl + c)
Select cell range A10:D12
Paste (Ctrl + v)
Oscar,
I'll look into. Thanks again.
What if the ID changes, and may not be the same in each cell ?
http://companyweb/Lists/Sales%20Product%20Information/DispForm.aspx?ID=1
or
http://companyweb/Lists/Sales%20Product%20Information/DispForm.aspx?ID=2
or
http://companyweb/Lists/Sales%20Product%20Information/DispForm.aspx?ID=....
Oscar,
Thanks a mil for that. Works perfectly. You saved me a major headache.
Thanks again and have a great day.
Oscar,
I think I have probably confused the issue. The data is on a worksheet within the document containing the pivot tables on other worksheets.
None of the data comes from the web, just that one column of the data may include the hyperlink provided ( "?ID=x" where x is a varaible ).
Basically, when I do a refresh of the data in the "pivotdata" worksheet, I need it to recognise the cell is a hyperlink. In addition, when I update the indivital pivot tables, it looks at the data in the "Notes" field and recognise it as a hyperlink.
Where do I put the "Function Identifyaddress()" information you gave.
Regards
Sean
Hi Oscar,
First, this formula is genius. And now that I buttered you up, here's my question. I wanted to use this but to have the data on another worksheet. So I:
=INDEX(Masterdata, SMALL(IF($B$2=colrow, ROW(colrow)-MIN(ROW(colrow))+1, ""), ROW(Master!B2)),COLUMN(Master!B2))
This works until I drag the formula down to include additional results and Master!B2 becomes Master!B3. At that point, I get a Number error.
I guess I would like to know why the number error occurs, but more importantly, if there is an error free way to use this formula to return data on another worksheet.
Thanks a mil.
KJ
Sean,
I am confused, it is difficult for me to understand without the workbook.
KJ,
The number error occurs because there are no matching cells left in formula.
Your formula must start with cell reference A1. Bolded in formula below.
Example,
Excel 2007, remove #num errors:
Oscar,
Thanks so much. Worked like a charm.
Hi, Thanks for valuable home page,
I want formula to find 2 valuse for the same ID, if I have long list with over 1000 ID's and want to find Anna and the status if she is active or not. How I should write my formula.
Thanks and have a great day.
Oscar,
Did you receive the workbook I emailed you ? If so, any joy with finding a solution to my problem ?
Sean,
Basically, when I do a refresh of the data in the "pivotdata" worksheet, I need it to recognise the cell is a hyperlink. In addition, when I update the indivital pivot tables, it looks at the data in the "Notes" field and recognise it as a hyperlink.
I am still confused. You want the data in column "Notes" being recognized as a hyperlink in a pivot table? So you can click an hyperlink a pivot table and it automatically opens a webpage in a webbrowser?
Correct on both counts. Does this sound feasible ?
Your help is very much appreciated.
Regards
Sean
Sean,
USING HYPERLINKS
Unlike external data ranges, active hyperlinks are not supported in PivotTable cells. The hyperlink is treated as text, but you cannot follow the hyperlink to a Web page or document, in Excel or Excel Services.
Source: Use a PivotTable report to make external table data available in Excel Services
Oh well
thanks for trying.
Kind Regards
Sean
thanks,
it really work for me...
now, i can make my accounting book more light
Hi I am using your formula where the data is in another tab but it does not seem to work for me.
=INDEX(Activities,SMALL(IF($B$3=NameID, ROW(NameID)-MIN(ROW(NameID))+1, ""), ROW(Data!C2)),COLUMN(Data!C2))
Activites = Data!C2:G27
NameID = Data!C2:C27
I am trying to return the same way as your example INDEX($A$2:$C$7, SMALL(IF($B$9=$A$2:$A$7, ROW($A$2:$A$7)-MIN(ROW($A$2:$A$7))+1, ""), ROW(A1)),COLUMN(A1))
Can you let me know what I am doign wrong?
Thanks
This is great. Thanks a lot for posting this. You've saved me a bunch of time.
Is it possible to use this to grab data from multiple sheets at once? I see how KJ has pointed it at a single different sheet, above, but my data is spread over 31 sheets all using the same template.
Thanks again.
I did not read the KJ post above. I changed to A1 and it worked just fine. Thanks again for showing the formula
Sean,
See this post: Follow hyperlinks in a pivot table
Andrew,
No, I have no idea how to accomplish that with array formulas.
Hi Oscar.
Your posts are really helpful and informative - and much appreciated. I have, however, got totally stuck with my example.
I have the following lookup values:
pen
eraser
paper
paper clip
and the following item list:
Allan's pen
eraser
Frances' eraser
Jenny's pen
paper
paper clip
pen
red pen
What I’d like to do is search for each lookup value in the item list and return all the items that contain that text string. For example, 'pen' would return (in adjacent cells):
Allan’s pen Jenny’s pen pen red pen
I have spent several days trying to tweak your examples, to no avail. Can you help or point me in the right direction, please? Many thanks.
Oscar,
I lifted your example verbatium and updated it for my 83 records. The if statement hangs on #value!. I moved it from below the data (row)and placed it in the first available column. The if statement works, but only on the respective row. Thus will not pick up the mutliple's?
Where'd I go wrong?
Thank you
You have got one hell of a great method of doing this. I had a found a similar solution some time ago, but that used a VBA custom formula to return arrays. This is better because there is no VBA. Thank you.
However, one big problem: Arrays take a very long time in calculating. I do not know why, but I had to find a solution. Turns out that making each cell an array is the wrong way of doing things.
I will give you my example:
I have a log of material receipts;
I have several sheets in excel that retrieves a list of receipts corresponding to a certain type;
The log has details of each receipt, e.g. date, reciept #, store, delivery type, qty, cost.
If I have say 60 types of materials and at least 30 rows on each sheet with 6 columns, I will have 10800 arrays. That is about 30 seconds of calculation. Far too many for practicality. So I made one array per sheet and I modified the formula so that the last two functions (row and col) have arrays as arguments. Much simpler then. But requires a little more explanation.
Thanks.
If I was not clear enough: Each sheet fetches receipts of a certain type from the log.
Help,
Completely perplexed. I can't alter the formula at all without getting the #value! error. What's wrong? I can insert into your example "tbl" but no alteration or edit or open or the formula without the #value!. Any suggestions would be nice?
RE,
Oscar,
I lifted your example verbatium and updated it for my 83 records. The if statement hangs on #value!. I moved it from below the data (row)and placed it in the first available column. The if statement works, but only on the respective row. Thus will not pick up the mutliple's?
Where'd I go wrong?
Thank you
Completely perplexed. I can't alter the formula at all without getting the #value! error. What's wrong? I can insert into your example "tbl" but no alteration or edit or open or the formula without the #value!. Any suggestions would be nice?
Suggestions:
Did you change cell references in the formula? Cell references are bolded:
The first formula must start with cell reference A1 (bolded), example:
Did you create an array formula?
Anas Hashmi,
Yes, you are right. Array formulas are too slow with large data sets.
Ok,ok...The Control, Shift, Enter thing I completely missed. After editing the formula you must re-enter formula via this key combination. As expected the formula is genius... 4 days later!
So thank you and can you now point me in the right direction. I have a column of cities (multiple), each city row has a color, red, green, blue, etc.. associated with it. The row and column formula works, but grows my data base in rows. I have my colors in columns and want to populate the color column as it applies to the city on a single row basis. The column formula works well, but I can't be assured that all colors will show up in their respective columns with in the city row.
Thus red green, blue, its possible that if green is not in the data, blue data falls into the green column.
Suggestions?
Any advise how to sort on city, and populate columns that apply to specific colors?
RE,
I am not sure I understand.
Download excel file
re-city-color.xlsx
From my comma delimited file dump:
Office# City Color Code ID
OF175 Ames Blue 672
OF175 Ames Red 8732
OF25 Oakmont Green 753
OF25 Oakmont Blue 8743
OF5 Omaha Red 634
OF95 Dallas Red 231
OF95 Dallas Blue 13244
OF95 Dallas Orange 13132
OF95 Dallas Pink 234
My Spreadsheet desired result
Office# City State Red Green Blue Orange
OF175 Ames Ia 8732 672
OF25 Oakmont Mn 753 8743
OF5 Omaha Ne 634
OF95 Dallas Tx 231 13244 13132
I'm trying to get from comma delimited rows to a consolidated row where the color code lines up and populates the cell with the ID.
Thank you for your time
Does this help?
Sorry it lost the formatting when I sent it,
CSV: Office# | City | Color |ID |
Excel: Office# | City | State | Red | Green | Blue | Orange
Oscar,
I guess it can't be done? Eh?
Re,
I am sorry, I somehow missed your comment.
Check out this file: re-city-color1.xlsx
Hi, Oscar...
I have been looking for help on "How to return multiple values using vlookup in excel" for one whole day... then I found this... Man!, you help me so much. Thanks for sharing your knowledge. God Bless
Erwin,
Thanks!
Hello,
Thanks for the helpful formula. I used this formula successfully, but now I wanted to get the data I missed with the formula. How would you add multiple IF/AND statements to the formula. For example add:
(IF($B$8<$B$2:$B$6,...),($C$8<$C$2:$C$6,...), etc.
Thanks!
Kevin,
I used this formula successfully, but now I wanted to get the data I missed with the formula.
What are you trying to do?
How would you add multiple IF/AND statements to the formula. For example add:
(IF($B$8< $B$2:$B$6,...),($C$8<$C$2:$C$6,...), etc.
Oscar, great post. One question: I am using the vba code you provided and it works great. I am dealing with a set of 110,000+ rows of data (2 columns) with about 17,500 unique lookup values and the remainder of the rows are unique values to be found. I need to use the vba that you posted but to have it output the data horizontally, not a vertical array. I am terrible with VBA. Any chance you could give me some pointers or tell me which part of the code to change. Any help would be much appreciated.
Ex:
35007000030002 354CSTRL
35007000030002 354CSTRU
35007000030002 402MRRW
35007000030003 404CHRK
35007000040000 402MRRW
Needs to become:
35007000030002 354CSTRL 354CSTRU 402MRRW
35007000030003 404CHRK
35007000040000 402MRRW
When looked up.
Sorry, my question was vague. Here is the setup of my worksheet:
I have 3 columns filled with percentages associated with W,X,Y,Z.
% red %pink % blue
10% 15% 30% W
25% 33% 80% X
40% 12% 66% Y
75% 4% 12% Z
I used your formula to find W,X,Y,Z meeting the below criteria:
>30% red >20% pink >50% blue
Y X X
Z Y
Now, I want to find one formula that will find the values not captured by the previous formulas. In other words, (% red <30%)(% pink <20%)( % blue <50%) = W
Thanks!
Jonathan ,
I have changed the existing udf on this webpage. It is now possible to return values horizontally.
Kevin,
Download file:
Vlookup-kevin.xls
Oscar, great job! keeping the thread alive for so long! This is a great one!
I have a question around merged cells. Lets say in your first example, Both Pen and Paper cost $5, so I have merged C2 and C3 into a single cell with the $5.
Now, in the search cell (B8), if I put in Pen, it will give me the correct asnwer. However, if I put in Paper it will not return the value, as opposed to the expected returned value of $5.
The question: is there a way to make this work with merged cells
Greatly appreciate you help.
Hass
Hass,
There is question I don´t know the answer to!
I would avoid merged cells as much as possible.
Hi Oscar,
The VLOOKUP "Return multiple values horizontally" is exactly what I need, however it is not working for me. I copied everything exactly as shown in your example, but all I get is "#VALUE!". I am using Excel 2010 on a PC. Has something changed in the 2010 version, or am I just missing something?
would it be possible for the formula to copy itself into other cells below depending on how amny unique references were found in the table?
Kieran,
That would require vba.
Janet,
Maybe you forgot to create an array formula? Instructions are in the post above.
Hi Oscar,
The array formula I used was the same as in your "Return Multiple Values Horizontally" example: "=INDEX($C$2:$C$6, SMALL(IF($B$9=$B$2:$B$6, ROW($B$2:$B$6)-MIN(ROW($B$2:$B$6))+1, ""), COLUMN(A1)))". I set up the table exactly as you show in your example as well, but it isn't working for me. All I get in cell C9 is #VALUE! Any ideas on why it isn't working?
Thanks.
Janet,
Download excel example file:
Return-multiple-values-horizontally.xls
I am trying to use the formula for a bigger range, C3:C40. I modified the formula as follows -
=INDEX('Resource Project Mapping'!C3:C40,SMALL(IF($B$4='Resource Project Mapping'!B3:B40,ROW('Resource Project Mapping'!B3:B40)-MIN(ROW('Resource Project Mapping'!B3:B40))+1,""),ROW('Resource Project Mapping'!A1)))
however I am getting the below results
#VALUE!
#NUM!
#NUM!
#NUM!
#NUM!
Can you please help?
DP,
You need to use absolute cell references (except the last cell reference):
Dear,
I want to copy the formula from first cell to its adjacent cells, but I want to keep the same value of the last cell to be multiplied with the above percentage, but when I paste the formula into adjacent cell it copies the value of the cell next to the one I need the value from
please advise
Hi Oscar,
I'm trying to use your vbaVlookup tool, and am having issues where by lookup columns are backwards from yours (switch the price and pen columns around), and I want to find all of the matches from the first column, not the second one. When I use the formula, it just returns a blank. It works fine if I switch the columns, but this is not ideal.
Here's an example:
A B
AAA GrpA
BBB GrpA
CCC GrpB
DDD GrpA
EEE GrpC
I then create another column elsewhere, with "GrpA" as a header, then in the second row of that column, my function looks like this:
{=vbaVlookup(C1,$A$2:$B$15,1)}
I figured the last parameter (1) was to look at the first column instead of the second.
If you could provide some help to get this working, that would be superb. Thanks!
Feras,
Can you describe in greater detail and some example values?
Ben,
download file:
Ben.xls
Oscar,
Is it possible to return the values in the next tab or sheet? instead of the same sheet?
Oscar, Pls ignore above.
I got how to do
Hi Oscar,
Thanks for this article... the formula has been a great help to me in setting up some of my worksheets. I have a question about it though:
=INDEX($A$2:$C$7, SMALL(IF($B$9=$A$2:$A$7, ROW($A$2:$A$7)-MIN(ROW($A$2:$A$7))+1, ""), ROW(A1))
Is it necessary to use the "MIN(ROW(array))+1" calculation? I was looking at a similar formula posted by Ashish Mathur and I noticed he didn't use the "MIN...".
Thanks
Timus,
You can also use MATCH(ROW($A$2:$A$7), ROW($A$2:$A$7)).
becomes
In fact, you can use: ROW($1:$7)
and the formula becomes:
Remember, if you then replace your cell references with named ranges, the last formula won´t work. It is not dynamic contrary to the other two formulas.
Thanks for commenting!
Hi Oscar, Thank you for your posting. My question is fairly basic. I recreated your table of "Return multiple values vertically" above and entered the exact formula. However, it doesn't work.
I entered in the C8 cell --> =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(A1)))
And excel gave me -- > #VALUE!
If I copied your formula directly from the formula bar to my sheet in C8 cell, excel would return #VALUE! as well. However, if I copied an entire cell, and paste into my C8, excel would return exact same value as your original table. I am using Excel Mac 2008 but I don't believe this would have anything to do with it. Please let me know. I really appreciate your time. Thank you very much.
Just want to tell you: THANK YOU! Your web is great, your are very generous. Thank you for sharing!
Dawisee,
You forgot to create an array formula.
How to create an array formula
1. Double click cell C8.
2. Paste formula
3. Press and hold Ctrl + Shift
4. Press Enter
Gab,
Thank you for commenting!
My formula no working, I follow your steps but there´s somethink wrong..., please give me a hand!
=INDEX($B$1:$C$700;SMALL(IF(($D$90=$B$1:$B$700);ROW($B$1:$B$700)-MIN(ROW($B$1:$B$700))+1;"");ROW(A1));2)
VIC,
I think you forgot to create an array formula.
=LEFT(INDEX($C$2:$C$13546,SMALL(IF($E$3=$B$2:$B$1300,ROW($B$2:$B$1300)-MIN(ROW($B$2:$B$1300))+1,""),COLUMN(A1))),11)
Hi Oscar,
Your code works perfect but hen I reach $B$1300 or more the result will be #VALUE! Please help!
thanks a lot,
jener
Jener,
adjust cell references (bolded):
Hi Oscar,
thanks for the reply,
I have no clue how to adjust it.
Can you give me a sample. lets say in Column B and C there are 100000 rows
Is this the right code for it:
=LEFT(INDEX($C$2:$C$100000,SMALL(IF($E$3=$B$2:$B$100000,ROW($B$2:$B$100000)-MIN(ROW($B$2:$B$100000))+1,""),COLUMN(A1))),11)
need your expert advice,
jener
Jener,
I thought you copied the array formula horizontally? You are using column(A1) in your formula.
Your example seems to be right.
Hi Oscar,
Im using the "Return multiple values vertically or horizontally (vba)". Right know I can get it to return 3 values in the the cells C9-C11. I have a lot of data to lookup and would like it to return more values vertically (maybe 10 values). When I try to copy the array to the next cells, I either get a error message og it will start returning the same values, is already shown in C9-C11. Can you help me? (Hope the question makes sense)
Ulrik
Hi Oscar,
First of all, thank you VERY much for the superb effort you put in for us Excel newbies to be better!
I am facing one problem in making this array formula run. My data table and output table are on different sheets. While the formula works perfectly when they are both in the same sheet, error is returned when they are on different sheets. Here's my formula that works:
=IFERROR(INDEX($A$2:$E$107, SMALL(IF(Dashboard!$C$6=$A$2:$E$107, ROW($A$2:$A$107)-MIN(ROW($A$2:$A$107))+1, ""), ROW(A1)),COLUMN(A1)),"No More Data Available")
Here's the one that does NOT work:
=IFERROR(INDEX('Business Events'!$B$2:$E$3177, SMALL(IF($C$6='Business Events'!$B$2:$E$3177, ROW('Business Events'!$B$2:$B$3177)-MIN(ROW('Business Events'!$B$2:$B$3177))+1, ""), ROW('Business Events'!B1)),COLUMN('Business Events'!B1)),"No More Data Available")
Data table is in sheet named "Business Events" and output table is in another sheet named "Dashboard". The filtering id is in cell C6 in the sheet "Dashboard".
Please help.
Thanks much!
Soumit
Soumit,
Does this formula work?
Ulrik,
I think the problem is how you enter the udf.
You can´t copy an array formula returning two or more values.
How to expand the array formula from cell range C9:C11 to C9:C21
1. Select C9:C21
2. Click in formula bar
3. Press and hold Ctrl + Shift
4. Press Enter
5. Release all keys.
Thanks for bringning this to my attention.
Hi Oscar,
Thanks much for your guide, it is helping out tremendously. I am wanting to alter your code a bit, and it seems like it should work but I am not getting the desired results. I would like to have an if or to check for another value. This is how I assumed it would look, but again, I'm not seeing the desired results. Assume C9 had 124.
=INDEX($A$2:$C$7, SMALL(IF(OR($B$9=$A$2:$A$7, $C$9=$A$2:$A$7), ROW($A$2:$A$7)-MIN(ROW($A$2:$A$7))+1, ""), ROW(A1)),COLUMN(A1))
I would like results for 123 and 124. I however seem to be getting every thing back as a result. What is going wrong? Thanks much for your help!
Mat,
($B$9=$A$2:$A$7)+($C$9=$A$2:$A$7) returns an array
OR($B$9=$A$2:$A$7,$C$9=$A$2:$A$7) returns a single value.
Beautiful! Thank you very much!
Thank you Oscar, worked perfectly.
Oscar, my apologies! The formula works exactly as (not) advertised, I screwed up on the accuracy of the id value. Basically the id matched no value on the data table, as a result the obvious error was displayed. Next time, I will make sure I am more diligent at my end before posting.
Anyway, thanks to you, I learned a great way to "query" an excel "Database"
Best regards-
Soumit
Hello,
I used the formula to get multiple values, and works great. My look-up values are available in the "Data" sheet. The result of look-up is in the "Summary" sheet. There is a percentage calculation in 'summary' sheet which is simple calculation like =g5/g7, and I want to sort values in descending order based on percentages. When I sorted results in the "summary" sheet, the sort was not applied. I guess it is because of "Index" formula. Frankly, I am lost as to how to apply sort. Could you please advise what should I do? Thanks for your help.
Best regards,
Prashant
Oscar,
thank you very much! It helped a lot to solve my problems!
Hi,
I am using your formula which works a treat and is very helpful. I wanted to ask could you show me how to move the lookup cell automatically down. I have unlocked it and copied it down but then when i copy the formula across the lookup cell moves and i have to individually have to update. Is there a way that I am able to change the formula so it will lookup the cell? I hope i have been clear. Thanks
Ak,
If the lookup cell is $B$1, change it to $B1.
More on absolute and relative cell referencing
Thanks for the quick response.
I had a look at the link but not sure what to implement. What i was trying to achieve is to use your formula to lookup multiple values and return them horizontally which is what your formula does.
My problem is that when i drag the formula down then it is locked to $B$1. If i change it then it works once dragging your formula down but when i drag across to see the other values the cell B1 is no longer the lookup, its cell B2. I have hundreds of lines so I didn't want to manually change and thought if you knew a way that can automate this in your formula?
Thanks
Ak
Ak,
Yes, cell reference B1 changes when you copy the formula.
Cell reference $B1 changes only the row number, it is locked to column B. When you drag the formula across it is still locked to column B.
Oscar - you are a gem, thanks so much, saved me so much time and helped me instantly!!! Thanks so much!!
OMG my whole world just changed after reading and trying out the above array examples. I've been pulling out my hair trying to find out how and why Excel doesn't accomodate such a function and, WHA-LAH!! Thank you GREATLY!!
Ak and Sun Kissed,
Thanks for commenting!!
I just wanted to say thank you soooo much. I have been trying to figure out this formula for 2 days!! I finally stumbled upon your notations above and had my formula problem resolved in an 20mins.
You are amazing
Dana,
Thank you for commenting!! I added a link to the explanation on the table of contents.
This worked very well. Your example was simple enough that I could extrapolate how to use it across multiple worksheets and how to copy it across multiple columns. Thank you.
Hello Oscar,
I have tried to use the vbaVlookup function stated above. I opened VBA using Atl+F11 in Excel 2007, I then opened a new module and pasted the text stated above into the module.
I then tried to use the function in excel and it returns the same value as the normal VLOOKUP function, the top most value associated with the lookup value. It is not going down the table to find the next lookup value for the output.
Any suggestions?
Thanks,
Scott
Hi Scott!
I then tried to use the function in excel and it returns the same value as the normal VLOOKUP function, the top most value associated with the lookup value. It is not going down the table to find the next lookup value for the output.
Any suggestions?
You have to enter the array formula in a cell range, example above: C9:C11. Then press and hold Ctrl + Shift and then press Enter to create an array formula.
Ethan,
I am happy you found it useful! Thank you for commenting!
Perfect! Thanks Oscar!
Oscar,
I've a question. please have a look at it.
Appreciated!
COUNTRY VISIT DATE NO OF VISITS CITY1 CITY2 CITY3 CITY4 CITY5 CITY6
AUSTRALIA 14-Mar-12 1 SYDNEY
ENGLAND 31-Mar-12 1 LONDON
USA 18-May-11 3 NY LA TDL
INDIA 19-Apr-10 3 DELHI BOMBAY HYD
Question:
If I lookup for the USA, the result I need is the city names in adjacent cells like below
Enter country name: USA NEWYORK LA TDL
I don't want the blank cells in between, is there any way around?
Appreciated!
Ahmed,
I don't want the blank cells in between, is there any way around?
What formula are you using?
Thanks Oscar!
Here is the formula i'm using
=INDEX($A$2:$I$5, SMALL(IF($C$11=$A$2:$A$5, ROW($A$2:$A$5)-MIN(ROW($A$2:$A$5))+1, ""), ROW(A1)),COLUMN(A1))
USA Wednesday, May 18, 2011 3 0 0 NY 0 LA TDL
USA Monday, April 19, 2010 3 DELHI BOMBAY 0 HYD 0 0
how can i attach my workbook, so that it becomes easy for you to understand what i exactly need.
Many thanks!
I just want to see the list of ONLY cities in adjacent cells,
Oscar?
I'm waiting for your response.
Thanks!
Ahmed,
See attached file:
Ahmed.xlsx
Hi Oscar,
I am trying to use your vba code to do the "return values horizontally" but it has been unsuccessful as when I tried to enter custom function array formula the cell range would all end up having the same formula as the first top right formula. I was wondering if you could help ...
I noticed that in your Array formula in cell C14:D14 you had the lookup_value as B8? (which is the cell that has text value "Return values vertically")? is this correct? I have been inputting the formula with reference to that first look up value....
Thanks so much and this is such a great helpful resource for excel!
Yuli,
I am trying to use your vba code to do the "return values horizontally" but it has been unsuccessful as when I tried to enter custom function array formula the cell range would all end up having the same formula as the first top right formula. I was wondering if you could help ...
You are right, there is an error with the cell references. They should have been absolute. Well, now they are, I edited the post.
I noticed that in your Array formula in cell C14:D14 you had the lookup_value as B8? (which is the cell that has text value "Return values vertically")? is this correct? I have been inputting the formula with reference to that first look up value....
You are right, another error.
Thanks for commenting!!
Hi Oscar,
Thank you so much for this wonderful example, it helps a lot. I have a 44000 rows spreadsheet, I put the formula in the cell it works great but eveytime I copy it and paste it to the next cell I have to hold crtl+shift and click on enter in order to get the result. Is there an easy way to do it?
this is the formula I am using
{=INDEX(Sheet1!$B$2:$B$15829, SMALL(IF(Sheet1!$A$2:$A$15829='Purchase Orders'!B150, ROW(Sheet1!$A$2:$A$15829)-MIN(ROW(Sheet1!$A$2:$A$15829))+1, ""), COLUMN(A1)))}
thanks for your help
Hi Oscar,
Thank you so much for the great example. I am working on a 45000 rows spreadsheet, I added the formula in the first cell and it worked like a charm but the things is when I try to copy and past the formula in other cells it wont work unless select the cell, put the cursor inside the formula box and hold crtl+shift then hit enter. Is there an easy way to copy the formula throughout the 45000 rows. Here is the formula I am using
{=INDEX(Sheet1!$B$2:$B$15829, SMALL(IF(Sheet1!$A$2:$A$15829='Purchase Orders'!B150, ROW(Sheet1!$A$2:$A$15829)-MIN(ROW(Sheet1!$A$2:$A$15829))+1, ""), COLUMN(A1)))}
Thanks for any feedback,
Moncef
Got it, the thing is the calculation tab in the tools-> option was changed from automatically to manually.
OSCAR,
Thanks alot!,
i've been away for sometime...
you solved my problem.
You are great!
Thanks again....
[Oscar Says:
April 9th, 2012 at 8:17 am
Ahmed,
See attached file:
Ahmed.xlsx
}
Ahmed,
Thanks for commenting!
Are you still answering questions?
Paul,
Yes I am still answering questions.
Thanks Oscar:
I am trying to use the Index formula in the first example, return multiple values vertically: I have a spreadsheet with 20 columns or so. My data begins in cell A1 and I have gotten rid of the headers. So my sample extends from A1 through AC29. The data to lookup in in column 1 and the info I want returned is in Column 2.
I am writing the formula on a different tab than the worksheet. I don't understand the reference to Row A1 at the end of your example because in your spreadsheet there is no date in Row 1 and it is not the intersecting points of where your data begins. I will also want to return data from columns 3 and 4 after I get the hang of this.
Paul
Oscar,
I have a little issue, I have read almost every post on this forum and still can’t a formula figured out. My sheet looks like this:
a b c
1
2 83301-3626 260
3 83301-0000 260
4 83301-5160 120
5 83301-8181 120
6 83311-9703 170
7 83316-5557 220
8 83316-5043 190
9 83318-5004 160
10 83325-5232 120
11 83328-5033 260
12 83328-5033 260
13 83333-0000 220
14 83335-5721 120
15 83338-2055 180
16 83338-2845 260
17 83338-2135 120
18 83341-2060 130
19 83347-0000 120
20 83348-0000 120
21 83350-9471 170
22 83350-9369 160
23 83350-9471 160
24 83350-9772 250
25
I need a formula that will look up any given value (zip code) in column “B” and return its respective value from column “C.” And then add those returned values together to produce one sum of the values for each zip code. So that the final result would look something like this:
83301 760 83311 170 83316 410 Etc…
This is only a small sample of the data pool that I am working with; normal size is usually in the high 100’s of randomly assorted zip codes. I would also like to be able to drop the additional 4 digits from each zip code without having to manually delete each one. Any help would be incredibly appreciated.
Hi Oscar!!! This post really helped me a lot!!!
I wanted to ask you something... I've been trying for days to "adjust" this same example to one I have. I would like the same output of this formula but searching for cells that CONTAIN a given word, instead of mathing for the exact same word...
Like in this case, instead of looking for cells that says only "Paper", I want the formula to give back not only the result for "Paper", but also the result for "Paper Clip"..
Thank you very much in advance and keep up the great job!!!
Tank,
I would suggest using a pivot table.
First drop the additional 4 digits from zip codes:
Formula in D2:
=LEFT(A1, FIND("-", A1)-1)
Then sum values with a pivot table.
Open attached file:
Tank.xlsx
Yair,
Read this post:
Search for a text string and return multiple adjacent values
Paul,
I am trying to use the Index formula in the first example, return multiple values vertically: I have a spreadsheet with 20 columns or so. My data begins in cell A1 and I have gotten rid of the headers. So my sample extends from A1 through AC29. The data to lookup in in column 1 and the info I want returned is in Column 2.
I am writing the formula on a different tab than the worksheet.
See attached workbook:
Paul2.xls
I don't understand the reference to Row A1 at the end of your example because in your spreadsheet there is no date in Row 1 and it is not the intersecting points of where your data begins.
ROW(A1) contains a relative cell reference, read step 3 in the explanation in this post.
I will also want to return data from columns 3 and 4 after I get the hang of this.
See Sheet3 in the attached file.
Hi Oscar, Love it! But have a question.
My array is going to constantly change, as it is pulled daily from an outside data source. As a result, I do not want to have to constantly update the range. In your 1st example, $B$2:$B$6 could be $B$2:$B12 tomorrow or $B$2:$B$20 the next for me.
I've tried naming the range, but It's not working. Any ideas?
Thanks SOOO much for putting this together.
What a pleasure to have an expert and considerate helper on hand.
Thanks,
Paul
You are awesome! I've been looking for an easy way to do this for a long time and yours works flawlessly!
Thanks again!
I do have 1 question. I haven't tried it yet and you may already have an article on it, but is it possible to use this to do a comparison on the value returned from the array with a different column on the cell you are doing the vlookup on?
I know this is probably going to be really complicated, but it would be really cool if I could do this. Your formula does this, but instead of returning 8 results in 8 columns. I just want the 1 result that matches b2 in the column next to it in the array. In your example if you added QTY to column D for your array and your results you knew the price of the item you wanted to return and just needed the QTY returned (rather than 2 prices, it just returns the QTY)
Here is how I would consider this working logically. Not sure if this is usful or not, but hoping it can clairfy the question.
if B14 = {B2:C6}
return {all cells in C2:C6 that match B14} (your function returns this)
if C2:C6 results match C14
return cell in D2:D6 that matches (will only have 1 match)
TJ,
Vlookup-dynamic-named-range.xls
shawn,
Try this:
Shawn.xlsx
Wonderful. Thank you!
Wow, even better! I don't even need VBS!
Any chance you have something written up on how that works? I'm a decent scripter and such but not really sure where you would start when building a formula like that. Been working with excel a long time and always get lost when people use index and min to come up with stuff.
I'm sorry if I'm missing something here but I'm looking at the return horizontally and I'm getting #value in return. Even the example file I downloaded returned #value if you simply enacted the formula. could this be becuase I'm using excel 2007? Also why does it look like you named the range as tbl?
Oscar,
First off thanks for doing what you do.
Sorry about the earlier post. I did'nt fully understand the creating Array formula part.
But now that I've got that down, I still get a #num in your example file that i downloaded. I recreated your example and recieved the same results. I think I might be missing something in the column reference. what does that mean? why not C1 or D25?
I feel like I'm sooo close to getting this
Oscar,
First off thanks for doing what you do.
Sorry about the earlier post. I didn’t fully understand the creating Array formula part, even though you said it like 20 times in the comments. But after reviewing the comments I still get a #num in your example file that I downloaded. I recreated your example and received the same results. I think I might be missing something in the column reference. What does that mean? Why not C1 or D25?
I feel like I'm sooo close to getting this
MR,
You can remove #num errors with IFERROR(array_formula, "").
Example array formula:
Oscar,
Hate to bother you again but have one more. I need to search in a column and find a value, then return the value below that value and the next say 2-4 values below that same value.
So I will have A1:A15 with values. In cell B1 I want to return the value I need, in B2 the value below the first, in B3 the value below that one and so on.
Thanks,
Paul
"Return multiple values horizontally" - I was looking for it. Fortunately I found this article in Google, otherwise I could have spent long hours on this. Thanks Oscar a lot.
Paul,
Formula in cell B2:
Copy cell and paste down as far as needed.
Explaining formula in cell B2
Step 1 - Return the relative position of an item in an array that matches a specified value
MATCH($B$1, $A$1:$A$15,0)
becomes
MATCH("GG", {"AA", "BB", "CC", "DD", "EE", "FF", "GG", "HH", "II", "JJ", "KK", "LL", "MM", "NN", "OO"}, 0)
and returns 7.
Step 2 - Add 1 to calculale the row number to the value below.
MATCH($B$1, $A$1:$A$15,0)+ROW(A1)
becomes
7 + 1
and returns 8.
Step 3 - Return the value of a cell at the intersection of a particular row and column.
=INDEX($A$1:$A$15, MATCH($B$1, $A$1:$A$15,0)+ROW(A1))
becomes
=INDEX($A$1:$A$15, 8 )
becomes
=INDEX({"AA", "BB", "CC", "DD", "EE", "FF", "GG", "HH", "II", "JJ", "KK", "LL", "MM", "NN", "OO"}, 8 )
and returns "HH".
Download *.xlsx file
Paul.xlsx
Monirul Islam,
Thanks for commenting!
Can't thank you enough again. Not only do you come up with the answers but this enabled me to develop a worksheet that saves and enormous amount of man hours where we used to do this work manually!!!
Paul
Dear Sir,
i have hundred names and different values of these names.
i want to know that how i can found three values with name which is equal to my questions value example is mention below,
NAME VALUE
ALI 200
SHABAZ 100
NAEEM 300
IRFAN 450
MUDASAR 670
WASEEM 750
JABAR 670
KHIZER 810
ADNAN 1050
SARFRAZ 1200
KHURAM 310
USMAN 220
I WANT TO KNOW WHOSE THREE VALUES ARE = 1780
NAEEM 300
MUDASAR 670
KHIZER 810
ANSWER IS 1780
it is necessary that value should be three of answer, it is also possible that the answers is equal to different three values example mention below.
NAEEM 300
MUDASAR 670
KHIZER 810
TOTAL OF THREE VALUE 1780
ABDUL 250
RAZAQ 680
AZEEM 850
TOTAL OF THREE VALUE 1780
please mention the formula how i can found these.
thanks
I am trying to get a single dollar($) value return from a selected item in a drop down list. My goal is to have several dropdown that will produce a single dollar value and calculate a total.
Paul,
You are welcome! Thanks for commenting!
khuram khalil,
See this post:
Excel udf: Find numbers in sum
Use this formula to find the related name to each value.
=INDEX(array, MATCH(value, array,0))
Thanks so much! This was EXACTLY what I was looking for!Will be posting a link to this on my site!
I would like to a) match the cell from column A with value in column C and b) transfer information from the cell B next to the cell A to column D based on name shown in column C.
Example:
Column A B C D
Alan absent Herb present
Carol present Carol present
Daniel absent Daniel absent
Anna present Alan
Herb present Anna
Thank you!!
Izabela,
Oscar,
You are awesome! Thank you so much!!!
I love this website!
Izabela
Thank you all for sharing!!!
[...] to wrap some additional functions around the solution presented above, such as that shown at The Get Digital Help blog (modified version shown [...]
Thanks for the great info about vlookups. I put it to good use in the creation of some of my own spreadsheet templates. Array formulas sure do come in handy don't they!
Hello Oscar,
Thank you for publishing this, it's been of great help. I'm still running into problems, particularly on Step 4 (the SMALL function). I am able to properly sort out the relevant records, but when it comes time to nest the IF statement within the SMALL function, I get returns of #VALUE! on the non-relevant records and #NUM! on the relevant records. If I do the Ctrl+Shift+Enter, they all turn into #REF!.
My formula on the 1st row is =SMALL(IF(Form!$C$7=Data!$A$2:$A$15945, ROW(Data!$A$2:$A$15945)-MIN(ROW(Data!$A$2:$A$15945))+1,""),ROW(A1))
Thank you!
Keyes,
I can´t find anything wrong with your formula. Does it work if you use a smaller range? Data!$A$2:$A$200
Oscar
Please help, you seem to be the man.
i am trying to do a lookup for a todays date, and then outputting a couple of colums data from the same row
Frans
frans,
Formula in cell F1:
=TODAY()
Array formula in cell E3:
=IFERROR(INDEX($A$2:$C$11, SMALL(IF($F$1=$A$2:$A$11, MATCH(ROW($A$2:$A$11), ROW($A$2:$A$11)), ""), ROW(A1)), COLUMN(A1)), "")
HI Oscar
Such a great site you have!
Im trying to understand the formula witht the vlookup with horizontal results, but im trying calculate in excel 2007 and i got an error. I review step by step and when the if formula evaluates the $B$8=$B$2:$B$6 the error comes up...
any idea what am I doing wrong here?
Me again
Im trying to ise the vbaVlookup formula on your file, and works fine for 3 horizontal values, but when I try with 4th or more results horizontal the formula returns the 1st results again and again..
is there a way to have this work for more results? Up to 20?
thanks
Alright
I found how to make it work but now I want to copy the formula for my next 1500 rows, its there a way to excel copy the vbavlookup having as a reference value the firs column of each line for the vlookup, and ddo it automaticaly?
Flan,
=vbaVlookup(B14, $B$2:$C$6, 2, "h")
$B$2:$C$6 is an absolute cell reference and does not change when you copy the cells containing the array formula.
But B14 is an relative cell reference and CHANGES when you copy the cells containing the array formula.
Did my answer help you?
Hi thanks for all your help, the data is already sorted horizontally so does this mean i still need an array code? How do I do a Vlookup to basically say...if this box says "X" then display these values.....
Many thanks
Harry,
If I understand you correctly you still need to enter the formula as an array formula.
How do I do a Vlookup to basically say...if this box says "X" then display these values.....
Can you describe in greater detail?
Oscar...I just want to shout out and say "THANKS!". I used the array formula to look up multiple values horizontally. This was exactly what I was searching for, it saved me hours of time. Thank you for sharing your knowledge. Gerry
Oscar..I've learned a lot from this website. It is really a great help. There's one more scenario though that I wanted to resolve. I see that the formula works and returns X number of results based on the number of times the formula is written on X number of cells. Is there a way (maybe in VBA) to determine how many results will be displayed based on how may instances? For example, if Pen exists 3 times, and Eraser 2 times, I don't have to enter the formula 3 times to view the results for Pen, and enter the formula 2 times to return the results for Eraser?
Result:
Column A Column B
Pen $1.50
$1.30
$1.70
Eraser $2.00
$2.10
iette,
Thank you!
I guess you want to avoid error values and use vba to enter the array formula in a variable cell range depending on how many results that will be displayed?
I think it is easier to use the IFERROR function:
How to remove #num errors
HI Oscar,
I made exactly same excel sheet as your example (the first one above) and copied the formula =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(A1))) in my sheet but got #value!. Excel told me that it could not read $B$2:$B$6 in $B$8=$B$2:$B$6. I don't know what is going on.
Jin,
Did you enter the formula as an array formula?
Hi Oscar,
Yes. I basically copied and pasted same example (the first one) as what you illustrated. Copied and paste the array formula in C8 but got the #value! issue. This is puzzled me for many hours.
Is there an error somewhere in the cell range $B$2:$B$6 in your sheet?
Hi Oscar,
Sorry for the late reply. The evaluate formula shows the following:
=INDEX($C$2:$C$6, SMALL(IF("pen"=#value, ROW($B$2:$B$6)-MIN(ROW($B$2:$B$6))+1, ""), COLUMN(A1)))
What do you think happened?
Cheers
J
I still think you forgot to enter the formula as an array formula. I get the same error when I evaluate the formula.
How to create an array formula
Hi Oscar,
Thanks very much! It is the array formula's problem.
Hi Oscar,
i'm using vba code, its working fine when i retrieve values [fetched by vbalookup] using vlookup on same sheet, but when i try to retrieve values [fetched by vbalookup] on different sheet it returns only first value, for other values it shows nothing. thanks in advance
its been fixed, sorry for the inconvenience, thanks for the great formula
Thank you in advance for your help, I hope to resolve this issue soon.I have a file, Cells A1:A50 have multiple e-mail addresses separated by ";". On Column B, I have a list of 1,000 e-mail addresses, each cell on column B has only one address. What I am trying to get to, is on Column C, to see which e-mails from cell A1 are found in the entire column B. Then which e-mails from cell A2 are found in the entire column B, and so on. If I need to send a spreadsheet please let me know. Thank you for your help.
I tried it and it worked, thank you very much! I was making it way too complicated. Sorry about asking twice, after I first hit the "add comment", it loaded a page with error on it and I could not see my question. Then I opened a new page and posted again.
[...] ROW($A$2:$A$7)-MIN(ROW($A$2:$A$7))+1, ""), ROW(A1)),COLUMN(A1)) (it's better explained on How to return multiple values using vlookup in excel | Get Digital Help - Microsoft Excel resource) The thing is that the forumla is super slow. It takes about 2-3 seconds per row in a document [...]
Thank you so much! I've used this so many times, but I've hit a wall for this new application I'm trying to use.
In this use, I'm trying to have the "IF" function evaluate on six possibilities. In this example, there are multiple people who can be the "Lead" on an account, and I want it to return the results on a subset of six of those accounts.
Evaluating the formula, it all works fine and dandy until the "OR" function, which turns the six arrays of {TRUE, FALSE, FALSE ...},{FALSE, TRUE, FALSE,...}... into {TRUE} instead of {TRUE, TRUE, FALSE...}.
I tried using it with only one criterion and everything works fine, so it's pulling the results from other files just fine. It's just this part that's the trouble! THANKS!
Here is the formula:
{=INDEX('[Flux Capacitor.xlsx]Potential Leads'!$AK$2:$AK$100,
SMALL(
IF(
OR($A$1='[Flux Capacitor.xlsx]Potential Leads'!$AK$2:$AK$100,
$A$2='[Flux Capacitor.xlsx]Potential Leads'!$AK$2:$AK$100,
$A$3='[Flux Capacitor.xlsx]Potential Leads'!$AK$2:$AK$100,
$A$4='[Flux Capacitor.xlsx]Potential Leads'!$AK$2:$AK$100,
$A$5='[Flux Capacitor.xlsx]Potential Leads'!$AK$2:$AK$100,
$A$6='[Flux Capacitor.xlsx]Potential Leads'!$AK$2:$AK$100),
ROW('[Flux Capacitor.xlsx]Potential Leads'!$AK$2:$AK$100)-
MIN(
ROW('[Flux Capacitor.xlsx]Potential Leads'!$AK$2:$AK$100))+1, ""), ROW(A1)),
COLUMN(A1))}
Taylor,
Change this:
OR($A$1='[Flux Capacitor.xlsx]Potential Leads'!$AK$2:$AK$100,
$A$2='[Flux Capacitor.xlsx]Potential Leads'!$AK$2:$AK$100,
$A$3='[Flux Capacitor.xlsx]Potential Leads'!$AK$2:$AK$100,
$A$4='[Flux Capacitor.xlsx]Potential Leads'!$AK$2:$AK$100,
$A$5='[Flux Capacitor.xlsx]Potential Leads'!$AK$2:$AK$100,
$A$6='[Flux Capacitor.xlsx]Potential Leads'!$AK$2:$AK$100)
to this
COUNTIF($A$1:$A$6,[Flux Capacitor.xlsx]Potential Leads'!$AK$2:$AK$100)
Oscar,
I used your "Return multiple values horizontally" formula, which worked great and gave me my desired data, plus the #num. I then added the "How to remove #num errors" formula. But now I am not getting any data, it appears the formula is viewing everything as an error. Where am I going wrong? The formula is below.
=IFERROR(INDEX($A$2:$L$7, SMALL(IF($B$9=$A$2:$A$7, ROW($A$2:$A$7)-MIN(ROW($A$2:$A$7))+1, ""), ROW(B2)),COLUMN(B2)),"")
Oscar,
Sorry, I was ablle to figure it out. Thanks. Great website.
[...] return the first value even if there are multiple matches, I made post a few years ago about this:How to return multiple values using vlookup in excelVLOOKUP(lookup_value, table_array, col_index_num ,range_lookup)=VLOOKUP(C2, A8:E17, 3, FALSE)This [...]
I want to copy a cell value including its format. just like copying a cell and paste it to another sheet using "paste special" -> "values and number formats". is there any excel formula to do this?
Donnie,
As far as I know, you can´t copy the formatting with a formula.
You could use the TEXT function to format a value but that won´t copy the formatting.
http://www.techonthenet.com/excel/formulas/text.php
Thanks Oscar,
however, TEXT function is far from what i expect in as "Paste special".
anyway i tried you vbaVlookup() function and it really great, however with your coding is there any possibility to store the cell properties into an array?
Donnie,
An udf can only return a value, it can not change the formatting.
then i just hope Microsoft would developed or add some option from their vlookup() function that would allow cell properties to be copied as well.
thank you very much for the information..
great site, very helpful.
God Bless
How to I look up a value (e.g. no 12345) in multiple sheets( i.e. whether number 12345 appears in Sheet 1, Sheet 2, Sheet 3, Sheet 4, Sheet 5) in one excel spreadsheet (i.e. whole document).
Is it a V look up function?
What do I type ?
MB,
Vlookup across multiple sheets in excel
Hi, How did the Row(A1) returned a value of 1?
Oscar,
I'm hoping you can help. I am trying to group a number of rows together by the first column, providing a union of the column values. In the example below, we are looking to Vendors V1-V3 to sell us some subset of products P1-P5.
; P1 ; P2 ; P3 ; P4 ; P5
V1 ; 1 ; ; ; 1 ;
V2 ; ; 1 ; ; 1 ;
V1 ; ; ; 1 ; ;
V3 ; ; ; 1 ; ; 1
Once transformed, I would like to see the following:
; P1 ; P2 ; P3 ; P4 ; P5
V1 ; 1 ; ; 1 ; 1 ;
V2 ; ; 1 ; ; 1 ;
V3 ; ; ; 1 ; ; 1
Thank you for your help!
Mike,
read this post:
Group a number of rows together by the first column
Oscar - great work and assistance (I appreciate all your replies). I didnt see this type of question, sorry if you already dealt with it.
I have a set of data, like the one you used in the original example that also has a column for the date of the transaction. I would like my Index-type formula to search for both the main item (the rep's name) and also if the date of the transaction falls in the date range).
Start Date: 11/26/2012
End Date: 11/30/2012 (both entered by the user)
Rep: John
Then the results, in each row/column of the 'result' section (INDEX formula results) would show results for John that occurred from 11/26 to 11/30 (including both dates).
Thanks for any help with the formula for that.
Jason
Jason C,
read this:
Lookup multiple values in different columns and return multiple values
[...] excel *.xlsx fileSBabu.xlsxLookup multiple values in different columns and return multiple valuesJason C asks:I have a set of data, like the one you used in the original example that also has a column for the [...]
Thanks a Lot
it was very help fill
Thank you for your help. I have found that the "INDEX($A$2:$C$7, SMALL(IF($B$9=$A$2:$A$7, ROW($A$2:$A$7)-MIN(ROW($A$2:$A$7))+1, ""), ROW(A1)),COLUMN(A1))" formula should do the trick. I am currently trying to apply this formula to both Excel 2000 and OOo Calc.
I have it working in Calc with the exception that it shows all entries of my order form instead of the ones that indicate a purchase.
Ex.:
What's going on: I have an input cell of E2 which you enter a number for the purchases made of some products from swiss colony. In the following example it is one. This should display all items with a purchase quantity of one. I used your formula with a slight modification of using ";" to seperate instead of "," since I am applying it to calc.
Results:
AC649 1 5 HOLIDAY PRETZEL TRIO 17.95 $17.95
AC528 1 12 FUDGE BROWNIE PUFFS 29.95 $29.95
Err:504 Err:504 Err:504 Err:504 Err:504 Err:504 Err:504
Err:504 Err:504 Err:504 Err:504 Err:504 Err:504 Err:504
AC519 1 15 COCONUT MACAROONS 17.95 $17.95
Err:504 Err:504 Err:504 Err:504 Err:504 Err:504 Err:504
As you can see I have been successful in listing the items that have a purchase quantity of 1, but I have not figured out why it is listing the ones with a quantity of "0" as Err:504.
Here is my exact formula:
INDEX(SwissColony2012.$B$4:$H$50;SMALL(IF($E$2=SwissColony2012.$C$4:$C$50;ROW(SwissColony2012.$C$4:$C$50)-MIN(ROW(SwissColony2012.$C$4:$C$50))+1;"");ROW(A1));COLUMN(A1))
also in order to get the items beyond the first I had to change the +1 in "MIN" to +2 for all cells below the first.
You will have to excuse my usage of calc as I must be able to work well in both excel and calc with the work I do. I plan on implementing this in calc next week as the holidays are preventing my access to excel.
Thanks in advance,
Tyron
Tyron,
I found this:
Code 504 : Parameter list error : Function parameter is not valid, for example, text instead of a number, or a domain reference instead of a cell reference
http://www.linuxtopia.org/online_books/office_guides/openoffice_calc_user_guide/openoffice_calc_General_error_codes.html
Can you evaluate (step by step) a formula in Calc?
Thank you Oscar for replying so quickly. I would have posted sooner, but due to the holiday my computer use has been minimal. I have discovered the problem. Unfortunately you must do the cntrlshiftreturn with calc as well. The excel formula works great. You helped me a great deal. Thank you again
Tyron
Hi Oscar
I am trying to use your horizontal formula to index/lookup 195 potentials, so my IF formula looks like this IF($B$220=$B$2:$B$196,ROW($B$2:$B$196)-MIN(ROW($B$2:$B$196))+1,""). Yet when i review the calculation steps the formula breaks down at the $B$220=$B$2:$B$196 stage. The cell accurately identifies $B$220 but returns #Value for the =$B$2:$B$196 which in turn errors the whole function.
Basically I am running a payment received report where multiple payments come in for multiple invoices per item. And we would like to break it down by the item fro quick auto adjustments. I can't post the data here due to the sensitivity of the info, but if you could assist that would be great. The items are numbers, not text, and I don't think this has anything to do with it but I am just throwing it out there to make sure.
Charles,
The cell accurately identifies $B$220 but returns #Value for the =$B$2:$B$196 which in turn errors the whole function.
How to create an array formula
I don't see how creating an array formula has to do with an IF formula not reading the correct value. I have everything verbatum, but the it seems unable to understand to look for a value in $B$2:$B$196
Charles,
If you compare a value to another value your approach works.
Comparing a value to multiple values requires that you enter the formula as an array formula.
i am using your 'vlookup return multiple records sheet' and when i enter the same array formula into the my sheet, i get #ref! for cells where i should be returning a result. what do you suggest? I did use CSE to enter the array. thanks!
Actually - scratch that I figured out how to get it to work... I am using your vlookup multiple records sheet in my own sheet, and have it so that all errors are blank, etc... my problem now is that i have ~5000 rows, which makes the function extremely slow... do you have any suggestions?
abigail,
I tried the formula with a "5000 rows and 36 columns" table and it is quite fast. I have a fairly slow computer.
How slow is extremely slow?
when i use the formula as well as the =iserror blank function it is around 10s to return
Hi Oscar,
Thank you so much for postings these wonderful tutorials. It has saved me so much time.
I have a case that I just do not know how to deal with.
Data include
Student Id, course, grade, citizenship mark, teacher
12345, english, a, s, s, ms. smith
23456, english, c, o, s, mr. lu
12345, art, b, o, s, mr. johnson
12346, enlgish, a, o, o, mr. lu
so if looking up with the student id 12345 how can excel return all the classes taken by the student with all the information in the same order in a row
result
12345 english, a, s, s, ms. smith, art, b, o, s, mr. johnson
Hahale,
I can´t solve it entirely.
They are in reverse order.
Download excel *.xlsx file
Hahale.xlsx
Thank you so much.
I can do a "paste special" values and readjust the columns.
Hi Oscar,
I have playing with the excel file that you've created for me.
One problem
the teacher name ms. smith should up twice if should have been ms. smith for english and mr. johnson for the art class.
How to correct that?
Thanks so much.
Hahale,
you are right!
Here is the corrected formula:
You're a genius!!! Thank you so much.
Oscar,
Thanks so much for your website! It's fantastic...
I just downloaded the Vlookup-vba3 doc and it's giveing me #VALUE! errors. I haven't edited it at all yet. I wondered what I might be missing. I enabled the macros...
Will,
I downloaded Vlookup-vba3.xls and I get #VALUE errors also. The cells show correct values until i click "Enable editing" in excel 2010. I am not sure why. Here is how you fix it:
1. Select a #value cell
2. Click in formula bar
3. Press and hold CTRL + SHIFT
4. Press Enter
5. Release all keys
Thanks!!!
I copied and pasted this into another sheet I wanted to use it in and I am getting the "#NAME?" error when I follow the instructions wothout spaces. I am using your UniqueFilterVBA and it's working great!
This is my formula:
=vbaVlookup(M2,N:O, 2)
But when I change it to (Including the space between "," and "N:0" it works. Is that normal?
=vbaVlookup(M2, N:O, 2)
Will,
I included spaces in the udf vbaVlookup and I am not getting a #NAME? error. (Excel 2010)
My formula is this =INDEX(Sheet1!$B$2:$B$14788, SMALL(IF(Sheet2!A2=Sheet1!$A$2:$A$19247, ROW(Sheet1!$A$2:$A$19247)-MIN(ROW(Sheet1!$A$2:$A$19247))+1, ""), COLUMN(B1)))
All works but when I copy it from cell B2 to I2 I get #REF error.
From B2 to E2 is ok but from F2 to I2 is #REF error
Please help...
Thank you for showing the excel awesome capability.
[...] This formula derives from one of the most popular blog posts here: How to return multiple values using vlookup [...]
Will this work with named ranges?
Shri,
yes, you can convert the cell references to named ranges.
This is brilliant. Thanks for sharing!!
Hey, hi i am new to VBA and array and so curious about it as it could help me so much in my excel. can you please suggest how to start
Manu,
Here is a great link:
http://www.cpearson.com/excel/arrayformulas.aspx
Thanks Osacar, I will look after it . Hope it will serve my curosity
Hi,
I am trying to return a value that meets multiple criteria and am having trouble doing so:
Workstream First Move Group Name
XMD 1 AAAAA95
Nice 2 AAAAA001
Nice 3 AAAAA002
Safe 4 AAAAA504
XMD 3 AAAAA505
Nice 2 AAAAA509
XMD 4 AAAAA510
Safe 1 AAAAA547
Nice 3 AAAAA548
Nice 2 AAAAA557
XMD 4 AAAAA559
Nice 1 AAAAA564
Nice 2 AAAAA565
I am trying to get the data to show all names that are part of Nice and part of first move group 3.
I've been playing around with nested if's and vlookups and nothing seems to be working.
Jeremey,
Array formula in cell F4:
=INDEX($C$2:$C$14,SMALL(IF(($A$2:$A$14=$F$1)*($B$2:$B$14=$F$2),MATCH(ROW($A$2:$A$14),ROW($A$2:$A$14)),""),ROW(A1)))
Hi Oscar,
Thanks for this formula and for this website, found good useful stuffs here with great clarity
got stuck with this formula, it works fine till 905 lines, but gets stuck up in line 906, dont know why ? Wish i could share my excel file, which would give you clear picture..
=INDEX($B$2:$B$5000,SMALL(IF($D2=$A$2:$A$5000,ROW($A$2:$A$5000)-MIN(ROW($A$2:$A$5000))+1,""),COLUMN(A1)))
Krishna,
upload your file here:
http://www.get-digital-help.com/excel-consulting/
Hi Oscar,
Received Your file with solution. Thanks for the quick help.
Oscar,
What a great site! I am trying to find a solution that will show vlookup multiple results vertically (like you show above) but it inserts a new row instead of fills the next existing row. This way I can list the results like below:
Reference Result
Pen 1.70
1.50 <--- new row inserted based on multiple result
Eraser 2.00
Paper Clip 1.70
Paper 1.50
Is this possible?
As a bonus, I also need to return multiple data points. (For example, Pen would return Price, Quantity and Model number horizontally)
Thanks!
Peter
Thanks a lot - you saved me a lot of time trying to figure this out myself. Please continue the good work!
Maryke
Thanks Oscar! This is an awesome guide to array functions. I actually understand your explanation, and was able to create my own array function now instead of just copy/pasting (then using trial and error)!
Thanks again!
Andy,
I am happy you like it!
Hi,
I'm using another formula and I get REFERENCE ERROR. How can I solved it. Here's my formula:
=INDEX(Test!$B$4:$I$439, SMALL(INDEX(($K$3=Test!$B$4:$I$439)*(MATCH(ROW(Test!$B$4:$I$439), ROW(Test!$B$4:$I$439)))+($K$3Test!$B$4:$I$439)*1048577, 0, 0),ROW(A4)),COLUMN(A4))
1584ZF92 BR SA 173 699.73 0 746 7
1584ZF92 MT SA 2 3.05 0 3 0
1584ZF92 RB RB 616 2859.15 0 3048 28
1584ZF92 RB SA 133 544.69 0 581 5
#REF! #REF! #REF! #REF! #REF! #REF! #REF! #REF!
#REF! #REF! #REF! #REF! #REF! #REF! #REF! #REF!
#REF! #REF! #REF! #REF! #REF! #REF! #REF! #REF!
war123,
The formula you are refering to is here: No more array formulas?
You can use the IFERROR function to remove #REF errors.
=IFERROR(INDEX(Test!$B$4:$I$439, SMALL(INDEX(($K$3=Test!$B$4:$I$439)*(MATCH(ROW(Test!$B$4:$I$439), ROW(Test!$B$4:$I$439)))+($K$3Test!$B$4:$I$439)*1048577, 0, 0),ROW(A4)),COLUMN(A4)),"")
THIS IS HELP A LOT FOR ME TO UNDERSTAND THE VLOOKUP...... THANKU SIR
Hello,
I wanna use Vlook up for more than 2million rows. But its not possible to copy & paste again the formulas for each row. So what do I do??
Rick,
I wanna use Vlookup for more than 2million rows.
Excel allows you to have maximum 1,048,576 rows.
But its not possible to copy & paste again the formulas for each row. So what do I do??
Why isn´t it possible, can you describe in greater detail?
I have 13,000 row reference numbers, in the formula below $O$7, that I want to compare to 600,000 rows of data. The Horizonal function works great, you are a genius, but how do I copy the formula without having to change the absolute reference each time (yikes 13,000 rows)? I tried changing the formula using a relative reference $O7 but then the formula did not work. (Yes I did (cntrl+c and cntrl+v) + enter (once).
=IFERROR(INDEX($E$7:$E$593288, SMALL(IF($O$7=$L$7:$L$593288, ROW($L$7:$L$593288)-MIN(ROW($L$7:$L$593288))+1, ""), COLUMN(A1))),"")
Thanks a million gazillion u r great!
Alan,
The following picture shows how to copy the horizontal function. You use the same technique with the array formula.
I hope this helps, thank you for commenting!
This formula works great. Your code can only pull up to 3 items, and I need it to pull a 4th? (HELP?
Can you help me with this?
Allan P,
1. Select the first cell
2. Type the formula in the formula bar
3. Press and hold CTRL + SHIFT simultaneously
4. Press Enter
5. Release all keys
6. Copy cell and paste the cell (not the formula) to the three cells to the right
7. Select all four cells.
8. Double click the black dot (see picture above)
I hope this makes sense.
I'm trying to use your array formula, which works great until the value that I am trying to lookup changes. Here is a sample of the data.
Name ID Results ID Tracking #
Test1 123 555 123 555
Test2 123 565 123 565
Test3 123 456 444
Test4 456 456 678
Test5 456 789 999
Test6 789 121 154
The formula entered into the Results column is:
{=IFERROR(INDEX($P$2:$P$7,SMALL(IF($M2=$O$2:$O$7,ROW($O$2:$O$7)-MIN(ROW($O$2:$O$7))+1,""),ROW(S1))),"")}
The only way I get the correct results for Test 4 and Test 5 is to change the last part from ROW(S4) and ROW(S5) back to ROW(S1), which is a pain considering the numbers for the real data changes every 2-3 rows.
I tried changing it to:
{=IFERROR(INDEX($P$2:$P$7,SMALL(IF($M2=$O$2:$O$7,ROW($O$2:$O$7)-MIN(ROW($O$2:$O$7))+1,""),ROW(S$1))),"")}
and this duplicates the first value found, such as Test 1, Test 2, and Test 3 will all have a result of 555. Then Test4 and Test5 both say 444. So it will not progress to the next number.
I also tried using ROW($S1) and I get the same results as show in the test above. Nothing I have done to this works.
Is there a way to correct this and to get it to continue to search the entire array?
That sample data looks wierd, so I wanted to upload it a little cleaner. Column S is an empty column.
These are Results with this formula:
{=IFERROR(INDEX($P$2:$P$7,SMALL(IF($M2=$O$2:$O$7,ROW($O$2:$O$7)-MIN(ROW($O$2:$O$7))+1,""),ROW(S1))),"")}
L M N
Name ID Results
Test1 123 555
Test2 123 565
Test3 123
Test4 456
Test5 456
Test6 789
O P
ID Tracking #
123 555
123 565
456 444
456 678
789 999
121 154
These are the results with this formula:
{=IFERROR(INDEX($P$2:$P$7,SMALL(IF($M2=$O$2:$O$7,ROW($O$2:$O$7)-MIN(ROW($O$2:$O$7))+1,""),ROW(S$1))),"")}
L M N
Name ID Results
Test1 123 555
Test2 123 555
Test3 123 555
Test4 456 444
Test5 456 444
Test6 789 999
O P
ID Tracking #
123 555
123 565
456 444
456 678
789 999
121 154
T F,
Array formula in cell N2:
Download excel *.xlsx file
T-F.xlsx
Thank you so much Oscar...that worked! I am speechless...I never would have thought of that!
Thanks!!
Hi,
I had issues with this when running the Module vbaLookup. It would continue displaying the first value until the formula eventually excluded the first result from the array, then finally moving to the next result. Example:
A B C D
1 John A
2 Mary B
3 Pete C
4 John D
5 Luke E
6 John F
John A A A D D F
(The result ideally would of been John A D F. Instead, I'm having to continue to extend the formula to get the result.)
Sorry, I figured it out!
However, a new problem has arisen...
The things I am looking up are sometimes large strings of text (it's a survey) and it seems from testing that a certain amount of text will break the formula.
Josh,
Excel version? How much is a certain amount of text?
Can you provide an example?