## 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 - Return multiple values [horizontally]
- VLOOKUP - Extract multiple records based on a condition
- Lookup and return multiple values [AutoFilter]
- Lookup and return multiple values [Advanced Filter]
- Lookup and return multiple values [Excel Defined Table]
- Return multiple values vertically or horizontally [UDF]
- How to count VLOOKUP results
- Lookup and return multiple values in one cell

I have made a formula, demonstrated in a separate article, that allows you to VLOOKUP and return multiple values across worksheets, there is also an 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.

### 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.

**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-click on a cell.
- Paste (Ctrl + v) array formula.
- Press and hold Ctrl + Shift simultaneously.
- Press Enter once.
- Release all keys.

#### Read more

- How to enter an array formula
- Convert array formula to a regular formula
- How to enter array formulas in merged cells

The array formula above filters only values with one condition, the following article explains how to filter based on multiple criteria: Vlookup with 2 or more lookup criteria and return multiple matches

If you don't like array formulas, try this regular but more complicated formula in cell D10:

### Explaining array formula (Return values vertically)

You can easily follow along as I explain the formula, select cell D10. Go to tab "Formulas" on the ribbon and click "Evaluate Formula" button.

Click "Evaluate" button shown above to move to next step.

#### Step 1 - Identify cells equal to the criterion

= (equal sign) is a comparison operator and checks if criterion (E3) is equal to values in array ($B$3:$B$7). This operator is **not **case sensitive.

$E$3=$B$3:$B$7

becomes

"**France**"={"Germany";"Italy";"**France**";"Italy";"**France**"}

and returns

{FALSE, FALSE, TRUE, FALSE, TRUE}

#### Step 2 - Create array containing corresponding row numbers

The ROW function returns the row number based on a cell reference, we are using a cell reference that points to a cell range containing multiple rows so the ROW function returns an array of row numbers.

MATCH(ROW($B$3:$B$7), ROW($B$3:$B$7))

The MATCH function finds the relative position of a value in a cell range or array, however, I am using multiple values so this step returns an array of numbers.

MATCH(ROW($B$3:$B$7), ROW($B$3:$B$7))

becomes

MATCH({3, 4, 5, 6, 7}, {3, 4, 5, 6, 7})

and returns {1,2,3,4,5}

#### Step 3 - Filter row numbers equal to a condition

The IF function has three arguments, the first one must be a logical expression. If the expression evaluates to TRUE then one thing happens (argument 2) and if FALSE another thing happens (argument 3).

IF(($B$10=$B$3:$B$7), MATCH(ROW($B$3:$B$7), ROW($B$3:$B$7)), "")

becomes

IF(FALSE, FALSE, TRUE, FALSE, TRUE}, MATCH(ROW($B$3:$B$7), ROW($B$3:$B$7)), "")

becomes

IF(FALSE, FALSE, TRUE, FALSE, TRUE},{1, 2, 3, 4, 5}, "")

and returns {"", "", 3, "", 5}

#### Step 4 - Return the 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.

### How to remove #num errors

The picture above shows you the array formula copied down to cell D12 however there are only two values shown, the remaining cells show nothing not even an error.

Array formula in cell D10:

The IFERROR function lets you convert error values to blank cells or really in whatever value you want. In this case it returns blank cells.

#### Recommended articles

- How to use the IFERROR function
- How to use the ISERROR function
- How to use the ERROR.TYPE function
- How to find errors in a worksheet
- Delete blanks and errors in a list

### Count matching values

The following image shows you a data set in column B and C. The lookup value in cell E3 is used for identifying matching cell values in column B.

**Formula in cell G3:**

**Alternative formula in cell G3:**

#### Recommended articles

- Count a given pattern in a cell value
- Count cells containing text from list
- Count cells between specified values
- Count entries based on date and time
- Count unique distinct values
- Count unique distinct records

### Return multiple values horizontally

This array formula is entered in cell C9. Then copy cell C9 and paste to the right.

**Array formula in C9:**

Enter the formula as an array formula or use this regular but more complicated formula:

#### Recommended articles

- Search values distributed horizontally and return corresponding values
- Resize a range of values
- Rearrange values
- Rearrange cells in a cell range to vertically distributed values
- Rearrange values based on category(VBA)
- Normalize data (VBA)
- Normalize data, part 2

### Extract multiple records based on a condition

The formula in cell A10 extracts records based on the value in cell B9.

Array formula in cell A10:

To enter an array formula, type the formula in a cell then press and hold CTRL + SHIFT simultaneously, now press Enter once. Release all keys.

The formula bar now shows the formula with a beginning and ending curly bracket telling you that you entered the formula successfully. Don't enter the curly brackets yourself.

Copy cell A10 and paste to cell range B10:C10. Then copy A10:C10 and paste to cell range A11:C12.

#### Watch a video where I explain how to use the array formula and how it works

Enter the formula above as an array formula or use this regular but more complicated formula:

#### Recommended reading

- Extract all rows from a range that meet criteria in one column
- Match two criteria and return multiple records
- Extract records where all criteria match
- Search for a text string in a data set using an array formula
- Filter unique distinct records

### Lookup and return multiple values [AutoFilter]

The AutoFilter is a 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.

#### Instructions on how to filter a data set [AutoFilter]

- Right-click on a cell value that you want to filter
- Click on "Filter" and then "Filter by Selected Cell's Value"

- That's it!

#### How to remove a filter

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

- Click on "Clear Filter From "Country""

- The AutoFilter buttons next to each header are still there.

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

- The data set now looks like this:

### Lookup and return multiple values [Advanced Filter]

The Advanced Filter is a tool in Excel that allows you to filter a dataset using complicated criteria combinations like AND - OR logic that the regular AutoFilter tool can't accomplish.

In this case I am only going to filter based on a single condition so this will be an easy introduction to the Advanced Filter in Excel.

- Copy the dataset headers and place them above or below your dataset, this to avoid confusion if the conditions disappear when a filter is applied.

Rows will be hidden and if a condition is on the same row it will be hidden as well. I created headers on row 2, see image above. - Enter the condition below the correct header you want to apply a filter to, I entered my condition in cell B3.
- Select cell range B5:C10.
- Go to tab "Data" on the ribbon.
- Click "Advanced" button.
- Click in Criteria range: field and select cell range B2:C3

- Click OK button.

The image above shows the dataset filtered based on the condition used in cell B3. To clear the filter simply go to tab "Data" on the ribbon and click "Clear" button.

### Lookup and return multiple values [Excel Defined Table]

The image above shows you a dataset converted to an Excel Defined Table and filtered based on item "France" in column B.

- Select a cell in your data set.
- Press CTRL + T (shortcut for creating an Excel Defined Table).
- A dialog box appears, click the checkbox if your data set contains headers.

- Click OK button.

To filter the table follow these simple steps:

- Click the black arrow next to a header name.

- Make sure the checkbox next to the value you want to use as a condition is selected.
- Click OK button.

So why use an Excel defined Table? An Excel defined Table contains many more useful features.

- Enter a formula in one cell and Excel automatically enters the formula in the remaining Excel Table cells on the same column.
- Cell references are converted to structured references, for example a cell reference to column "Country" might look like this: Table[Country].

This is beneficial because you don't need to adjust cell references if your table grows or shrinks, the cell reference is the same no matter what. You don't need to use dynamic named ranges either. - Easy to filter and sort data.
- Easy to add or delete data, simply type your data below the last table row and the Excel defined Table will automatically expand.
- Use as data source for a chart and the chart will display what is filtered.

### Return multiple values vertically or horizontally [UDF]

Make sure you have copied the vba code below into a standard module before entering the array formula.

#### User defined Function Syntax

**vbaVlookup(***lookup_value, table_array, col_index_num*, [*h*]**)**

#### Arguments

lookup_value |
Required. |

table_array |
Required. A cell reference to the data table you want to search. |

col_index_num |
Required. A number representing the column in the table_array. |

[h] |
Optional. Return values horizontally. |

Array formula in cell C14:D14:

#### Watch a video that explains how to use the User Defined Function

**How to enter custom function array formula**

- Select cell range C9:C11
- Type above custom function
- Press and hold Ctrl + Shift
- Press Enter once
- Release all keys

Array formulas allows you to do advanced calculations not possible with regular formulas.

**How to enter custom function array formula**

- Select cell range C14:D14
- Type above custom function
- Press and hold Ctrl + Shift
- Press Enter once
- Release all keys

**How to copy array formula to the next row**

- Select cell range C14:D14
- Copy cell range
- Select cell range C15:D15
- Paste

**Vba code**

- Copy vba code below.
- Press Alt + F11 to open the visual basic editor.
- Right-click on your workbook in the project explorer.
- Click on "Insert".
- Click on "Module".

- Paste code to code module.
- Exit vb editor and return to Microsoft Excel

'Name User Defined Function and arguments Function vbaVlookup(lookup_value As Range, tbl As Range, col_index_num As Integer, Optional layout As String = "v") 'Declare variables and data types Dim r As Single, Lrow, Lcol As Single, temp() As Variant 'Redimension array variable temp ReDim temp(0) 'Iterate through cells in cell range For r = 1 To tbl.Rows.Count 'Check if lookup_value is equal to cell value If lookup_value = tbl.Cells(r, 1) Then 'Save cell value to array variable temp temp(UBound(temp)) = tbl.Cells(r, col_index_num) 'Add anoher container to array variable temp ReDim Preserve temp(UBound(temp) + 1) End If Next r 'Check if variable layout equals h If layout = "h" Then 'Save the number of columns the user has entered this User Defined Function in. Lcol = Range(Application.Caller.Address).Columns.Count 'Iterate through each container in array variable temp that won't be populated For r = UBound(temp) To Lcol 'Save a blank to array container temp(UBound(temp)) = "" 'Increase the size of array variable temp with 1 ReDim Preserve temp(UBound(temp) + 1) Next r 'Decrease the size of array variable temp with 1 ReDim Preserve temp(UBound(temp) - 1) 'Return values to worksheet vbaVlookup = temp 'These lines will be executed if variable layout is not equal to h Else 'Save the number of rows the user has entered this User Defined Function in Lrow = Range(Application.Caller.Address).Rows.Count 'Iterate through empty cells and save nothing to them in order to avoid an error being displayed For r = UBound(temp) To Lrow temp(UBound(temp)) = "" ReDim Preserve temp(UBound(temp) + 1) Next r 'Decrease the size of array variable temp with 1 ReDim Preserve temp(UBound(temp) - 1) 'Return temp variable to worksheet with values rearranged vertically vbaVlookup = Application.Transpose(temp) End If End Function

#### Recommended reading

- Lookup multiple values in one cell [UDF]
- Fuzzy lookups [UDF]
- Filter an Excel defined Table based on selected cell [VBA]
- Filter words containing a given string in a cell range [UDF]
- Filter an Excel defined Table programmatically [VBA]
- How to save custom functions and macros to an Add-In
- Add your personal Excel Macros to the ribbon

Vlookup with 2 or more lookup criteria and return multiple matches

VLOOKUP and return multiple matches based on many criteria.

Vlookup across multiple sheets

This article demonstrates an array formula that searches two tables on two different sheets and returns multiple results. Sheet1 contains […]

VLOOKUP and return multiple values across columns

This article demonstrates a formula that lets you extract non-empty values across columns based on a condition. The image above […]

Use a drop down list to search and return multiple values

I will in this article demonstrate how to use a value from a drop-down list and use it to do […]

Search values distributed horizontally and return corresponding value

Question: Hi, The formula here works great but I can't figure out how to change it to work with data […]

Vlookup with multiple matches returns a different value

Linda asks in this post: How to return multiple values using vlookup in excel I tried using the formula above […]

Lookup multiple values in different columns and return multiple values

Jason C asks: I have a set of data, like the one you used in the original example that also […]

Vlookup a cell range and return multiple values

VLOOKUP a multi-column range and return multiple values.

Use VLOOKUP and return multiple values sorted from A to Z

The array formula in column G filters values in column C using a condition in cell E3, comparing it with […]

### 474 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.

**Contact Oscar**

You can contact me through this contact form

Ok - I absolutely MUST comment on this! I've spent my entire day looking all over the web for help on doing a VLOOKUP to look up one value and return multiple corresponding values and have not found anything that has helped me as much as you have! =D You've made my day. Thank you for posting this!

~kenbra

I am happy you found this post, but my advice is to take a look at this post instead: Using array formula to look up multiple values in a list. It has an array formula not as complicated as this one.

Thank you for your comment!

I am trying to use your code for a calendar. the file template that you have shown I have downloaded. In place of the values in Column B, I would like to put dates. In Column C, for the $ values I would replace with addresses

When I try modifying the table and expanding it past three columns it tells me I am out of the array range.

Can you assist?

Thanks!

Jim,

Which formula and template?

Oscar,

I downloaded the file lookup-vba3.

I think I can use this to help me populate a calendar.

I substituted dates for Pen, Paper, and Eraser. I then had locations substituted for $ values.

Where I have a date of say, 11/27/12, I have 10 locations delivering that day. Using the template as shown in the screen shot under "Retun multiple values horizontally or vertically (vba)" I cannot expand past column "C" to return multiple values.

I think it is in the array code but I cannot figure out how to return values past column C.

If you can help, greatly appreciated!

Thanks,

Jim

Jim,

The function procedure can not return values from multiple columns.

This array formula returns values from a range, in cell B10:

I wish I could return all values to single a column.

Jim,

Now I know how to return all values to a single column.

Read this post:

Lookup and return multiple values from a range excluding blanks

Hi. I used this formula and it works great. However I like to know how the formulas I use work. I have spent a lot of time on the internet trying to break it down but this one has me stumped. I understand part, like the VLOOKUP and INDEX but I don't know how the rest fits in. Are you able to break this down for the dummies? If you have time it would be greatly appreciated.

Hi,

Continuing your example, is there a way to "Eraser" and "Paper clip"? and keep goign down? It returns #NUM because Row is set to 3:3 after the two Pen entries. Is there a way to reset the row to ROW(1:1) after a new vlookup search string?

Thanks

Excel user, 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/2009/12/29/vlookup-with-2-or-more-lookup-criteria-and-return-multiple-matches-in-excel/

Very helpful posts. I tried using the formula above but it didn't work for me and I can't figure out how to adjust it to accomodate my needs. Here is what I have: Data Range is in $E$1:$F$8

Data Range Col. A Col B

Red 2

Green 6

Pink 3

Blue 9

Red 7

Yellow 11

Blue 4

Red 14

Thank you for your very helpful posts. I tried using the formula above but it didn't work for me and I can't figure out how to adjust it to accomodate my needs. Here is what I have: Data Range is in $E$1:$F$8, I would like my results in Col. B. Lookup value in column A and return the value in Col F that matches. Since there are duplicates in Col. A I want Col. B to return the next matching value from col. F. Essentially this is a Vlookup with multiple matches that would return a different value. Thanks for any help you can provide.

Data Range Col. A Col B

Red 2 Red

Green 6 Red

Pink 3 Red

Blue 9 Yellow

Red 7 Blue

Yellow 11 Blue

Blue 4

Red 14

Linda,

read this post: Vlookup with multiple matches returns a different value in excel

looking for a formula that will take a part number from one column and go and look for all related vehicle applications per that part number and return the vehicle applications to a single cell related back to the part number

HI,

Thanks for the same.

I Tried a lot but i didnt get. i want to know how to use the same.

Please requesting you kindly help me on this.

Regards,

Chandra

Chandra,

Did you download the excel example file?

Hi there, I think I understand the logic behind these formula but when I try to amend it it doesn't work. I downloaded the example file.

This formula below works

INDEX(tbl,SMALL(IF($E$8=$B$2:$B$6,ROW($B$2:$B$6)-MIN(ROW($B$2:$B$6))+1,""),ROW(A1)),2)

Originally tbl is referencing B2:C6

I change it so that it is referencing B2:D6

Pen $1.50 Red

Eraser $2.00 Blue

Paper $1.00 Green

Pen $1.70 Yellow

Paper clip $3.00 Black

and then update the formula to

INDEX(tbl,SMALL(IF($E$8=$B$2:$B$6,ROW($B$2:$B$6)-MIN(ROW($B$2:$B$6))+1,""),ROW(A1)),3)

I would expect 'Red' and 'Yellow' to be returned instead of 1.50 and 1.70 but instead the formula will not calculate.

Any suggestions?

Ignore last. Realised it was because I hadn't pressed CTRL + SHIFT + ENTER!

Thanks very much for your post. It has really helped me!

Ignore last. Realised I hadn't hit CTRL + SHIFT + ENTER!

Thanks for your post. It really helped me.

Richard,

Read this post: Excel udf: Lookup and return multiple values concatenated into one cell

This is just awesome. Thank you. Had to do a big tweek to do a less than if statement. But wow this worked great. Thank you

Joe,

Thanks for your comment!

For the life of me I cannot get this to work on my spreadsheet. So frustrating.

Jim,

Post your formula here and I´ll see what I can do.

Thanks very much for this help. The information is very well presented and explanied.

I am attemting to do what is described above however rather than actually listing the mutiple values in different cells I just want to add them all together and find the total in one cell. Do you know if this is possible or is there an easy method.

Thanks

Henry

Henry Nichols,

SUMIF(range, critera, [sum_range])

Adds the cells specified by a given condition or criteria.

HI,

Nice one, just a little mistake

Here is the correct one

=INDEX($C$2:$C$6, SMALL(IF($B$8=$B$2:$B$6, ROW($B$2:$B$6)-MIN(ROW($B$2:$B$6))+1, ""), ROW($A$1:$A$6)))

At the end it is not Row(A1), but has to be a vector for an increasing increment.

cybou,

It is not a mistake, ROW(A1) is a relative cell reference.

Download the example file and check it out.

I like this - but would like the #num not to be displayed if the value is not found. Sometimes I have 3 values and at other times may be 5

Elwil,

IFERROR() function filters errors.

Just wanted to say thanks, what an awesome bit of excel-ing

You are most welcome! Thanks for commenting!

Oscar,

Thanks-- I'm using Excel 2003 and used the VLookup array successfully. However I'm been unsuccessful adding the IFERROR() function to clean up the sheet. Could you provide further explanation on using IFERROR for Excel 2003.

IFERROR is a function in excel 2007 and later versions.

Excel 2007

IFERROR(value, value_if_error)

Excel 2003 and earlier versions:

IF(ISERROR(formula), value_if_error, formula)

Hi,

First off thanks for the help ur site is great.

My question is in addition to the formula mentioned is there any way I can get the formula to return unique values only?

For the example above, if Pen had 4 prices ($1.5,$1.7,$1.5 and $1.7) is there a way to get only one 1.5 and 1.7?

I hope my question is clear...

Thanks!

Gi99a,

In my blog post example above, array formula in cell C8:

=INDEX(tbl, SMALL(IF(($B$8=$B$2:$B$6)*(COUNTIF($C$7:C7, $C$2:$C$6)=0), ROW($B$2:$B$6)-MIN(ROW($B$2:$B$6))+1, ""), ROW(A1)), 2) + CTRL + SHIFT + ENTER.

Hey,

Thanks for ur reply.

However this still gives me all values of "pen" so cell C8 onwards would give me repeated values for pen if there are any in "tbl"...

Gi99a

Download example file

When I change the data of the above example as below, it cannot output correctly. Could you solve it?

-------------------------

Data:

Pen $1.50

Pen $1.50

Pen $5.00

Pen $18.00

Paper clip $3.00

Output:

Pen $1.50

$18.00

#NUM!

-------------------------

AY,

You are right! I uploaded a new file. Thanks for commenting!

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.

ROW(A1)))How to create an array formulaCopy array formula (Ctrl + c)

Double click on a cell

Paste (Ctrl + v) array formula.

Press and hold Ctrl + Shift simultaneously.

Press Enter once.

Release all keys.

How to copy formula in cell D2Select 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:

+ CTRL + SHIFT + ENTERCopy 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 formulaCopy (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:

A1)),2),"0")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 formulaSelect 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,

Download excel fileVlookup - return multiple records.xlsx

(Excel 2007 Workbook *.xlsx)

1, You can´t change "" to 0, then the formula returns the first record in your pricelist.

2, I am guessing here.. Maybe the value in 'Price Sheet'!$B$4 contains an additional space somewhere? Compare the values

If I leave the "" in the formula I only returns a #VALUE!. I double checked and there is no additional spaces in the cell. Still not quite sure what the problem is.

Hi

This guide has gone directly to the top of my favorites. I've been loogin for this for so long!

But I still have a question.

I use this formula to put in all overdue invoices for my customers in a payment reminder letter.

So sometimes it is 1 invoice and sometimes it is 20 invoices or more.

Below the rows, where I am using your formula, I need to put in some plain text, but right now I has te be ind the very bottom of the page, and it doesn't look good when only one line is returned from the formula. Though it does look good when it is several rows.

Have you got any tips on how to make the area with formula flexible in how many rows it "occupies".

Hi Oscar....

THANK YOU SO MUCH.....

Hi,

is it possible to have the results in the same cell using a variation of your formula?..

I mean like:

John | x22 | John,Mark

John | x23 | John

John | x24 | John, George

Mark | x22 | John, Mark

George|x24 | John, George

The results separated by the comma in a single cell?...

And thanks a lot for your usefull code...

mcholst,It is hard for me to give you advice without having seen your sheet.

anna,you are welcome!

Ivan,I believe this post describes what you are asking for: Excel udf: Lookup and return multiple values concatenated into one cell

VBA code:

Hello,

thank you for your fast reply...

I'm not very good in excel and udf, anyway i followed your adivice, took your code and recreate a module.

The strange thing is that it gives me a blank cell (I took off the name of the sheet cause i want the results in the same one where the data are)..

Did i miss something?

Ivan,

Download excel fileLookup 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 clicked on and redirected., i.e. The worksheet "Customer Sales" is a pivot table based on the "Pivot Data" worksheet and the column title for the hyperlink data is "Notes".

I know I'm asking a bit much, but I'm sure there is a solution and at the moment I'm struggling to find one.

Regards

Séan

Hi Oscar,

First time here so please bear with me. I've searched just about everywhere for a formula that can help me. I'm using the

=INDEX($A$2:$C$7, SMALL(IF($B$9=$A$2:$A$7, ROW($A$2:$A$7)-MIN(ROW($A$2:$A$7))+1, ""), ROW(A1)),COLUMN(A1))

formula to return multiple records. I have tried to modify it but to no avail. Where your formula matches the cell in $B$9 exactly, I would want to simply put part of the value in $B$9 and let the formula return all that match it e.g. you use 123 and it shows you all the 123 and adjacent values but I would want to enter e.g. only 12 and would want it to return all values that contain 12.

The above is basically to be used to look for product codes. I have an array of 4 columns with product codes in 1st column and need e.g. all products starting with "CP-" and also displaying all adjacent values that come with the particular product code. Not sure if I'm being clear enough.

example:

product code...column2...column3...column4

MB-DKJ3475.....1122......N.........product description

CH-UYI2938.....125.......N.........product description

CP-DLK4378.....4258......C.........product description

CC-DDD3429.....26553.....C.........product description

CP-LKL4344.....33........C.........product description

I would want all the product lines starting with "CP-":

CP-DLK4378.....4258......C.........product description

CP-LKL4344.....33........C.........product description

Greatfull for any help that you might be able to provide.

Many thanks in advance

Regards

Coenraad

Sean,

Is there a particular book on excel that would be an ideal guide on some of the questions you are asked, or is it a case of trial and error ?As far as I know, there is not much written about array formulas.

I got interested in array formulas when I discovered Dennis Wallentin website: 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

When you refresh the data, so that each of the pivot tables are updated with the new data,Sure, use tables. They are dynamic. You need vba to refresh pivot tables automatically: Auto refresh a pivot table in excel

is it then possible to make sure that the data of the hyperlink is shown as a valid hyperlink which can be clicked on and redirected.,You need a macro to accomplish this.

i.e. The worksheet "Customer Sales" is a pivot table based on the "Pivot Data" worksheet and the column title for the hyperlink data is "Notes".Coenraad,

I believe you are looking for this post:

Lookup with multiple criteria and display multiple search results using excel formula

Array formula in cell A10Cell $A$8 contains the search value.

How to create an array formulaCopy (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 formulaSelect 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,

A1)),COLUMN(Master!A1))Excel 2007, remove #num errors:Oscar,

Thanks so much. Worked like a charm.

Hi, Thanks for valuable home page,

I want formula to find 2 valuse for the same ID, if I have long list with over 1000 ID's and want to find Anna and the status if she is active or not. How I should write my formula.

Thanks and have a great day.

Oscar,

Did you receive the workbook I emailed you ? If so, any joy with finding a solution to my problem ?

Sean,

Basically, when I do a refresh of the data in the "pivotdata" worksheet, I need it to recognise the cell is a hyperlink. In addition, when I update the indivital pivot tables, it looks at the data in the "Notes" field and recognise it as a hyperlink.I am still confused. You want the data in column "Notes" being recognized as a hyperlink in a pivot table? So you can click an hyperlink a pivot table and it automatically opens a webpage in a webbrowser?

Correct on both counts. Does this sound feasible ?

Your help is very much appreciated.

Regards

Sean

Sean,

USING HYPERLINKSUnlike 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:

$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 first formula must start with cell reference A1 (bolded), example:

A1)))Did you create an array formula?

Anas Hashmi,

Yes, you are right. Array formulas are too slow with large data sets.

Ok,ok...The Control, Shift, Enter thing I completely missed. After editing the formula you must re-enter formula via this key combination. As expected the formula is genius... 4 days later!

So thank you and can you now point me in the right direction. I have a column of cities (multiple), each city row has a color, red, green, blue, etc.. associated with it. The row and column formula works, but grows my data base in rows. I have my colors in columns and want to populate the color column as it applies to the city on a single row basis. The column formula works well, but I can't be assured that all colors will show up in their respective columns with in the city row.

Thus red green, blue, its possible that if green is not in the data, blue data falls into the green column.

Suggestions?

Any advise how to sort on city, and populate columns that apply to specific colors?

RE,

I am not sure I understand.

Download excel filere-city-color.xlsx

From my comma delimited file dump:

Office# City Color Code ID

OF175 Ames Blue 672

OF175 Ames Red 8732

OF25 Oakmont Green 753

OF25 Oakmont Blue 8743

OF5 Omaha Red 634

OF95 Dallas Red 231

OF95 Dallas Blue 13244

OF95 Dallas Orange 13132

OF95 Dallas Pink 234

My Spreadsheet desired result

Office# City State Red Green Blue Orange

OF175 Ames Ia 8732 672

OF25 Oakmont Mn 753 8743

OF5 Omaha Ne 634

OF95 Dallas Tx 231 13244 13132

I'm trying to get from comma delimited rows to a consolidated row where the color code lines up and populates the cell with the ID.

Thank you for your time

Does this help?

Sorry it lost the formatting when I sent it,

CSV: Office# | City | Color |ID |

Excel: Office# | City | State | Red | Green | Blue | Orange

Oscar,

I guess it can't be done? Eh?

Re,

I am sorry, I somehow missed your comment.

Check out this file: re-city-color1.xlsx

Hi, Oscar...

I have been looking for help on "How to return multiple values using vlookup in excel" for one whole day... then I found this... Man!, you help me so much. Thanks for sharing your knowledge. God Bless

Erwin,

Thanks!

Hello,

Thanks for the helpful formula. I used this formula successfully, but now I wanted to get the data I missed with the formula. How would you add multiple IF/AND statements to the formula. For example add:

(IF($B$8<$B$2:$B$6,...),($C$8<$C$2:$C$6,...), etc.

Thanks!

Kevin,

I used this formula successfully, but now I wanted to get the data I missed with the formula.What are you trying to do?

How would you add multiple IF/AND statements to the formula. For example add:(IF($B$8<$B$2:$B$6,...),($C$8<$C$2:$C$6,...), etc.

Oscar, great post. One question: I am using the vba code you provided and it works great. I am dealing with a set of 110,000+ rows of data (2 columns) with about 17,500 unique lookup values and the remainder of the rows are unique values to be found. I need to use the vba that you posted but to have it output the data horizontally, not a vertical array. I am terrible with VBA. Any chance you could give me some pointers or tell me which part of the code to change. Any help would be much appreciated.

Ex:

35007000030002 354CSTRL

35007000030002 354CSTRU

35007000030002 402MRRW

35007000030003 404CHRK

35007000040000 402MRRW

Needs to become:

35007000030002 354CSTRL 354CSTRU 402MRRW

35007000030003 404CHRK

35007000040000 402MRRW

When looked up.

Sorry, my question was vague. Here is the setup of my worksheet:

I have 3 columns filled with percentages associated with W,X,Y,Z.

% red %pink % blue

10% 15% 30% W

25% 33% 80% X

40% 12% 66% Y

75% 4% 12% Z

I used your formula to find W,X,Y,Z meeting the below criteria:

>30% red >20% pink >50% blue

Y X X

Z Y

Now, I want to find one formula that will find the values not captured by the previous formulas. In other words, (% red <30%)(% pink <20%)( % blue <50%) = W

Thanks!

Jonathan ,

I have changed the existing udf on this webpage. It is now possible to return values horizontally.

Kevin,

Download file:Vlookup-kevin.xls

Oscar, great job! keeping the thread alive for so long! This is a great one!

I have a question around merged cells. Lets say in your first example, Both Pen and Paper cost $5, so I have merged C2 and C3 into a single cell with the $5.

Now, in the search cell (B8), if I put in Pen, it will give me the correct asnwer. However, if I put in Paper it will not return the value, as opposed to the expected returned value of $5.

The question: is there a way to make this work with merged cells

Greatly appreciate you help.

Hass

Hass,

There is question I don´t know the answer to!

I would avoid merged cells as much as possible.

Hi Oscar,

The VLOOKUP "Return multiple values horizontally" is exactly what I need, however it is not working for me. I copied everything exactly as shown in your example, but all I get is "#VALUE!". I am using Excel 2010 on a PC. Has something changed in the 2010 version, or am I just missing something?

would it be possible for the formula to copy itself into other cells below depending on how amny unique references were found in the table?

Kieran,

That would require vba.

Janet,

Maybe you forgot to create an array formula? Instructions are in the post above.

Hi Oscar,

The array formula I used was the same as in your "Return Multiple Values Horizontally" example: "=INDEX($C$2:$C$6, SMALL(IF($B$9=$B$2:$B$6, ROW($B$2:$B$6)-MIN(ROW($B$2:$B$6))+1, ""), COLUMN(A1)))". I set up the table exactly as you show in your example as well, but it isn't working for me. All I get in cell C9 is #VALUE! Any ideas on why it isn't working?

Thanks.

Janet,

Download excel example file:

Return-multiple-values-horizontally.xls

I am trying to use the formula for a bigger range, C3:C40. I modified the formula as follows -

=INDEX('Resource Project Mapping'!C3:C40,SMALL(IF($B$4='Resource Project Mapping'!B3:B40,ROW('Resource Project Mapping'!B3:B40)-MIN(ROW('Resource Project Mapping'!B3:B40))+1,""),ROW('Resource Project Mapping'!A1)))

however I am getting the below results

#VALUE!

#NUM!

#NUM!

#NUM!

#NUM!

Can you please help?

DP,

You need to use absolute cell references (except the last cell reference):

Dear,

I want to copy the formula from first cell to its adjacent cells, but I want to keep the same value of the last cell to be multiplied with the above percentage, but when I paste the formula into adjacent cell it copies the value of the cell next to the one I need the value from

please advise

Hi Oscar,

I'm trying to use your vbaVlookup tool, and am having issues where by lookup columns are backwards from yours (switch the price and pen columns around), and I want to find all of the matches from the first column, not the second one. When I use the formula, it just returns a blank. It works fine if I switch the columns, but this is not ideal.

Here's an example:

A B

AAA GrpA

BBB GrpA

CCC GrpB

DDD GrpA

EEE GrpC

I then create another column elsewhere, with "GrpA" as a header, then in the second row of that column, my function looks like this:

{=vbaVlookup(C1,$A$2:$B$15,1)}

I figured the last parameter (1) was to look at the first column instead of the second.

If you could provide some help to get this working, that would be superb. Thanks!

Feras,

Can you describe in greater detail and some example values?

Ben,

download file:

Ben.xls

Oscar,

Is it possible to return the values in the next tab or sheet? instead of the same sheet?

Oscar, Pls ignore above.

I got how to do :)

Hi Oscar,

Thanks for this article... the formula has been a great help to me in setting up some of my worksheets. I have a question about it though:

=INDEX($A$2:$C$7, SMALL(IF($B$9=$A$2:$A$7, ROW($A$2:$A$7)-MIN(ROW($A$2:$A$7))+1, ""), ROW(A1))

Is it necessary to use the "MIN(ROW(array))+1" calculation? I was looking at a similar formula posted by Ashish Mathur and I noticed he didn't use the "MIN...".

Thanks

Timus,

You can also use MATCH(ROW($A$2:$A$7), ROW($A$2:$A$7)).

becomes

In fact, you can use: ROW($1:$7)

and the formula becomes:

Remember, if you then replace your cell references with named ranges, the last formula won´t work. It is not dynamic contrary to the other two formulas.

Thanks for commenting!

Hi Oscar, Thank you for your posting. My question is fairly basic. I recreated your table of "Return multiple values vertically" above and entered the exact formula. However, it doesn't work.

I entered in the C8 cell --> =INDEX($C$2:$C$6, SMALL(IF($B$8=$B$2:$B$6,ROW($B$2:$B$6)-MIN(ROW($B$2:$B$6))+1),ROW(A1)))

And excel gave me -- > #VALUE!

If I copied your formula directly from the formula bar to my sheet in C8 cell, excel would return #VALUE! as well. However, if I copied an entire cell, and paste into my C8, excel would return exact same value as your original table. I am using Excel Mac 2008 but I don't believe this would have anything to do with it. Please let me know. I really appreciate your time. Thank you very much.

Just want to tell you: THANK YOU! Your web is great, your are very generous. Thank you for sharing!

Dawisee,

You forgot to create an array formula.

How to create an array formula

1. Double click cell C8.

2. Paste formula

3. Press and hold Ctrl + Shift

4. Press Enter

Gab,

Thank you for commenting!

My formula no working, I follow your steps but there´s somethink wrong..., please give me a hand!

=INDEX($B$1:$C$700;SMALL(IF(($D$90=$B$1:$B$700);ROW($B$1:$B$700)-MIN(ROW($B$1:$B$700))+1;"");ROW(A1));2)

VIC,

I think you forgot to create an array formula.

=LEFT(INDEX($C$2:$C$13546,SMALL(IF($E$3=$B$2:$B$1300,ROW($B$2:$B$1300)-MIN(ROW($B$2:$B$1300))+1,""),COLUMN(A1))),11)

Hi Oscar,

Your code works perfect but hen I reach $B$1300 or more the result will be #VALUE! Please help!

thanks a lot,

jener

Jener,

adjust cell references (bolded):

$B$2:$B$1300,ROW($B$2:$B$1300)-MIN(ROW($B$2:$B$1300))+1,""),COLUMN(A1))),11)Hi Oscar,

thanks for the reply,

I have no clue how to adjust it.

Can you give me a sample. lets say in Column B and C there are 100000 rows

Is this the right code for it:

=LEFT(INDEX($C$2:$C$100000,SMALL(IF($E$3=$B$2:$B$100000,ROW($B$2:$B$100000)-MIN(ROW($B$2:$B$100000))+1,""),COLUMN(A1))),11)

need your expert advice,

jener

Jener,

I thought you copied the array formula horizontally? You are using column(A1) in your formula.

Your example seems to be right.

Hi Oscar,

Im using the "Return multiple values vertically or horizontally (vba)". Right know I can get it to return 3 values in the the cells C9-C11. I have a lot of data to lookup and would like it to return more values vertically (maybe 10 values). When I try to copy the array to the next cells, I either get a error message og it will start returning the same values, is already shown in C9-C11. Can you help me? (Hope the question makes sense)

Ulrik

Hi Oscar,

First of all, thank you VERY much for the superb effort you put in for us Excel newbies to be better!

I am facing one problem in making this array formula run. My data table and output table are on different sheets. While the formula works perfectly when they are both in the same sheet, error is returned when they are on different sheets. Here's my formula that works:

=IFERROR(INDEX($A$2:$E$107, SMALL(IF(Dashboard!$C$6=$A$2:$E$107, ROW($A$2:$A$107)-MIN(ROW($A$2:$A$107))+1, ""), ROW(A1)),COLUMN(A1)),"No More Data Available")

Here's the one that does NOT work:

=IFERROR(INDEX('Business Events'!$B$2:$E$3177, SMALL(IF($C$6='Business Events'!$B$2:$E$3177, ROW('Business Events'!$B$2:$B$3177)-MIN(ROW('Business Events'!$B$2:$B$3177))+1, ""), ROW('Business Events'!B1)),COLUMN('Business Events'!B1)),"No More Data Available")

Data table is in sheet named "Business Events" and output table is in another sheet named "Dashboard". The filtering id is in cell C6 in the sheet "Dashboard".

Please help.

Thanks much!

Soumit

Soumit,

Does this formula work?

Ulrik,

I think the problem is how you enter the udf.

You can´t copy an array formula returning two or more values.

How to expand the array formula from cell range C9:C11 to C9:C21

1. Select C9:C21

2. Click in formula bar

3. Press and hold Ctrl + Shift

4. Press Enter

5. Release all keys.

Thanks for bringning this to my attention.

Hi Oscar,

Thanks much for your guide, it is helping out tremendously. I am wanting to alter your code a bit, and it seems like it should work but I am not getting the desired results. I would like to have an if or to check for another value. This is how I assumed it would look, but again, I'm not seeing the desired results. Assume C9 had 124.

=INDEX($A$2:$C$7, SMALL(IF(OR($B$9=$A$2:$A$7, $C$9=$A$2:$A$7), ROW($A$2:$A$7)-MIN(ROW($A$2:$A$7))+1, ""), ROW(A1)),COLUMN(A1))

I would like results for 123 and 124. I however seem to be getting every thing back as a result. What is going wrong? Thanks much for your help!

Mat,

($B$9=$A$2:$A$7)+($C$9=$A$2:$A$7) returns an array

OR($B$9=$A$2:$A$7,$C$9=$A$2:$A$7) returns a single value.

Beautiful! Thank you very much!

Thank you Oscar, worked perfectly.

Oscar, my apologies! The formula works exactly as (not) advertised, I screwed up on the accuracy of the id value. Basically the id matched no value on the data table, as a result the obvious error was displayed. Next time, I will make sure I am more diligent at my end before posting.

Anyway, thanks to you, I learned a great way to "query" an excel "Database" :)

Best regards-

Soumit

Hello,

I used the formula to get multiple values, and works great. My look-up values are available in the "Data" sheet. The result of look-up is in the "Summary" sheet. There is a percentage calculation in 'summary' sheet which is simple calculation like =g5/g7, and I want to sort values in descending order based on percentages. When I sorted results in the "summary" sheet, the sort was not applied. I guess it is because of "Index" formula. Frankly, I am lost as to how to apply sort. Could you please advise what should I do? Thanks for your help.

Best regards,

Prashant

Oscar,

thank you very much! It helped a lot to solve my problems!

Hi,

I am using your formula which works a treat and is very helpful. I wanted to ask could you show me how to move the lookup cell automatically down. I have unlocked it and copied it down but then when i copy the formula across the lookup cell moves and i have to individually have to update. Is there a way that I am able to change the formula so it will lookup the cell? I hope i have been clear. Thanks

Ak,

If the lookup cell is $B$1, change it to $B1.

More on absolute and relative cell referencing

Thanks for the quick response.

I had a look at the link but not sure what to implement. What i was trying to achieve is to use your formula to lookup multiple values and return them horizontally which is what your formula does.

My problem is that when i drag the formula down then it is locked to $B$1. If i change it then it works once dragging your formula down but when i drag across to see the other values the cell B1 is no longer the lookup, its cell B2. I have hundreds of lines so I didn't want to manually change and thought if you knew a way that can automate this in your formula?

Thanks

Ak

Ak,

Yes, cell reference B1 changes when you copy the formula.

Cell reference $B1 changes only the row number, it is locked to column B. When you drag the formula across it is still locked to column B.

Oscar - you are a gem, thanks so much, saved me so much time and helped me instantly!!! Thanks so much!!

OMG my whole world just changed after reading and trying out the above array examples. I've been pulling out my hair trying to find out how and why Excel doesn't accomodate such a function and, WHA-LAH!! Thank you GREATLY!!

Ak and Sun Kissed,

Thanks for commenting!!

I just wanted to say thank you soooo much. I have been trying to figure out this formula for 2 days!! I finally stumbled upon your notations above and had my formula problem resolved in an 20mins.

You are amazing :)

Dana,

Thank you for commenting!! I added a link to the explanation on the table of contents.

This worked very well. Your example was simple enough that I could extrapolate how to use it across multiple worksheets and how to copy it across multiple columns. Thank you.

Hello Oscar,

I have tried to use the vbaVlookup function stated above. I opened VBA using Atl+F11 in Excel 2007, I then opened a new module and pasted the text stated above into the module.

I then tried to use the function in excel and it returns the same value as the normal VLOOKUP function, the top most value associated with the lookup value. It is not going down the table to find the next lookup value for the output.

Any suggestions?

Thanks,

Scott

Hi Scott!

I then tried to use the function in excel and it returns the same value as the normal VLOOKUP function, the top most value associated with the lookup value. It is not going down the table to find the next lookup value for the output.Any suggestions?

You have to enter the array formula in a cell range, example above: C9:C11. Then press and hold Ctrl + Shift and then press Enter to create an array formula.

Ethan,

I am happy you found it useful! Thank you for commenting!

Perfect! Thanks Oscar!

Oscar,

I've a question. please have a look at it.

Appreciated!

COUNTRY VISIT DATE NO OF VISITS CITY1 CITY2 CITY3 CITY4 CITY5 CITY6

AUSTRALIA 14-Mar-12 1 SYDNEY

ENGLAND 31-Mar-12 1 LONDON

USA 18-May-11 3 NY LA TDL

INDIA 19-Apr-10 3 DELHI BOMBAY HYD

Question:

If I lookup for the USA, the result I need is the city names in adjacent cells like below

Enter country name: USA NEWYORK LA TDL

I don't want the blank cells in between, is there any way around?

Appreciated!

Ahmed,

I don't want the blank cells in between, is there any way around?What formula are you using?

Thanks Oscar!

Here is the formula i'm using

=INDEX($A$2:$I$5, SMALL(IF($C$11=$A$2:$A$5, ROW($A$2:$A$5)-MIN(ROW($A$2:$A$5))+1, ""), ROW(A1)),COLUMN(A1))

USA Wednesday, May 18, 2011 3 0 0 NY 0 LA TDL

USA Monday, April 19, 2010 3 DELHI BOMBAY 0 HYD 0 0

how can i attach my workbook, so that it becomes easy for you to understand what i exactly need.

Many thanks!

I just want to see the list of ONLY cities in adjacent cells,

Oscar?

I'm waiting for your response.

Thanks!

Ahmed,

See attached file:

Ahmed.xlsx

Hi Oscar,

I am trying to use your vba code to do the "return values horizontally" but it has been unsuccessful as when I tried to enter custom function array formula the cell range would all end up having the same formula as the first top right formula. I was wondering if you could help ...

I noticed that in your Array formula in cell C14:D14 you had the lookup_value as B8? (which is the cell that has text value "Return values vertically")? is this correct? I have been inputting the formula with reference to that first look up value....

Thanks so much and this is such a great helpful resource for excel!

Yuli,

I am trying to use your vba code to do the "return values horizontally" but it has been unsuccessful as when I tried to enter custom function array formula the cell range would all end up having the same formula as the first top right formula. I was wondering if you could help ...You are right, there is an error with the cell references. They should have been absolute. Well, now they are, I edited the post.

I noticed that in your Array formula in cell C14:D14 you had the lookup_value as B8? (which is the cell that has text value "Return values vertically")? is this correct? I have been inputting the formula with reference to that first look up value....You are right, another error.

Thanks for commenting!!

Hi Oscar,

Thank you so much for this wonderful example, it helps a lot. I have a 44000 rows spreadsheet, I put the formula in the cell it works great but eveytime I copy it and paste it to the next cell I have to hold crtl+shift and click on enter in order to get the result. Is there an easy way to do it?

this is the formula I am using

{=INDEX(Sheet1!$B$2:$B$15829, SMALL(IF(Sheet1!$A$2:$A$15829='Purchase Orders'!B150, ROW(Sheet1!$A$2:$A$15829)-MIN(ROW(Sheet1!$A$2:$A$15829))+1, ""), COLUMN(A1)))}

thanks for your help

Hi Oscar,

Thank you so much for the great example. I am working on a 45000 rows spreadsheet, I added the formula in the first cell and it worked like a charm but the things is when I try to copy and past the formula in other cells it wont work unless select the cell, put the cursor inside the formula box and hold crtl+shift then hit enter. Is there an easy way to copy the formula throughout the 45000 rows. Here is the formula I am using

{=INDEX(Sheet1!$B$2:$B$15829, SMALL(IF(Sheet1!$A$2:$A$15829='Purchase Orders'!B150, ROW(Sheet1!$A$2:$A$15829)-MIN(ROW(Sheet1!$A$2:$A$15829))+1, ""), COLUMN(A1)))}

Thanks for any feedback,

Moncef

Got it, the thing is the calculation tab in the tools-> option was changed from automatically to manually.

OSCAR,

Thanks alot!,

i've been away for sometime...

you solved my problem.

You are great!

Thanks again....

[Oscar Says:

April 9th, 2012 at 8:17 am

Ahmed,

See attached file:

Ahmed.xlsx

}

Ahmed,

Thanks for commenting!

Are you still answering questions?

Paul,

Yes I am still answering questions.

Thanks Oscar:

I am trying to use the Index formula in the first example, return multiple values vertically: I have a spreadsheet with 20 columns or so. My data begins in cell A1 and I have gotten rid of the headers. So my sample extends from A1 through AC29. The data to lookup in in column 1 and the info I want returned is in Column 2.

I am writing the formula on a different tab than the worksheet. I don't understand the reference to Row A1 at the end of your example because in your spreadsheet there is no date in Row 1 and it is not the intersecting points of where your data begins. I will also want to return data from columns 3 and 4 after I get the hang of this.

Paul

Oscar,

I have a little issue, I have read almost every post on this forum and still can’t a formula figured out. My sheet looks like this:

a b c

1

2 83301-3626 260

3 83301-0000 260

4 83301-5160 120

5 83301-8181 120

6 83311-9703 170

7 83316-5557 220

8 83316-5043 190

9 83318-5004 160

10 83325-5232 120

11 83328-5033 260

12 83328-5033 260

13 83333-0000 220

14 83335-5721 120

15 83338-2055 180

16 83338-2845 260

17 83338-2135 120

18 83341-2060 130

19 83347-0000 120

20 83348-0000 120

21 83350-9471 170

22 83350-9369 160

23 83350-9471 160

24 83350-9772 250

25

I need a formula that will look up any given value (zip code) in column “B” and return its respective value from column “C.” And then add those returned values together to produce one sum of the values for each zip code. So that the final result would look something like this:

83301 760 83311 170 83316 410 Etc…

This is only a small sample of the data pool that I am working with; normal size is usually in the high 100’s of randomly assorted zip codes. I would also like to be able to drop the additional 4 digits from each zip code without having to manually delete each one. Any help would be incredibly appreciated.

Hi Oscar!!! This post really helped me a lot!!!

I wanted to ask you something... I've been trying for days to "adjust" this same example to one I have. I would like the same output of this formula but searching for cells that CONTAIN a given word, instead of mathing for the exact same word...

Like in this case, instead of looking for cells that says only "Paper", I want the formula to give back not only the result for "Paper", but also the result for "Paper Clip"..

Thank you very much in advance and keep up the great job!!!

Tank,

I would suggest using a pivot table.

First drop the additional 4 digits from zip codes:

Formula in D2:

=LEFT(A1, FIND("-", A1)-1)

Then sum values with a pivot table.

Open attached file:

Tank.xlsx

Yair,

Read this post:

Search for a text string and return multiple adjacent values

Paul,

I am trying to use the Index formula in the first example, return multiple values vertically: I have a spreadsheet with 20 columns or so. My data begins in cell A1 and I have gotten rid of the headers. So my sample extends from A1 through AC29. The data to lookup in in column 1 and the info I want returned is in Column 2.I am writing the formula on a different tab than the worksheet.

See attached workbook:

Paul2.xls

I don't understand the reference to Row A1 at the end of your example because in your spreadsheet there is no date in Row 1 and it is not the intersecting points of where your data begins.

ROW(A1) contains a relative cell reference, read step 3 in the explanation in this post.

I will also want to return data from columns 3 and 4 after I get the hang of this.See Sheet3 in the attached file.

Hi Oscar, Love it! But have a question.

My array is going to constantly change, as it is pulled daily from an outside data source. As a result, I do not want to have to constantly update the range. In your 1st example, $B$2:$B$6 could be $B$2:$B12 tomorrow or $B$2:$B$20 the next for me.

I've tried naming the range, but It's not working. Any ideas?

Thanks SOOO much for putting this together.

What a pleasure to have an expert and considerate helper on hand.

Thanks,

Paul

You are awesome! I've been looking for an easy way to do this for a long time and yours works flawlessly!

Thanks again! :)

I do have 1 question. I haven't tried it yet and you may already have an article on it, but is it possible to use this to do a comparison on the value returned from the array with a different column on the cell you are doing the vlookup on?

I know this is probably going to be really complicated, but it would be really cool if I could do this. Your formula does this, but instead of returning 8 results in 8 columns. I just want the 1 result that matches b2 in the column next to it in the array. In your example if you added QTY to column D for your array and your results you knew the price of the item you wanted to return and just needed the QTY returned (rather than 2 prices, it just returns the QTY)

Here is how I would consider this working logically. Not sure if this is usful or not, but hoping it can clairfy the question.

if B14 = {B2:C6}

return {all cells in C2:C6 that match B14} (your function returns this)

if C2:C6 results match C14

return cell in D2:D6 that matches (will only have 1 match)

TJ,

Vlookup-dynamic-named-range.xls

shawn,

Try this:

Shawn.xlsx

Wonderful. Thank you!

Wow, even better! I don't even need VBS! :)

Any chance you have something written up on how that works? I'm a decent scripter and such but not really sure where you would start when building a formula like that. Been working with excel a long time and always get lost when people use index and min to come up with stuff.

I'm sorry if I'm missing something here but I'm looking at the return horizontally and I'm getting #value in return. Even the example file I downloaded returned #value if you simply enacted the formula. could this be becuase I'm using excel 2007? Also why does it look like you named the range as tbl?

Oscar,

First off thanks for doing what you do.

Sorry about the earlier post. I did'nt fully understand the creating Array formula part.

But now that I've got that down, I still get a #num in your example file that i downloaded. I recreated your example and recieved the same results. I think I might be missing something in the column reference. what does that mean? why not C1 or D25?

I feel like I'm sooo close to getting this

Oscar,

First off thanks for doing what you do.

Sorry about the earlier post. I didn’t fully understand the creating Array formula part, even though you said it like 20 times in the comments. But after reviewing the comments I still get a #num in your example file that I downloaded. I recreated your example and received the same results. I think I might be missing something in the column reference. What does that mean? Why not C1 or D25?

I feel like I'm sooo close to getting this

MR,

You can remove #num errors with IFERROR(array_formula, "").

Example array formula:

Oscar,

Hate to bother you again but have one more. I need to search in a column and find a value, then return the value below that value and the next say 2-4 values below that same value.

So I will have A1:A15 with values. In cell B1 I want to return the value I need, in B2 the value below the first, in B3 the value below that one and so on.

Thanks,

Paul

"Return multiple values horizontally" - I was looking for it. Fortunately I found this article in Google, otherwise I could have spent long hours on this. Thanks Oscar a lot.

Paul,

Formula in cell B2:Copy cell and paste down as far as needed.

Explaining formula in cell B2Step 1 - Return the relative position of an item in an array that matches a specified valueMATCH($B$1, $A$1:$A$15,0)

becomes

MATCH("GG", {"AA", "BB", "CC", "DD", "EE", "FF", "GG", "HH", "II", "JJ", "KK", "LL", "MM", "NN", "OO"}, 0)

and returns 7.

Step 2 - Add 1 to calculale the row number to the value below.MATCH($B$1, $A$1:$A$15,0)+ROW(A1)

becomes

7 + 1

and returns 8.

Step 3 - Return the value of a cell at the intersection of a particular row and column.=INDEX($A$1:$A$15, MATCH($B$1, $A$1:$A$15,0)+ROW(A1))

becomes

=INDEX($A$1:$A$15, 8 )

becomes

=INDEX({"AA", "BB", "CC", "DD", "EE", "FF", "GG", "HH", "II", "JJ", "KK", "LL", "MM", "NN", "OO"}, 8 )

and returns "HH".

Download *.xlsx filePaul.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.

Download excel *.xlsx file

Hahale.xlsx

Thank you so much.

I can do a "paste special" values and readjust the columns.

Hi Oscar,

I have playing with the excel file that you've created for me.

One problem

the teacher name ms. smith should up twice if should have been ms. smith for english and mr. johnson for the art class.

How to correct that?

Thanks so much.

Hahale,

you are right!

Here is the corrected formula:

You're a genius!!! Thank you so much.

Oscar,

Thanks so much for your website! It's fantastic...

I just downloaded the Vlookup-vba3 doc and it's giveing me #VALUE! errors. I haven't edited it at all yet. I wondered what I might be missing. I enabled the macros...

Will,

I downloaded Vlookup-vba3.xls and I get #VALUE errors also. The cells show correct values until i click "Enable editing" in excel 2010. I am not sure why. Here is how you fix it:

1. Select a #value cell

2. Click in formula bar

3. Press and hold CTRL + SHIFT

4. Press Enter

5. Release all keys

Thanks!!!

I copied and pasted this into another sheet I wanted to use it in and I am getting the "#NAME?" error when I follow the instructions wothout spaces. I am using your UniqueFilterVBA and it's working great!

This is my formula:

=vbaVlookup(M2,N:O, 2)

But when I change it to (Including the space between "," and "N:0" it works. Is that normal?

=vbaVlookup(M2, N:O, 2)

Will,

I included spaces in the udf vbaVlookup and I am not getting a #NAME? error. (Excel 2010)

My formula is this =INDEX(Sheet1!$B$2:$B$14788, SMALL(IF(Sheet2!A2=Sheet1!$A$2:$A$19247, ROW(Sheet1!$A$2:$A$19247)-MIN(ROW(Sheet1!$A$2:$A$19247))+1, ""), COLUMN(B1)))

All works but when I copy it from cell B2 to I2 I get #REF error.

From B2 to E2 is ok but from F2 to I2 is #REF error

Please help...

Thank you for showing the excel awesome capability.

[...] This formula derives from one of the most popular blog posts here: How to return multiple values using vlookup [...]

Will this work with named ranges?

Shri,

yes, you can convert the cell references to named ranges.

This is brilliant. Thanks for sharing!!

Hey, hi i am new to VBA and array and so curious about it as it could help me so much in my excel. can you please suggest how to start

Manu,

Here is a great link:

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 click the black dot (see picture above)

I hope this makes sense.

I'm trying to use your array formula, which works great until the value that I am trying to lookup changes. Here is a sample of the data.

Name ID Results ID Tracking #

Test1 123 555 123 555

Test2 123 565 123 565

Test3 123 456 444

Test4 456 456 678

Test5 456 789 999

Test6 789 121 154

The formula entered into the Results column is:

{=IFERROR(INDEX($P$2:$P$7,SMALL(IF($M2=$O$2:$O$7,ROW($O$2:$O$7)-MIN(ROW($O$2:$O$7))+1,""),ROW(S1))),"")}

The only way I get the correct results for Test 4 and Test 5 is to change the last part from ROW(S4) and ROW(S5) back to ROW(S1), which is a pain considering the numbers for the real data changes every 2-3 rows.

I tried changing it to:

{=IFERROR(INDEX($P$2:$P$7,SMALL(IF($M2=$O$2:$O$7,ROW($O$2:$O$7)-MIN(ROW($O$2:$O$7))+1,""),ROW(S$1))),"")}

and this duplicates the first value found, such as Test 1, Test 2, and Test 3 will all have a result of 555. Then Test4 and Test5 both say 444. So it will not progress to the next number.

I also tried using ROW($S1) and I get the same results as show in the test above. Nothing I have done to this works.

Is there a way to correct this and to get it to continue to search the entire array?

That sample data looks wierd, so I wanted to upload it a little cleaner. Column S is an empty column.

These are Results with this formula:

{=IFERROR(INDEX($P$2:$P$7,SMALL(IF($M2=$O$2:$O$7,ROW($O$2:$O$7)-MIN(ROW($O$2:$O$7))+1,""),ROW(S1))),"")}

L M N

Name ID Results

Test1 123 555

Test2 123 565

Test3 123

Test4 456

Test5 456

Test6 789

O P

ID Tracking #

123 555

123 565

456 444

456 678

789 999

121 154

These are the results with this formula:

{=IFERROR(INDEX($P$2:$P$7,SMALL(IF($M2=$O$2:$O$7,ROW($O$2:$O$7)-MIN(ROW($O$2:$O$7))+1,""),ROW(S$1))),"")}

L M N

Name ID Results

Test1 123 555

Test2 123 555

Test3 123 555

Test4 456 444

Test5 456 444

Test6 789 999

O P

ID Tracking #

123 555

123 565

456 444

456 678

789 999

121 154

T F,

Array formula in cell N2:

Download excel *.xlsx file

T-F.xlsx

Thank you so much Oscar...that worked! I am speechless...I never would have thought of that!

Thanks!!

Hi,

I had issues with this when running the Module vbaLookup. It would continue displaying the first value until the formula eventually excluded the first result from the array, then finally moving to the next result. Example:

A B C D

1 John A

2 Mary B

3 Pete C

4 John D

5 Luke E

6 John F

John A A A D D F

(The result ideally would of been John A D F. Instead, I'm having to continue to extend the formula to get the result.)

Sorry, I figured it out!

However, a new problem has arisen...

The things I am looking up are sometimes large strings of text (it's a survey) and it seems from testing that a certain amount of text will break the formula.

Josh,

Excel version? How much is a certain amount of text?

Can you provide an example?

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/2009/10/25/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))

Download 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)))

Download *.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,

Download *.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)))

Download 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)))

Download 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 downloading the 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. Click on "Evaluate Formula" button

3. Click "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?