5 easy ways to VLOOKUP and return multiple values
The VLOOKUP function is designed to return only a corresponding value of the first instance of a lookup value, from a column you choose. But there is a workaround to identify multiple matches.
The array formulas demonstrated below are smaller and easier to understand and troubleshoot than the useful VLOOKUP function.
However you are not limited to array formulas, Excel also has built-in features that work very well, you will be amazed at how easy it is to filter values in a data set.
Table of Contents
- VLOOKUP - Return multiple values [vertically]
- VLOOKUP and return multiple values - case sensitive
- VLOOKUP and return multiple values - if not equal to
- VLOOKUP and return multiple values - if smaller than
- VLOOKUP and return multiple values - if larger than
- VLOOKUP and return multiple values - if contains
- VLOOKUP and return multiple values - if not contain
- VLOOKUP and return multiple values - that begins with
- VLOOKUP and return multiple values - that ends with
- How to count VLOOKUP results
- VLOOKUP and return multiple values - sorted from A to Z (link)
- VLOOKUP and return multiple values - unique distinct (link)
- VLOOKUP and return multiple values - based on criteria (link)
- VLOOKUP - Return multiple values [horizontally]
- VLOOKUP - Extract multiple records based on a condition
- Lookup and return multiple values [AutoFilter]
- Lookup and return multiple values [Advanced Filter]
- Lookup and return multiple values [Excel Defined Table]
- Return multiple values vertically or horizontally [UDF]
- Lookup and return multiple values in one cell (Link)
I have made a formula, demonstrated in a separate article, that allows you to VLOOKUP and return multiple values across worksheets, there is also an Add-In that makes it even easier to accomplish this task.
Now, if you only need one instance of each returned value then check this article out: Vlookup – Return multiple unique distinct values It lets you specify a condition and the formula is not even an array formula.
I have also written an article about searching for a string (wildcard search) and return corresponding values, it requires a somewhat more complicated formula but don't worry, you will find an explanation there, as well.
Did you know that it is also possible to VLOOKUP and return multiple values distributed over several columns, the formula even ignores blanks.
1. VLOOKUP - Return multiple values vertically
Can VLOOKUP return multiple values? It can, however the formula would become huge if it needs to contain the VLOOKUP function. The formula presented here does not contain that function, however, it is more versatile and smaller.
The image above shows you an array formula that extracts adjacent values based on a lookup value in cell D10.
Another great thing with this array formula is that it allows you to lookup and return values from whatever column you like contrary to the VLOOKUP function that lets you only do a lookup in the left-most column, in a given range.
Update 17 December 2020, check out the new FILTER function available for Excel 365 users. Regular formula in cell D10:
Read here about how it works: Filter values based on a condition
The following formula is for earlier Excel versions. Array formula in D10:
This video explains how to VLOOKUP and return multiple matching values:
The array formula in cell G3 looks in column B for "France" and return adjacent values from column C. The array formula in cell G3 filters values unsorted, if you want to sort returning values alphabetically, read this:
Vlookup with 2 or more lookup criteria and return multiple matches
How to create an array formula
- Copy array formula above. (Ctrl + c)
- Double-press with left mouse button on a cell.
- Paste (Ctrl + v) array formula.
- Press and hold Ctrl + Shift simultaneously.
- Press Enter once.
- Release all keys.
Read more
How to enter an array formula | Convert array formula to a regular formula | How to enter array formulas in merged cells
The array formula above filters only values with one condition, the following article explains how to filter based on multiple criteria: Vlookup with 2 or more lookup criteria and return multiple matches
If you don't like array formulas, try this regular but more complicated formula in cell D10:
Explaining array formula (Return values vertically)
You can easily follow along as I explain the formula, select cell D10. Go to tab "Formulas" on the ribbon and press with left mouse button on "Evaluate Formula" button. Press with left mouse button on "Evaluate" button shown above to move to next step.
Step 1 - Identify cells equal to the condition in cell B10
= (equal sign) is a comparison operator and checks if criterion (E3) is equal to values in array ($B$3:$B$7). This operator is not case sensitive.
$B$10=$B$3:$B$7
becomes
"France"={"Germany";"Italy";"France";"Italy";"France"}
and returns
{FALSE, FALSE, TRUE, FALSE, TRUE}
The image above shows an array in cell range D3:D7 containing boolean values, those values correspond to the logical expression if cell B10 is equal B3:B7. Cell B5 and B7 is equal to cell B10, these return TRUE. The other remaining cells is not equal to cell B10 and return FALSE.
Step 2 - Create array containing corresponding row numbers
The ROW function returns the row number based on a cell reference, we are using a cell reference that points to a cell range containing multiple rows so the ROW function returns an array of row numbers.
MATCH(ROW($B$3:$B$7), ROW($B$3:$B$7))
The MATCH function finds the relative position of a value in a cell range or array, however, I am using multiple values so this step returns an array of numbers.
MATCH(ROW($B$3:$B$7), ROW($B$3:$B$7))
becomes
MATCH({3, 4, 5, 6, 7}, {3, 4, 5, 6, 7})
and returns {1,2,3,4,5}
The image above shows the array in cell range D3:D7, the array always begins with 1 and has must have the same number of values in the array as the table has rows.
Step 3 - Filter row numbers equal to a condition
The IF function has three arguments, the first one must be a logical expression. If the expression evaluates to TRUE then one thing happens (argument 2) and if FALSE another thing happens (argument 3).
IF(($B$10=$B$3:$B$7), MATCH(ROW($B$3:$B$7), ROW($B$3:$B$7)), "")
becomes
IF(FALSE, FALSE, TRUE, FALSE, TRUE}, MATCH(ROW($B$3:$B$7), ROW($B$3:$B$7)), "")
becomes
IF(FALSE, FALSE, TRUE, FALSE, TRUE},{1, 2, 3, 4, 5}, "")
and returns {"", "", 3, "", 5}
The IF function replaces the numbers that correspond to boolean value FALSE with "" (nothing) and boolean value TRUE with a number, shown in cell range D3:D7.
Step 4 - Return the k-th smallest row number
To be able to return a new value in a cell each I use the SMALL function to filter row numbers from smallest to largest.
The ROWS function keeps track of the numbers based on an expanding cell reference. It will expand as the formula is copied to the cells below.
SMALL(IF(($E$3=$B$3:$B$7),ROW($B$3:$B$7)-MIN(ROW($B$3:$B$7))+1,""),ROWS($A$1:A1))
becomes
SMALL({"", "", 3, "", 5}, ROWS($A$1:A1))
This part of the formula returns the k-th smallest number in the array {"", "", 3, "", 5}
To calcualte the k-th smallest value I am using ROWS($A$1:A1) to create the number 1.
When the formula in cell D10 is copied to cell D11, ROWS($A$1:A1) changes to ROWS($A$1:A2). ROWS($A$1:A2) returns 2.
In Cell D10: =INDEX($C$3:$C$7, SMALL({"", "", 3, "", 5}, ROWS($A$1:A1))
=INDEX($C$3:$C$7, SMALL({"", "", 3, "", 5}, 1))
The smallest number in array {"", "", 3, "", 5} is 3.
In Cell D11: =INDEX($C$3:$C$7, SMALL({"", "", 3, "", 5}, ROWS($A$1:A2)))
=INDEX($C$3:$C$7, SMALL({"", "", 3, "", 5}, 2))
The second smallest number in array {"", "", 3, "", 5} is 5.
Step 4 - Return value based on row number
The INDEX function returns a value based on a cell reference and column/row numbers.
In Cell D10:
=INDEX($C$3:$C$7, 3)
becomes
=INDEX({"Pear", "Orange", "Apple", "Banana", "Lemon"}, 3)
and returns "Apple" in cell D10.
In Cell D11:
=INDEX($C$3:$C$7, 5) returns "Lemon"
This article demonstrates how to filter an Excel defined table programmatically based on a condition using event code and a macro.
1.1 Return multiple values - case sensitive
The image above demonstrates a formula in cell C10 that extracts values from cell range C3:C7 if the corresponding value in cell range B3:B7 is equal to the value in cell B10.
The values in cell B3:B7 must have the same upper and lower letters as the lookup value in cell B10 to generate a match.
Lookup value "france" is found in cell B3 and B7 but not in cell B5. Cell B5 has a value that begins with an upper letter. The corresponding cells to B3 and B7 are C3 and C7, those values are returned in cell C10 and cells below.
Array formula in cell C10:
Copy cell C10 and paste to cells below.
If you own Excel 365 you can use the much easier FILTER function to accomplish the same thing: Filter values based on a condition - case sensitive
1.2 Return multiple values - if not equal to
The picture above shows an array formula in cell C10 that extracts values from cell range C3:C7 if the corresponding value in cell range B3:B7 is NOT equal to the lookup value in cell B10.
The lookup value in cell B10 is not equal to the value in B3, B4, and B6. The corresponding values in C3, C4, and C6 are returned to cell C10 and cells below.
Array formula in cell C10:
Copy cell C10 and paste to cells below.
If you own Excel 365 you can use the much easier FILTER function to accomplish the same thing: Filter values if not equal to
1.3 Return multiple values - if smaller than
The image above demonstrates a formula in cell C10 that extracts items from cell range B3:B7 if the corresponding value in cell range C3:C7 is smaller than the value in cell B10.
In the example above, cells C5 and C7 are smaller than the value in cell B10. The corresponding cells are B5 and B7 which the formula returns in cell C10 and cells below.
Array formula in cell C10:
Copy cell C10 and paste to cells below.
If you own Excel 365 you can use the much easier FILTER function to accomplish the same thing: Filter values if smaller than
1.4 Return multiple values - if larger than
The picture above demonstrates a formula in cell C10 that extracts values from cell range B3:B7 if the corresponding values in C3:C7 are less than the value in cell B10.
In this example, cells C5 and C7 are smaller than the value in cell B10. The formula in cell C10 returns the corresponding values from B5 and B7.
Array formula in cell C10:
Copy cell C10 and paste to cells below.
If you own Excel 365 you can use the much easier FILTER function to accomplish the same thing: Filter values if smaller than
1.5 Return multiple values - if contains
The image above demonstrates a formula in cell C10 that extracts values from cell range C3:C7 if the corresponding values in cell range B3:B7 contain the value in cell B10.
In this example, the values in cells B3, B5, and B7 contain the value in cell B10. The array formula returns the corresponding values from cells C3, C5, and C7 to cell C10 and cells below as far as necessary.
Array formula in cell C10:
Copy cell C10 and paste to cells below.
If you own Excel 365 you can use the much easier FILTER function to accomplish the same thing: Filter values if contains
1.6 Return multiple values - if not contains
The picture above demonstrates a formula in cell C10 that extracts values from cell range C3:C7 if the corresponding values in cell range B3:B7 do not contain the value in cell B10.
In this example, the values in cells B4, and B6 do not contain the value in cell B10. The array formula returns the corresponding values from cells C4, and C6 to cell C10 and cells below as far as necessary.
Array formula in cell C10:
Copy cell C10 and paste to cells below.
If you own Excel 365 you can use the much easier FILTER function to accomplish the same thing: Filter values if contains
1.7 Return multiple values - that begins with
The formula in cell C10 extracts values from cell range C3:C7 if the corresponding values in B3:B7 begin with the same value as the value entered in cell B10.
The image above shows that cell B4 and B6 begins with the same value as the value in cell B10. The corresponding values in cell C4 and C6 are displayed in cell C10 and C11.
Array formula in cell C10:
Copy cell C10 and paste to cells below as far as needed.
If you own Excel 365 you can use the much easier FILTER function to accomplish the same thing: Filter values that begin with
1.8 Return multiple values - that ends with
Array formula in cell C10:
Copy cell C10 and paste to cells below as far as needed.
If you own Excel 365 you can use the much easier FILTER function to accomplish the same thing: Filter values that end with
How to remove #num errors
The picture above shows you the array formula copied down to cell D12 however there are only two values shown, the remaining cells show nothing not even an error.
Array formula in cell D10:
The IFERROR function lets you convert error values to blank cells or really in whatever value you want. In this case it returns blank cells.
Recommended articles
How to use the IFERROR function | How to use the ISERROR function | How to use the ERROR.TYPE function | How to find errors in a worksheet | Delete blanks and errors in a list
1.9 Count matching values
The following image shows you a data set in column B and C. The lookup value in cell E3 is used for identifying matching cell values in column B.
Formula in cell G3:
Alternative formula in cell G3:
Recommended articles
Count a given pattern in a cell value | Count cells containing text from list | Count cells between specified values | Count entries based on date and time | Count unique distinct values | Count unique distinct records |
2. Return multiple values horizontally
Update 17 December 2020, use the FILTER function to return multiple values horizontally. Regular formula in cell C10:
The formula above works only in Excel 365. The array formula below is for earlier Excel versions and is entered in cell C10.
Array formula in C10:
Copy cell C10 and paste to cells to the right of cell C10 as far as needed.
Enter the formula above as an array formula or use this regular but more complicated formula:
Recommended articles
Search values distributed horizontally and return corresponding values | Resize a range of values | Rearrange values | Rearrange cells in a cell range to vertically distributed values | Rearrange values based on category(VBA) | Normalize data (VBA) | Normalize data, part 2 |
3. Extract multiple records based on a condition
Update 17 December 2020, use the new FILTER function to extract values based on a condition, formula in cell A10:
The FILTER function is available for Excel 365 users and the formula above is entered as a regular formula.
The formula below is for earlier Excel versions, it extracts records based on the value in cell B9. Array formula in cell A10:
To enter an array formula, type the formula in a cell then press and hold CTRL + SHIFT simultaneously, now press Enter once. Release all keys.
The formula bar now shows the formula with a beginning and ending curly bracket telling you that you entered the formula successfully. Don't enter the curly brackets yourself.
Copy cell A10 and paste to cell range B10:C10. Then copy A10:C10 and paste to cell range A11:C12.
3.1 Watch a video where I explain how to use the array formula and how it works
Enter the formula above as an array formula or use this regular but more complicated formula:
Recommended reading
- Extract all rows from a range that meet criteria in one column
- Match two criteria and return multiple records
- Extract records where all criteria match
- Search for a text string in a data set using an array formula
- Filter unique distinct records
4. Lookup and return multiple values [AutoFilter]
The AutoFilter is a built-in feature in Excel that allows you to quickly filter data. The following video shows you how to quickly filter a data set, I don't think you can do it more quickly than this.
4.1 Instructions on how to filter a data set [AutoFilter]
- Press with right mouse button on on a cell value that you want to filter
- Press with mouse on "Filter" and then "Filter by Selected Cell's Value"
- That's it!
4.2 How to remove a filter
- Press with mouse on filter button next to header, shown in picture below
- Press with mouse on "Clear Filter From "Country""
- The AutoFilter buttons next to each header are still there.
- If you want to remove those as well, go to tab "Home" on the ribbon and press with left mouse button on "Sort & Filter" button, then on "Filter"
- The data set now looks like this:
5. Lookup and return multiple values [Advanced Filter]
The Advanced Filter is a tool in Excel that allows you to filter a dataset using complicated criteria combinations like AND - OR logic that the regular AutoFilter tool can't accomplish.
In this case I am only going to filter based on a single condition so this will be an easy introduction to the Advanced Filter in Excel.
- Copy the dataset headers and place them above or below your dataset, this to avoid confusion if the conditions disappear when a filter is applied.
Rows will be hidden and if a condition is on the same row it will be hidden as well. I created headers on row 2, see image above. - Enter the condition below the correct header you want to apply a filter to, I entered my condition in cell B3.
- Select cell range B5:C10.
- Go to tab "Data" on the ribbon.
- Press with left mouse button on "Advanced" button.
- Press with left mouse button on in Criteria range: field and select cell range B2:C3
- Press with left mouse button on OK button.
The image above shows the dataset filtered based on the condition used in cell B3. To clear the filter simply go to tab "Data" on the ribbon and press with left mouse button on "Clear" button.
6. Lookup and return multiple values [Excel Defined Table]
The image above shows you a dataset converted to an Excel Defined Table and filtered based on item "France" in column B.
- Select a cell in your data set.
- Press CTRL + T (shortcut for creating an Excel Defined Table).
- A dialog box appears, press with left mouse button on the checkbox if your data set contains headers.
- Press with left mouse button on OK button.
To filter the table follow these simple steps:
- Press with left mouse button on the black arrow next to a header name.
- Make sure the checkbox next to the value you want to use as a condition is selected.
- Press with left mouse button on OK button.
So why use an Excel defined Table? An Excel defined Table contains many more useful features.
- Enter a formula in one cell and Excel automatically enters the formula in the remaining Excel Table cells on the same column.
- Cell references are converted to structured references, for example a cell reference to column "Country" might look like this: Table[Country].
This is beneficial because you don't need to adjust cell references if your table grows or shrinks, the cell reference is the same no matter what. You don't need to use dynamic named ranges either. - Easy to filter and sort data.
- Easy to add or delete data, simply type your data below the last table row and the Excel defined Table will automatically expand.
- Use as data source for a chart and the chart will display what is filtered.
7. Return multiple values vertically or horizontally [UDF]
Make sure you have copied the vba code below into a standard module before entering the array formula.
7.1 User defined Function Syntax
vbaVlookup(lookup_value, table_array, col_index_num, [h])
7.2 Arguments
| lookup_value | Required. |
| table_array | Required. A cell reference to the data table you want to search. |
| col_index_num | Required. A number representing the column in the table_array. |
| [h] | Optional. Return values horizontally. |
Array formula in cell C14:D14:
7.3 Watch a video that explains how to use the User Defined Function
7.4 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
Recommended articles
Array formulas allows you to do advanced calculations not possible with regular formulas.
7.5 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
7.6 How to copy array formula to the next row
- Select cell range C14:D14
- Copy cell range
- Select cell range C15:D15
- Paste
7.7 Vba code
- Copy vba code below.
- Press Alt + F11 to open the visual basic editor.
- Press with right mouse button on on your workbook in the project explorer.
- Press with mouse on "Insert".
- Press with mouse on "Module".
- Paste code to code module.
- Exit vb editor and return to Microsoft Excel
'Name User Defined Function and arguments
Function vbaVlookup(lookup_value As Range, tbl As Range, col_index_num As Integer, Optional layout As String = "v")
'Declare variables and data types
Dim r As Single, Lrow, Lcol As Single, temp() As Variant
'Redimension array variable temp
ReDim temp(0)
'Iterate through cells in cell range
For r = 1 To tbl.Rows.Count
'Check if lookup_value is equal to cell value
If lookup_value = tbl.Cells(r, 1) Then
'Save cell value to array variable temp
temp(UBound(temp)) = tbl.Cells(r, col_index_num)
'Add anoher container to array variable temp
ReDim Preserve temp(UBound(temp) + 1)
End If
Next r
'Check if variable layout equals h
If layout = "h" Then
'Save the number of columns the user has entered this User Defined Function in.
Lcol = Range(Application.Caller.Address).Columns.Count
'Iterate through each container in array variable temp that won't be populated
For r = UBound(temp) To Lcol
'Save a blank to array container
temp(UBound(temp)) = ""
'Increase the size of array variable temp with 1
ReDim Preserve temp(UBound(temp) + 1)
Next r
'Decrease the size of array variable temp with 1
ReDim Preserve temp(UBound(temp) - 1)
'Return values to worksheet
vbaVlookup = temp
'These lines will be rund if variable layout is not equal to h
Else
'Save the number of rows the user has entered this User Defined Function in
Lrow = Range(Application.Caller.Address).Rows.Count
'Iterate through empty cells and save nothing to them in order to avoid an error being displayed
For r = UBound(temp) To Lrow
temp(UBound(temp)) = ""
ReDim Preserve temp(UBound(temp) + 1)
Next r
'Decrease the size of array variable temp with 1
ReDim Preserve temp(UBound(temp) - 1)
'Return temp variable to worksheet with values rearranged vertically
vbaVlookup = Application.Transpose(temp)
End If
End Function
Recommended reading
- Lookup multiple values in one cell [UDF]
- Fuzzy lookups [UDF]
- Filter an Excel defined Table based on selected cell [VBA]
- Filter words containing a given string in a cell range [UDF]
- Filter an Excel defined Table programmatically [VBA]
- How to save custom functions and macros to an Add-In
- Add your personal Excel Macros to the ribbon
Vlookup and return multiple values category
Question: How do i return multiple results for one lookup value? I have tried to use multiple vlookups, with no […]
VLOOKUP and return multiple matches based on many criteria.
This article demonstrates an array formula that searches two tables on two different sheets and returns multiple results. Sheet1 contains […]
Excel categories
485 Responses to “5 easy ways to VLOOKUP and return multiple values”
Leave a Reply
How to comment
How to add a formula to your comment
<code>Insert your formula here.</code>
Convert less than and larger than signs
Use html character entities instead of less than and larger than signs.
< becomes < and > becomes >
How to add VBA code to your comment
[vb 1="vbnet" language=","]
Put your VBA code here.
[/vb]
How to add a picture to your comment:
Upload picture to postimage.org or imgur
Paste image link to your comment.
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 opened. 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 opened 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, did you ever figure out how to accomplish this? I have been searching all over for this exact question and cannot find the answer. The equation works great but I need to use it thousands of times, and resetting the Row to 1:1 for every new search string is too cumbersome. Thanks.
Excel User,
I am not sure I understand but I think I covered your question in this post: https://www.get-digital-help.com/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 get 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 got 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.
Get 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
Get the example file
When I change the data of the above example as below, it cannot output correctly. Could you solve it?
-------------------------
Data:
Pen $1.50
Pen $1.50
Pen $5.00
Pen $18.00
Paper clip $3.00
Output:
Pen $1.50
$18.00
#NUM!
-------------------------
AY,
You are right! I uploaded a new file. Thanks for commenting!
I'm not even sure if this website is still active or if you would even receive this message but I will ask anyways!
I am needing to produce the same results that Gi99a needed but is there a way to produce them in a single cell instead of down the column?
Thanks in advance!
Thank you for this, it was really helpful.
ErikB,
Thanks for your comment, I appreciate it!
How to return multiple values using vlookup in excel and removing duplicates?
my sheet is setup as follows
A B C D E
1 Section Category item flavor size
2 food Coffee Espresso none Single
3 food Coffee Espresso none double
4 food Coffee Americano none Single
5 food Coffee Americano none double
i have tried the formula to return multiple values using the index example and worked fine with none duplicate item but how can i list them without the duplicate?
appreciate your help
Ahmed Ali,
Read this post: Vlookup – Return multiple unique distinct values in excel
Hey there, I'd just like to drop a bomb of thanks, this helped reduce my fear of ARRAY formulas man. This post was a BIG help!
Dear Oscar,
First of all thank you for sharing your expertise in excel. I am one who benefited from all your comments and formula here. I have some difficulties though from the below formula. Basically I'm using xcelsius 2008, and the problem is ROW function is not recognize by xcelsius, can please help me with the same concept of the below formula without using the row function? I'm using the below formula because i want the multiple results using vlookup. please help. Thanks
Marlon
=VLOOKUP($Y$1, INDEX($AA$38:$AO$51, SMALL(IF($Y$1=INDEX($AA$38:$AO$51, , 1), ROW($AA$38:$AO$51)-MIN(ROW($AA$38:$AO$51))+1, ""), ROW(7:7)), , 1), $AB$36, FALSE)
Marlon,
Sorry, I have no knowledge about xcelsius 2008.
Duuuude, you have no idea how helpful this was
Munir,
thanks!
Hi Oscar,
I am trying to implement this formual in my spreadsheet but it is just returning the first row and the rest of the rows are #num.
I have data in A2:B602. I entered the name to look up in cell c2. I want it to return all the data corresponding to that name in column D. Could you please suggest some work around or why the formula is not working
=INDEX($B$2:$B$602,SMALL(IF(($C$2=$A$2:$A$602),ROW($A$2:$A$602)-MIN(ROW($A$2:$A$602))+1,""),ROW(A2)))
Thanks
Ramya,
A minor change to your formula.
How to create an array formula
Copy array formula (Ctrl + c)
Double press with left moue button on a cell
Paste (Ctrl + v) array formula.
Press and hold Ctrl + Shift simultaneously.
Press Enter once.
Release all keys.
How to copy formula in cell D2
Select cell D2
Copy (Ctrl + c)
Select cell range D2:D10
Paste (Ctrl + v)
(Extend formula further down if range D2:D10 is too small)
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,
Vlookup-return-multiple-records.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,
Get the 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,
Get the Excel file
Lookup and return multiple values concatenated into one cell.xls
(Excel 97-2003 Workbook *.xls)
Hello,
Does this only work with values in a single sheet? Is there a way to make this work across multiple sheets? I am using the macro and have this =IFERROR(Lookup_concat(G243,IndMtch!$A$2:$K$1773,IndMtch!$A$2:$A$1773),"NA") and it is returning NA even though there are values that match in the areas specified. I am using Excel 2007.
Thank you for your assistance in advance!
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 :
https://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 pressed with left mouse button 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: https://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 :
https://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:
https://office.microsoft.com/en-us/excel-help/get-and-analyze-data-from-the-web-in-excel-HA001054848.aspx
https://www.openjason.com/2008/01/25/3-steps-to-scrape-the-web-with-microsoft-excel/
https://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 = "https://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 pressed 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 ?
https://companyweb/Lists/Sales%20Product%20Information/DispForm.aspx?ID=1
or
https://companyweb/Lists/Sales%20Product%20Information/DispForm.aspx?ID=2
or
https://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 press with left mouse button on 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.
Get the 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,
Get the Excel 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,
Get the 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,
get the 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. Press with left mouse button twice on 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. Press with left mouse button in formula bar
3. Press and hold Ctrl + Shift
4. Press Enter
5. Release all keys.
Thanks for bringing 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 press 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 got 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 got. 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 got. 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".
Get the Excel *.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.
https://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
https://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.
Get the 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 opened the Vlookup-vba3 doc and it's giving me #VALUE! errors. I haven't edited it at all yet. I wondered what I might be missing. I enabled the macros...
Will,
I opened Vlookup-vba3.xls and I get #VALUE errors also. The cells show correct values until I press with left mouse button on "Enable editing" in excel 2010. I am not sure why. Here is how you fix it:
1. Select a #value cell
2. Press with left mouse button on the 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:
https://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:
https://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 press with left mouse button on 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:
Get the 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?
Hi,
I used the non-array formula. I am trying to reverse engineer this formula and understand it. What is the aterisk for?
i.e. =INDEX($C$2:$C$5, SMALL(INDEX(($B$9=$B$2:$B$6)*(MATCH(ROW($B$2:$B$6), ROW($B$2:$B$6)))+($B$9$B$2:$B$6)*1048577, 0, 0), COLUMN(A1)))
Paul,
the asterisk multiples the arrays.
Example {0,1,1}*{0,1,0) returns {0,1,0)
0*0=0
1*1=1
1*0=0
Thanks Oscar! I really appreciate your taking the time to assist me.
I am using the formula for two different columns. The two columns are very different data. As far as I can tell, the formula is pulling the data from each column in the same order that it is on the source sheet. Is there any instance where this will not happen?
For examlpe:
Pen $1.50 10-13-13
Eraser $2.00 12-05-12
Paper $1.70 09-21-12
Pen $1.70 09-30-12
Paper Clip $3.00 05-08-11
Results:
Pen $1.50 10-13-13
$1.70 09-30-12
#NUM! #NUM!
What I need is for the date column to correspond correctly with the cost collumn. It appears that the formula is pulling everything in order, but I am curious if there is anything that could cause it to pull in a different sequence?
Thanks so much for your help!
On the 2nd formula, the problem I am having a hard time understanding is:
Match(ROW($B$2:$B:$6),ROW($B$2:$B$6))
In this example, ROW($B$2:$B$6) is equal to 2. Therefor wouldn't the formula in a sense be MATCH(2,2) which results in #N/A?
Paul,
ROW($B$2:$B$6) returns this array: {2; 3; 4; 5; 6}
Match(ROW($B$2:$B:$6),ROW($B$2:$B$6))
becomes
Match({2; 3; 4; 5; 6},{2; 3; 4; 5; 6})
and returns {1; 2; 3; 4; 5}
However, if you "Evaluate Formula" this array formula: =ROW($B$2:$B$6). Excel shows only the first value 2 but believe me, it returns an array: {2; 3; 4; 5; 6}
[IMG]https://i43.tinypic.com/e7du7b.jpg[/IMG]
Check this out hope someone can help me. I want to lookup those with the value of "1" but instead of the actual value being looked up the one nearest to the left is being looked up 0,10,20,30 instead! Thanks in advance to those who can help!
https://postimg.org/image/sv2g7fq47/
Sorry the picture did not get posted.
Thank you All, you guys have made my day. I got my problem solved and only owe you guys a kiss.
Thank you very much for this one!
Hi Oscar,
Thanks for this great guide.. this is my first foray into ARRAY formula and I have spent several hours looking through your various guides. Now, I've stumbled across a strange problem. Imagine Column A is full of Account numbers much like "id" in the sample above, where there may be several instances of each unique account number. Now, I am able to return the (multiple) desired values in Columns B and C for each instance of the account number in Column A... except for the very first account number.
So, imagine for only the "id" value 123, Rows 11 and 12 return #NUM! errors despite Row 10 correctly returning 123, ANNA, and active in Cells A10, B10, and C10 respectively. But if I were to enter 124 or 125, Rows 11 and 12 would correctly populate with the desired information (if, in the above example, they each had multiple instances as well). Sorry if this is confusing, but basically the formula is working fine for each Acct# I've tried except whichever Acct# appears first (or more appropriately, right under the header. Here is the formula below for reference:
{=INDEX($A$3:$C$23, SMALL(IF($B$28=$A$3:$A$23,ROW($A$3:$A$23)-MIN(ROW($A$3:$A$23))+1,""),ROW(A1)),COLUMN(A1))}
In this case, $B$28 is the Acct# I am interested in. I appreciate any help in this issue and please let me know if you require further explanation to make my problem clearer.
Thanks,
Shane
Shane,
Sorry, I am not following. Upload an example file.
Thanks a lot oscar !!
vinayak,
thanks for commenting!
Hi -
Your suggestions on using arrays to search in a group of data to return multiple rows/columns/ matches works well, but I am to the point where I have too much data and the array formula is too slow. Do you have a suggestion on how to get the same results but quicker?
Abigail
Abigail,
Thanks for your feedback.
Did you try the custom function?
Return multiple values vertically or horizontally (vba)
This post is awesome. Was able to alter the formulas to save me significant time trying to compare two lists with name variations...
Thanks!
Jeffrey Carpenter,
Thank you!
[…] It is exactly the same as in the source but doesn't seem to be working? Source: https://www.get-digital-help.com/how-to-return-multiple-values-using-vlookup-in-excel/ I tried INDEX(Sheet2!A:A,MATCH(Sheet1!A1,Sheet2!C:C,0)) also and that doesn't work plus don't […]
This is the best post I've found on Excel, I was getting tired looking for a horizontal Vlookup and multiple values! And I thought I knew my around Excel. Thanks Oscar!
Jack,
thank you for commenting!
Hello Oscar, i have a question for you, im wantch to index a column and return a value, but im want the one have certain word in the same file but other cell, excample
A B C D
1 truck / kms / type / oil
2 96 / 145 / change / shell
3 96 / 150 / recharge / shell
4 96 / 155 / change / chevron
5 96 / 158 / recharge / shell
-------- im want the most new entry of 96 and having in the C column de word " change "
this im want the 4 file only:
6 Truck / 96
7 Kms / 155
8 Type / change
9 Oil / chevron
how i can do this ?
Thank you for the help and time!
JOSE GARZA,
Array formula in cell B7:
=INDEX($B$2:$B$5,MATCH(LARGE(IF((B6=A2:A5)*(B8=C2:C5),B2:B5,""),1),B2:B5,0))
Array formula in cell B9:
=INDEX($D$2:$D$5,MATCH(LARGE(IF((B6=A2:A5)*(B8=C2:C5),B2:B5,""),1),B2:B5,0))
Get the Excel *.xlsx file
Jose-Garza.xlsx
wooahhhhhhhhhhhhhhhhhh!!!!
AMAZING !! ur genius!!
greatings from Mexico!!!
Hello, me again, sorry for disturb, this function doesn't work in different sheets ? im have a sheet called " oil " and the other " truck 96 " im put in " truck 96 " sheet this:
Turck:
Kms:
Type:
Oil:
im put the formula to pick the data from the sheet " oil " where are the records and trow #NUM error in the cell.
JOSE GARZA,
try this:
Array formula in cell B7:
=INDEX(oil!$B$2:$B$5,MATCH(LARGE(IF((B6=oil!A2:A5)*(B8=oil!C2:C5),oil!B2:B5,""),1),oil!B2:B5,0))
last question, who is the best way to make this case:
im have a in one sheet this:
" Truck 96 "
# Tire:
Brand:
Kms:
in other SHEET " tires " im have a list al the tires im buy, but im doesnt erase the old tires for have a record, but im have 4 trucks ejemple:
truck / # tire / Brand / Kms:
96 15 Michellin 54
97 16 Pirelli 89
96 78 Bridgeston 60
im want to have only the newset tire change, in this case in the sheet " truck 96 " i want to only have this
# Tire: 78
Brand: Bridgeston
Kms: 60
Vlookup? Match? what is the best option to do this ?
Saved me from long and exhausting reports' generating days!!
Thank you very very much!
Biba,
thank you for commenting!
Hello, and thanks you in advance!
I please need your help.
In your "Return multiple values horizontally" case, i need the same thing but istead i got like:
Pen1 $1.50
Pen2 $4.30
Eraser1 $2.50
Pen3 $0.25
Paper1 $4.05
I need all "Pen" in a horizontal row like:
Pen $1.50 $4.30 $2.50
I tried to use the "*" with your formula but didnt work.
If is possible to do it? and how?
Thank you!
Juancho,
Array formula in cell C8:
=INDEX($C$2:$C$6, SMALL(IF(ISNUMBER(SEARCH($B$8,$B$2:$B$6)), ROW($B$2:$B$6)-MIN(ROW($B$2:$B$6))+1, ""), COLUMN(A1)))
Get the Excel *.xlsx file
Vlookup-and-return-mutliple-corresponding-values-if-text-string-is-found-in-a-column.xlsx
Hello Oscar,
please i need ur help. I've gotten a results/grades computation sheet for a particular academic session. Everything has been sorted out but for only this:
Example
A B C D E F G H
1 124 John 85 60 45 30 72 40
2 A1 B2 D4 F6 A1 E5
3 129 Ken 60 35 58 42 20 65
4 B2 F6 C3 E5 F6 B2
5 145 Ann 87 95 21 25 48 62
6 A1 A1 F6 F6 D4 B2
it goes down like the above.
here is the challenge- I want to make a transcript sheet where id-no(124, 129, 145) will be in a dropdown list, once selected, needs it return all corresponding cells.
Expected Output- if 124 is selected from the dropdown list, i want cells B1:H2 returned as is found here.
Note- column A is id-no column
B is the name column
C:H is the column for scores obtained with attendant grades below.
hope this is clear enough... thanks
Olawale,
Get the Excel *.xlsx file
Olawale.xlsx
Thanks very much. You are a genius. So sorry for the delayed response. had to attend RCCG Congress in Nigeria.
Hello, thank you so much for your response, it was very very usefull and works pefectly.
Now i have another question:
I have 2 columns:
Column 1:
Pen
Glass
Paper
Chair
In Column 2:
Pen1
Pen2
Eraser2
Pen3
Table4
I need in column 2 to put if the articles exists or not in column 1:
The result is going to be like:
Column 2:
Pen1 YES
Pen2 YES
Eraser2 NO
Pen3 YES
Table4 NO
Thanks!
Juancho
Hi Oscar,
This formula worked great, thank you so much.
Only when i copy the formula into the cells below it is counting the same value every time instead of returning the #num error.
Am i doing something wrong? I dont want it repeating the same value each time.
Thanks.
Holly,
I don´t think you entered the formula as an array formula.
If you did, can you tell me which formula you are using?
Thanks for reply oscar, but I actually figured out a way to do what I needed using another one of your pages!
Also wanted to say thank you so much for what you do, your site is an amazing help and so much better than anything else out there.
Holly
[…] have had to do similar procedures in the past. I have found How to return multiple values using vlookup in excel | Get Digital Help - Microsoft Excel resource to be most helpful and I think you can tweak his formula to match exactly what you […]
Excellent tutorial, very helpful, thank God for people like you who are willing to share.
I am having an issue and I was hoping you would be so kind to help me, hopefully the image I am attaching can help to explain the situation.
Thank you so much!
subir fotos
heres the picture
https://s8.postimg.org/sx6b5ocmt/Sin_titulo.jpg
Luis,
I believe it is possible.
RE Return multiple values horizontally
Oscar, this solved my problem for the most part so thank you! Can you tell me- is there a tweak that can be made that would only return 1 of each instance horizontaly. E.g if you have multiple "1.50"s, "4.30"s and "0.25"s for Pen that it would just return 3 columns ie:
Pen 0.25 1.50 4.30
Thanks in advance for your help!
Karen,
Array formula in cell C10:
=INDEX($C$2:$C$8, SMALL(IF(($B$10=$B$2:$B$8)*NOT(COUNTIF($B$10:B10, $C$2:$C$8)), ROW($B$2:$B$8)-MIN(ROW($B$2:$B$8))+1, ""), COLUMN(A1)))
Thank you so much, appreciate the time you took to help!
[…] I have found this post nearly invaluable when extracting data from an unsorted list: How to return multiple values using vlookup in excel | Get Digital Help - Microsoft Excel resource […]
You are the master! Thank you so much for this post!
[…] This page has proven invaluable to me in looking up and returning values where MATCH usually fails: How to return multiple values using vlookup in excel | Get Digital Help - Microsoft Excel resource Please note that this formula is what's known as an array formula so if you might see a slow down […]
Hi Oscar,
Your post is probably the most useful one from any other posts available on Vlookup Functions. I'm not a techie and i'm still in the process of acquainting myself on Excel functions. I've tried to copy paste your above formula to reproduce multiple corresponding values vertically. However, I was unsuccessful. Here is what i need
Name Status
Anand Leave
Rahul Present
Kamal Leave
And the results in a different Tab.
Leave Anand
Kamal
Anand,
Perhaps you forgot to enter the formula as an array formula?
Hey Oscar,
Thanks for the information, used it several times and its been really helpful.
However, I have this new model that I need to use this but with a slight modification which I can't seem to be able to get it work.
Instead of having the IF function compare say if a "Pen", I'd like it to have a sort of range check. For example I want all the dates that fall within Oct-13 and Dec-13. I tried using an AND function within the If, but it doesnt seem to work.
I'd appreciate your help.
Thanks in advance
Mohammed,
Array formula in cell G5:
=INDEX($D$2:$D$7,SMALL(IF(($F$5=$C$2:$C$7)*($G$2<=$B$2:$B$7)*($G$3>=$B$2:$B$7),MATCH(ROW($C$2:$C$7),ROW($C$2:$C$7)),""),ROW(A1)))
Get the Excel *.xslx file
How-to-return-multiple-values-verticallyv2.xlsx
I have used the formula to Return multiple records and it works really well, however my dataset is quite large and it is slowing down the spreadsheet so it is nearly impossible to work with (I am using the formula to return multiple records from a separate worksheet with about 7000 rows.
Formula is:
=INDEX(Formatted!$A$3:$Q$7000, SMALL(INDEX(($B$1=Formatted!$A$3:$Q$7000)*(MATCH(ROW(Formatted!$A$3:$Q$7000), ROW(Formatted!$A$3:$Q$7000)))+($B$1Formatted!$A$3:$Q$7000)*1048577, 0, 0),ROW(C1)),COLUMN(C1))
Any advice much appreciates!
Is there a way where i can perform a loose vlookup? i.e. I am looking for Pen but i am willing to accept 'A Pen (Red)' and '123Pen123'?.
Charles,
Yes,there is.
Search for a text string and return multiple adjacent values
Dude, thanks man you are awesome!
Hi Oscar,
Thank you very much for this multiple vlookup value example. It helped me a lot in the excel i was working and I loved the explanation proved.
Thanks a lot..
Great stuff :)
Hi I'm having a hard time using this function to "join" two tables. This is what I'm trying to do:
I have two tables, for example. The first table is this:
id name
123 ANNA
124 jhun
125 liza
129 roy
789 mary
123 ANNA
And the second table is this:
id status
123 active
124 separated
125 active
129 separated
789 separated
123 separated
What function can I use in order to make sure for ID 123 I don't get "active" status both times.
What I've been doing so far is this (the third column below consists of =VLOOKUP(A2,$H$2:$I$7,2,FALSE)
id name status
123 ANNA active
124 jhun separated
125 liza active
129 roy separated
789 mary separated
123 ANNA active
Can you please help me? I would attach a file to show you exactly what I've been doing but I'm not given an option to attach.
Taca,
Array formula in cell I3:
=INDEX($E$3:$E$8, SMALL(IF(G3=$D$3:$D$8, MATCH(ROW($E$3:$E$8), ROW($E$3:$E$8)), ""), COUNTIF($G$3:G3, G3)))
Get the Excel *.xlsx file
Taca.xlsx
This is amazing, thank you so much Oscar!!!
Invaluable advice and share!
Have tried to manipulate your formulaes but not getting very far:
This is trying to get it working on the same sheet.. return a value where 'X' search criteria is based in the first column. I have done the array CTRL+SHIFT+ENTER but get a #NUM! returned..
=INDEX($A$4:$D$7, SMALL(IF($B$9=$D$4:$D$7, ROW($D$4:$D$7)-MIN(ROW($D$4:$D$7))+1, ""), ROW(C3)),COLUMN(C3))
Sceario: What I want to achieve is - I have a Sheet1 with source table data which I want to split the results into new Sheets
Sheet1: Source table data
A B C D E...etc
AAA 1 a x X
BBB 2 a
CCC 3 b X
DDD 4 b X
Sheet2: Return value column A-C where Column D='X'
Sheet2: Return value column A-C where Column E='X'...etc
Your insight would be greatly appreciated!
SODs law, I have figured it out! If only I invested the extra time before posting!
=INDEX(Summary!$A:$E, SMALL(IF($D$1=Summary!$E:$E, ROW(Summary!$E:$E)-MIN(ROW(Summary!$E:$E))+1, ""), ROW(Summary!E1)),COLUMN(Summary!E1))
Thanks for awesome forum! :)
Dear Oscar,
I have been trying all day to figure out how to return multiple records from "multiple sheets (same format)" by referencing your posts of the "return multiple records" and "Vlookup across multiple sheets in excel" and I couldn't figure it out still. Could you please help me to figure this out?
Brandon
Hi Oscar,
First of all, thank you for your tutorials and they have been very helpful at my work.
In your original example on stationery, what would the formula be if I added another column in column C, say brands where the first pen is AA, second is BB and the rest of the items CC. I would like the answer to be Pen BB price 1.70 and the other "". I would like my answer vertically on C9 and C10. Thanks in advance.
Hello Oscar,
Thanks a lot for your website, it is very helping.
I have used the formula in one on my document
=IFERROR(INDEX(data!$C$4:$C$1783,SMALL(IF(Sheet1!$A2=data!$B$4:$B$1783,ROW(data!$B$4:$B$1783)-MIN(ROW(data!$B$4:$B$1783)+1),""),COLUMN(data!A1))),"")
and it is working for almost all of them however there are few that have "0,00" instead of the actual text. The database is fine so I don't understand why some don't work like the rest...any idea?
Thanks,
Alice
Hi,
I have a table (see below) and I'm trying to create a table below it (not finished, but I added the 2 columns as an example). My goal is to create one formula in one field and just be able to drag it without changing anything. I've tried a combination of sum(if) and match and others but nothing seems to work. So far, this is what I've come up with C15={SUM(IF(MONTH($A$3:$A$10)=$A15,$B$3:$B$10,0))}
However, this only works for answers in column C, I can't drag it to other columns without changing the formula. Can you please help me?
New Old
A B C A B C
1/1/2014 3 1 12 6 3 56
1/2/2014 6 22 1 7
2/3/2014 4 26 7 2 34
2/4/2014 4 1 22 4 1
3/5/2014 2 17 21
3/6/2014 6 16 35
5/7/2014 2 1 8 6 67
7/8/2014 4 16 7 12
New Old
A B C A B C
1 January 9 1
2 February 1
3 March 8
4 April
5 May 2 1
6 June
7 July 4
Tatjana
[…] How to return multiple values using vlookup in excel | Get … – 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… […]
[…] How to return multiple values using vlookup in excel | Get … – 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…. […]
[…] The lookup function won´t return multiple matches. There is a workaround, see this link : How to return multiple values using vlookup […]
How does this work with a selection from a listbox on a user form?
Alexis
Data table:
Box ID Sku Sku Sku Sku Locations
A 123 123 456 123 Shop
b 123 456 789 123 dock
C 789 456 789 456 whse
D 123 123 store1
Results needed
Search sku: 123 box ID locations records
a shop 3
b dock 2
d store1 2
follow up
Hi Oscar, I am new to excel and arrays in particular.. What is the "1048577" for in your non-array formula? I think I follow the rest of it but I don't understand where this number came from.
Thanks!
the_nibs
What is the "1048577" for in your non-array formula?
There are 1048576 rows in excel, if I use a value above 1048576 the formula returns #ref for values that I don´t want to extract.
Hi Oscar
Your help is amazing in this regard. But I have some complicated table as I explained below:
Imagine we have three column: 1- some data referring to the brand of equipment
2- summary description of each brand in previous column
3- date of delivery of each equipment
I have a check box in my excel for each month. and also combo box for brands. by selecting one brand and one month, I want to show the list of delivered equipment on that month.
How can I do this?
Thanks a million
great site......
could you help me with the below query.....
I am doing vlookup, my ref column will have duplicate but I need all their corresponding items in one single cell line by line... can u help me on this....
Hi Oscar,
There are multiple columns in two different worksheets, one has more columns than another. I need to compare column F of worksheet 1 and column E of worksheet 2; if the value matches, compare column G of worksheet 1 and column F of worksheet 2; if the value matches, record the value in column N of worksheet1 from column M of worksheet 2. Please see example below.
Worksheet 1:
Column F Column G Column N
Item Code Item Sub-Code Bank Fee
1 0
2 0
4 0
8 0
28 0
Worksheet2:
Column E Column F Column M
Item Code Item Sub-Code Bank Fee
1 0 60
2 0 165
4 0 60
8 0 250
8 2 33
28 0 15
28 1 16.5
Appreciate your help in advance!
I have a very simple requirement.
My excel sheet has four values entered from A1:A4.
Say INFO, DEBUG, ERROR and TRACE.
I want a formula which looks the text and assigns a value to the same in a different column say from B1:B4.
Algorithm:
FOR All Values in A1 to A4,
IF (INFO, put 1 in colulmn
I have a very simple requirement.
My excel sheet has four values entered from A1:A4.
Say INFO, DEBUG, ERROR and TRACE.
I want a formula which looks the text and assigns a value to the same in a different column say from B1:B4.
Algorithm:
FOR All Values in A1 to A4,
IF (CELL value is INFO, put 1 in column B) ELSE IF (CELL value is DEBUG, put 2 in column B) ELSE IF (CELL Value is ERROR, put 3 in column B) ELSE IF (CELL Value is TRACE, put 4 in column B)
Dear Oscar,
If the data is spread horizontally as below:
Pen $1.50 $1.70 $1.90
Eraser $2.00 $4.00 $6.00
Paper $1.70 $1.90 $2.00
Marker S2.00 $2.10 $2.20
And then I need lookup with multiple return result vertically as below:
Pen $1.50
S1.70
$1.90
Is this possible?
Thanks in advance for your help.
Cheers,
Lis
Lis,
You can use this array formula as long as your values are numbers.
[…] sissey asks: […]
Dear Oscar,
Good Morning!
Thank you very much for your reply. I didn't realize that you have replied me coz I didn't receive the notification in my inbox.
So...I am so excited to see your reply Yayyyy:D
How about if my values are not numbers but texts?
Example:
Data spread horizontally
Dept-A Pencil Eraser Ruler Stapler
Dept-B Stapler Clip Pen
Dept-C Cutter Paper Stabilo
Dept-D Marker Glue
Return result vertically:
Dept-A Pencil
Eraser
Ruler
Pen
Is this possible?
Thanks in advance for your super help :D
Best regards,
Lis
i was trying to use your multiple return values formula in my excel workbook
=INDEX($A$2:$C$7, SMALL(INDEX(($B$9=$A$2:$A$7)*(MATCH(ROW($A$2:$A$7), ROW($A$2:$A$7)))+($B$9$A$2:$A$7)*1048577, 0, 0),ROW(A1)),COLUMN(A1))
but i need to return multiple values for multiple items
here B9 is locked and it is a reference. I need to get return values in row wise since the B9 is locked i wont get result if i enter id different cell.
can u help me out in this situation
Oscar,
This post was very helpful however I am searching for a way to adjust it to allow for a fuzzy search.
Searching for A2 "Procurement Manager"
In column B entries such as
"Procurement Manager"
"Procurement Manager / Warehouse Manager"
"Sales Rep / Procurement Manager / Warehouse Manager"
How can I make the search allow for fuzzy searches or wildcard characters?
Hi Oscar every time I copy and paste the formula horizontally the next instance always shows as "0" however the first instance is always bang on....any thoughts?
Btw - I had to use excel formula name manager to name my column arrays otherwise the column array would change every time I dragged the formula horizontally to the right to find the next instance...
grascase = 'grascase'J:J
bnnumber = 'grascase'N:N
=INDEX(grascase, SMALL(INDEX((M2=bnnumber)*(MATCH(ROW(bnnumber), ROW(bnnumber)))+(M2bnnumber)*1048577, 0, 0), COLUMN(A1)))
Hi Oscar,
how to use the formula when data is more that 1000 rows ,it gives #N/A if rows are select more than 700 .
=INDEX($H$2:$H$1500, SMALL(INDEX(($A$2=$G$2:$G$1500)*(MATCH(ROW($G$2:$G$1500), ROW($G$2:$G$1500)))+($A$2$G$2:$G$1500)*1048577, 0, 0), ROW(A1)))
Thxs
Hi Oscar,
Thank you for your post it saved my day. :)
Take care.
Thanks,
Sarunas
Sarunas
Thank you.
Hello Oscar,
This array formula works great! I'm new at this, so I would like to know how to use it in another sheet?
I would like to enter ID in sheet 1 and for it to pull the other info from sheet 7. If I try to combine your formula with something else I find on the internet, I'm just gonna mess it up :)
Thank you in advance!
Never mind, figured it out! :)
Thanks anyway for an excellent formula.
Hi oscar,
I have data in a separate excel for about 700 rows and 4 columns for eg.
types value1 value2 value3
apple 20 25 35
orange 15 20 25
grapes 22 26 31
I required the result in another excel as i enter the types it should display the values in the next 3 columns.
thnx.
Many thanks for this formula. Do you know if it can also return colored cells as a result of conditional formatting?
Your articles are very very useful. Thank you so much for the great service. Learnt something useful. Thank you once again.
vigneshwaran
Thank you.
Thanks for the formula Oscar, works a treat. My issue is that I have a spreadsheet with different lookup values in a column and as I copy your formula down, the ROW(L1) changes number therefore it won't work. Is there a way to loop it so that where there is a new value to lookup it reverts back to ROW(L1)?
Maddy
ROW(A1) returns 1 and extracts smallest row number based on a condition. When you copy cell C8 and paste to cells below, ROW(A1) changes to ROW(A2) and ROW(A3) and so on. This functionality makes it possible to get a new value in each 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)))
The issue with ROW(A1) is that if you insert a new row above the formula, ROW(A1) changes to ROW(A2) and this is not good.
The ROWS function takes care of this problem.
=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, ""), ROWS($A$1:A1)))
I have changed the formula in this post.
However, I am not sure I completely understand your question, can you describe in greater detail?
Oscar,
Big thanks for helping out. Very helpful page.
I am trying to use these formulas to filter out records and generate reports from sheet 1 to sheet 2. In order to filter records, I have an input cell where I can type "Mexico" and then your formula will pull all Mexico records. However, to be able to effectively search by multiple critera, I also need to be able to clear some criteria (essentially remove the filter, or filter for "all"). One way I have tried to do this is by searching for "**". However with the "=" function it seems you cannot search for partial matches using the * method. Using the Match formula you can search for partial matches using the *, but then the array does not work.
Example:
Lets say I want to pull all "No"&"Outstanding" records, but not filter by country. However, I may need to be able to filter by country later. Do you know how I could set this up?
** "No" "Outstanding (This is the row where I type in to filter)
Column A Column B Column C
Mexico Yes Done
US No Done
Mexico No Outstanding
US Yes Outstanding
Sensitive file, can't upload.
Thanks for your help.
Oscar,
Upon re-reading that was a very confusing request. Let me simplify and I can take it from there-
Is there a way to search for partial matches? Say that in your first example I wanted to search not for "France" but for "Fran*" - is this possible?
Thanks
JF,
Is there a way to search for partial matches? Say that in your first example I wanted to search not for "France" but for "Fran*" - is this possible?
Yes, there is!
https://www.get-digital-help.com/2012/03/28/search-for-a-text-string-and-return-multiple-adjacent-values/
You're the man
Thank you *so* much for your detailed examples and actively replying to users! I have a problem, which I've tried solving by editing your formula examples for 20+ hours without a success although I thought I could do it myself but apparently not, so here goes:
I have a long list of organizations that work in specific zip areas defined by zip ranges (start and end). One org might have multiple zip ranges and there can be overlap between organizations (i.e. one zip might "belong" to >1 org). Then there's another list that has got all the possible existing zips. I would need to have all existing zips falling inside the zip range of the organization added to separate columns on the matching row of the first list.
First list:
org name | zip range start | zip range end
org 1 | 00100 | 00200
org 2 | 00180 | 00250
org 1 | 00220 | 00230
Second list:
00100
00110
00190
00220
00225
Desired result:
org name | zip1 | zip2 | zip3 | zip n...
org 1 | 00100 | 00110 | 00190
org 2 | 00190 | 00220 | 00225
org 1 | 00220 | 00225
Perfect result:
org name | zip1 | zip2 | zip3 | zip n...
org 1 | 00100 | 00110 | 00190 | 00220 | 00225
org 2 | 00190 | 00220 | 00225
This would be of HUGE help if you could solve the problem. Thank you very much already for all the help, your examples have provided me with tons of new Excel wizardry skills.
Best wishes,
Eero
Eero S,
Read this post: https://www.get-digital-help.com/2017/11/01/dynamic-lookup-based-on-numerical-ranges/
Hi!
Your formula for transposing match results is great, but I'm looking to search for a value in a string, for example searching "Apple" and returning cells that contain "Apple, Pear" or "Orange, Apple" as well as just "Apple"
=INDEX($C$2:$C$5, SMALL(INDEX(($B$9=$B$2:$B$6)*(MATCH(ROW($B$2:$B$6), ROW($B$2:$B$6)))+($B$9$B$2:$B$6)*1048577, 0, 0), COLUMN(A1)))Sorry, forgot to mention I'm opting for formula over array as array seemed to slow down excel (there are 15 columns and 200 rows in the table)
Phill,
I believe this post demonstrates what you are looking for:
https://www.get-digital-help.com/2012/03/28/search-for-a-text-string-and-return-multiple-adjacent-values/
Hi Oscar,
I have a value error when making the multiple lookup horisontally.
I want to return dates. Is that the reason why?
Bo,
No, I don't think so. Can you post your formula?
Hello Ive tried to use your formula on my worksheet and i always receive a value error, even when opening the attached excel spreadsheet when I would refresh it it value errors out. Is there something wrong with my settings?
John
What happens if you evaluate the formula?
1. Go to tab "Formulas" on the ribbon
2. Press with the left mouse button on "Evaluate Formula" button
3. Press with left mouse button on "Evaluate" to iterate through calculations
Hi Oscar,
Love your vba formula. It works out perfectly! However, I have a file that has around 2000 rows with 5 columns to copy the array formula. It's exactly just like your example, except I have too many rows to go through. Is there a way to have another vba formula to auto "copy paste" all of them?
Thank you so much in advance!
CTW,
You can select the cell range you want to use before you enter the User Defined Function. Then type the UDF in the formula bar. Press CTRL + Enter to automatically enter the formula in each cell in your selected cell range.
It is also possible to select the cell range using the Name box next to the formula bar. Type the cell range and press Enter. The cell range you entered is now selected.
Hi Oscar nice website, just love it but i have difficulties when try it on from multiple sheets, did you have an example for multiple results from multiple sheets? Thanks Oscar and best regards
[…] Ahmed Ali asks: […]
Hi Oscar,
You video using the vlookup value has been very helpful but when I try it for large number of datas some of the values are not counted. Could you please tell where i am going wrong. TIA
Farhana,
can you tell me which formula you are using?
Hi Oscar,
Thank you for the in depth article breaking down array formulas & multiple matches. I have used this in the past without a problem. but this time round I am having problems. I am ONLY receiving #NUM! results.
Sheet1 is the tab I am trying to pull results to.
Sheet2 is where I am trying to pull the data from.
Any Ideas what I am doing wrong?
=INDEX(Sheet2!$O:$O,SMALL(IF(Sheet1!$F1=Sheet2!$F:$F,ROW(Sheet2!$F:$F)-MIN(ROW(Sheet2!$F:$F))+1,""),COLUMN(Sheet2!M1)))I traced it back!it wasn't an issue to do with anything in this formula.
Column F in the search field was pulling the data from somewhere else & I had selected the wrong data to pull into that column for then further use.
All sorted now :)
Hi Oscar,
Thanks so much this is a great resource! I am trying to add an AND condition to the IF statement (this is on the very initial line of code that is provided on this page) in order to check that two conditions are met before getting the adjacent value.
I am having trouble with adding this AND condition, do you know how I can check that two columns meet a condition and then return the value in an adjacent column before moving on to the next?
Much appreciated!
Isaac
Isac,
thank you!
I believe the following articles answer your question:
https://www.get-digital-help.com/2017/12/21/index-and-match-multiple-criteria-and-multiple-results/
https://www.get-digital-help.com/2017/11/29/index-match-with-multiple-criteria/
Hi Oscar - This is probably a dumb question. I was with you up until ROWS($A$1:A1)) at the end of the first example. Is this just a "helper" column? I assume it should be blank?
Thank you for your great site.
Tom
ROWS($A$1:A1) is there to keep track of which row number to extract. We don't want to extract the same value again and again. ROWS($A$1:A1) returns 1 in the first cell and the smallest row value is extracted.
$A$1:A1 changes to $A$1:A2 when you copy the cell (not the formula) and paste it to the cell below. ROWS($A$1:A2) returns 2 and the second smallest row number is extracted.
$A$1:A2 changes to $A$1:A3 in the next cell below and ROWS($A$1:A3) returns 3. Now the 3rd smallest row number is extracted from the array.
G’day Oscar, great work on the site, full of helpful formulas!! I have used one and need assistance in modifying it to suit my needs.
I am using the ARRAY formula to return multiple outcomes from multiple criteria. It works correctly. However, now I would like to determine whether an outcome fills two other criteria also. The formula should go down the list to determine whether each line fills certain criteria. Then, if it does, does if fulfil two other criteria? If so, then display, else ignore and move onto the next line in the list.
For example – my data set has a classroom number, each with a count of desks, chairs and computers. The formula asks for a list of all classrooms that have so many desks, so many chairs and so many computers. However, if the returned classroom number that fills all three criteria also fills the criteria that if there are zero computers and a fourth criteria of “yes” there are students, then display nothing and move onto the next returned classroom number.
So, it seems to me to require a formula that has an array formula nested within another array with an IF/THEN…
I tried this -
{=INDEX('Main Data Tab'!A:A,
IF(AND('Main Data Tab'!$F$2:$F$29="0",$B$15="yes"),"",
SMALL(IF(('Main Data Tab'!$B$2:$B$35>=$B$6)+('Main Data Tab'!$C$2:$C$35>=$B$9)+('Main Data Tab'!$D$2:$D$35>=$B$12)=3,ROW('Main Data Tab'!$A$2:$A$35)),ROW('Main Data Tab'!A6))))}
(I have placed the relevant section by itself for clarity.)
The section ought to search out the zero computers and yes for students to display nothing and move to the next record, but it doesn’t; it simply returns the results from the first three criteria without looking at the other two criteria.
I placed it first in this instance but I have also tried placing it last.
Hi Leon
First, why isn't this cell reference 'Main Data Tab'!$F$2:$F$29 as large as the others? For example, 'Main Data Tab'!$B$2:$B$35
Second, Main Data Tab'!$F$2:$F$29="0" returns an array of values but you want only a value corresponding to the found value if I understand correctly?
Dear Oscar,
Thank you very much for the helpful formulas!
You are doing great job helping us - Excel beginners :)
Ivo
How do I expand this formula (Vlookup and display multiple values vertically) to more than one lookup value. Essentially, lookup values in a2:a10 and display all matches for each of these values. Please help. Thanks!
I am looking to return multiple values (by formula) into one cell.
List of unique numbers, looking up against a another sheet that contains both unique and duplicate results to be grabbed and concatenated into a single cell on the original sheet.
I found this question on other great blog, but they were only able to provide an addin tools solution, and not a formula one.
https://www.ablebits.com/office-addins-blog/2017/02/22/vlookup-multiple-values-excel/
Currently I am expecting to have to use one of the array formulas already presented to bring the duplicate results into separate columns/rows, and then concatenate them back down to a single column of cells to paste as values afterwards. Spreadsheet is quite large, so don't want to use such a messy and non agile method. Prefer single formula that can auto fill the duplicate matching results into single cell column.
Thanks in advance for your Help.
How to get dedicated price from sheet2 for each customer
Sheet1
A1=Product code B1=Price F1=Customer # G1=10001
A2=book1 B2=??
A3=book2 B3=??
A4=book3 B4=??
Sheet2 (product list with prices)
A1=Product code B1=Price Low C1=Price Medium D1=Price Customer 10001
A2=book1 B2=1 C2=4 D2=7
A3=book2 B3=2 C3=5 D3=8
A4=book3 B4=3 C4=6 D4=9
I am using at the moment this, but i am not able to get more options with price lists.
= IFERROR(VLOOKUP(A2,'[EA DATA BASE.xlsx]PRODUCTSLIST'!A$2:AU$1500,2,FALSE), "")
perfect solution - thank you!
Very helpful!, thanks!. Can you provide an explanation for the non array formula?
Hi there I'd like to find
I have a list of sizes S,M,L and widths S=50 M =100 and L=200
I would like to test my width is greater than sizes and return options available for example
width of 30 would return S,M,L
width of 90 would return M,L
width of 190 would return M,L
width larger would return "message"
Hello,
Can you please help with below out using excel formula?
IT1 Apple
IT1 Orange
IT1 Banana
IT2 potato
IT2 grapes
Output as below and values to be wrapped in one cell
IT1 "Apple,Orange,Banana"
IT2 "potato,grapes"
Hello Oscar, first congratulations your website.
I had this error:
•IFERROR(INDEX(data!$C$4:$C$1783,SMALL(IF(Sheet1!$A 2-data!$B$4:$B$1783,ROW(data!$B$4:$B$1783)-MIN(ROW(data!$B$4:$B$1783)+1),""),COLUMN(data! A1))),"")
But thanks to the comments and reading the article well I was able to solve it, thank you very much.
I tried to use the array formula for returning multiple values vertically and continually got an error message. I though maybe it was my mistake so I copied your example spreadsheet exactly and entered the formula in D10 just as you had, but instead of the values "apple", "lemon" I got #NUM!
Is there some way I need to reformat excel to accept your formula? It currently doesn't work at all.
Thank you for your great site. thank you
Solenoid valves, like other valves, have the main effect of controlling and moderating the flow. The solenoid valve will have two alternating inlet and outlet valves. However, the special thing here is that it can use electric current to control the gas.
Hi Oscar,
Thank you for your help, but I tried it in my mac computer and it is not working.
Does it make any difference? and my microsoft excel is not 365.
thank you.
Sincerely,
Gracia
Thanks for the tutorial - would love to see more on non-array formulas
So, used this to create a spreadsheet for a statute lookup. I have it set up where column a is the first 3 numbers of the statute, column B is the complete statute, and C is the description of the statute. I have the search box searching column A and returning all matches under the first 3 numbers, but the issue I am having is that it is not returning the last statute in the group. Instead its grouping it with a totally different number group (which is the very next group). example: Search: 784 / Results: last line of 782 is returned and omits last line of 784. Search: 787 / Results: last line of 784 is returned and omits last line of 787, and so on.
I can't figure out why this is happening. I do have 2 separate sheets. 1 is the search page and the other is the master page.
Here is the formula that I am using: {=INDEX(MASTER!$B$2:$B$604,SMALL(IF($C$5=MASTER!$A$3:$A$604,MATCH(ROW(MASTER!$A$3:$A$604),ROW(MASTER!$A$3:$A$604))," "),ROWS($B$1:B10)))}
Can these results be used to populate a data validation drop downm list?
David,
yes they can.
Populate drop down list with unique distinct values sorted from A to Z