*The tutorial shows a few ways to Vlookup multiple matches in Excel based on one or more conditions and return multiple results in a column, row or single cell.*

When using Microsoft Excel for data analysis, you may often find yourself in situations when you need to get all matching values for a specific id, name, email address or some other unique identifier. An immediate solution that comes to mind is using the Excel VLOOKUP function, but the problem is that it can only return a single match.

Vlookup for multiple values can be done via a combined use of several functions. If you are far from being an Excel expert, don't hurry to leave this page. I will do my best to explain the underlying logic so that even a novice could understand the formulas and adjust them for solving similar tasks. Even better, I will show you another possible solution that takes just a few mouse clicks and does not require any knowledge of Excel formulas at all!

As mentioned in the beginning of this tutorial, there is no way to make Excel VLOOKUP return multiple values. The task can be accomplished by using the following functions in an array formula:

- IF - evaluates the condition and returns one value if the condition is met, and another value if the condition is not met.
- SMALL - gets the k-th smallest value in the array.
- INDEX - returns an array element based on the row and column numbers you specify.
- ROW - returns the row number.
- COLUMN - returns the column number.
- IFERROR - traps errors.

Below you will find a few examples of such formulas.

Let's say, you have the seller names in column A and the products they sold in column B, column A containing a few occurrences of each seller. Your goal is to get a list of all products sold by a given person. To have it done, please follow these steps:

- Enter a list of unique names in some empty row, in the same or another worksheet. In this example, the names are input in cells D2:G2:

**Tip.**To quickly get all different names in a list, you can use a formula to extract distinct values in Excel. - Under the first name, select a number of empty cells that is equal to or greater than the maximum number of possible matches, enter one of the following array formulas in the formula bar, and press Ctrl + Shift + Enter to complete it (in this case, you will be able to edit the formula only in the entire range where it's entered). Or, you can enter the formula in the first cell, hit Ctrl + Shift + Enter, and then copy the formula to a few more cells down the column (in this case, you will be able to edit the formula in each cell individually).
`=IFERROR(INDEX($B$3:$B$13, SMALL(IF(D$2=$A$3:$A$13, ROW($B$3:$B$13)-2,""), ROW()-2)),"")`

or

As you see, the 1

^{st}formula is a bit more compact, but the 2^{nd}one is more universal and requires fewer modifications (we will elaborate more on the syntax and logic a bit further). - Copy the formula to other columns. For this, select the range of cells where you've just entered the formula, and drag the fill handle (a small square at the lower right-hand corner of the selected range) to the right.

The result will look something similar to this:

This is an example of intermediate to advanced uses of Excel that implies basic knowledge of array formulas and Excel functions. Working from the inside out, here's what you do:

**IF function**At the core of the formula, you use the IF function to get the positions of all occurrences of the lookup value in the lookup range: IF(D$2=$A$3:$A$13, ROW($B$3:$B$13)-2,"")

IF compares the lookup value (D2) with each value in the lookup range (A3:A13), and if the match if found, returns the relative position of the row; an empty string ("") otherwise.

The relative positions of the rows are calculated by subtracting 2 from ROW($B$3:$B$13) so that the first row has position 1. If your return range begins in row 2, then subtract 1, and so on. The result of this operation is the array {1;2;3;4;5;6;7;8;9;10;11}, which goes to the value_if_true argument of the IF function.

Instead of the above calculation, you can use this expression: ROW(lookup_column)- MIN(ROW(lookup_column))+1, which returns the same result but does not require any changes regardless of the return column location. In this example, it'd be ROW($A$3:$A$13)- MIN(ROW($A$3:$A$13))+1.

So, at this point you have an array consisting of numbers (positions of matches) and empty strings (non-matches). For cell D3 in this example, we have the following array:

If you check with the source data, you will see that "Adam" (lookup value in D2) appears on the 3

^{rd}, 8^{th}and 10^{th}positions in the lookup range (A3:A13).**SMALL function**

Next, the SMALL(array, k) function steps in to determine which of the matches should be returned in a specific cell.With

*array*already established, let's work out the*k*argument, i.e. the k-th smallest value to be returned. For this, you make a sort of an "incremental counter" ROW()-n, where "n" is the row number of the first formula cell minus 1. In this example, we entered the formula in cells D3:D7, so ROW()-2 returns "1" for cell D3 (row 3 minus 2), "2" for cell D4 (row 4 minus 2), etc.As the result, the SMALL function pulls the 1

^{st}smallest element of the array in cell D3, the 2nd smallest element in cell D4, and so on. And this transforms the initial long and complex formula into a very simple one, like this:**Tip.**To see the calculated value behind a certain part of the formula, select that part in the formula bar and press F9.**INDEX function**This part is easy. You use the INDEX function to return the value of an array element based on its row number.

**IFERROR****function**And finally, you wrap the formula in the IFERROR function to handle possible errors, which are inevitable because you cannot know how many matches will be returned for this or that lookup value, and therefore you copy the formula to a number of cells equal to or greater than the number of possible matches. Not to scare your users with a bundle of errors, simply replace them with an empty string (blank cell).

Putting all this together, we get the following generic formulas to Vlookup multiple values in Excel:

**Formula 1**:

IFERROR(INDEX(*return_range*, SMALL(IF(*lookup_value* = *lookup_range*, ROW(*return_range* )- *m* ,""), ROW() - *n* )),"")

**Formula 2**:

IFERROR(INDEX(*return_range*, SMALL(IF(*lookup_value* = *lookup_range* , ROW(*lookup_range*) - MIN(ROW(*lookup_range* ))+1,""), ROW() - *n*)),"")

Where:

*m*is the row number of the first cell in the return range minus 1.*n*is the row number of the first formula cell minus 1.

In case you want to return multiple values in rows rather than columns, change the above formulas this way:

`=IFERROR(INDEX($B$3:$B$13, SMALL(IF($D3=$A$3:$A$13, ROW($B$3:$B$13)-2,""), COLUMN()-4)),"")`

Or

`=IFERROR(INDEX($B$3:$B$13,SMALL(IF($D3=$A$3:$A$13,ROW($A$3:$A$13)- MIN(ROW($A$3:$A$13))+1,""),COLUMN()-4)), "")`

Like in the previous example, both are array formulas, so remember to press the Ctrl + Shift + Enter shortcut to complete them correctly.

The formulas work with the same logic as in the previous example, except that you use the COLUM function instead of ROW to determine which matching value should be returned in a specific cell: COLUMN()-n. Where n is the column number of the first cell where the formula is entered minus 1. In this example, the formula is input in cells E2:H2. With E being the 5^{th} column, *n* is equal to "4" (5-1=4).

Wrapping up, here are the generic formulas for Vlookup with multiple results returned in rows:

**Formula 1**:

IFERROR(INDEX(*return_range*, SMALL(IF(*lookup_value* = *lookup_range*, ROW(*return_range*) - *m*, ""), COLUMN() - *n*)), "")

**Formula 2**:

IFERROR(INDEX(*return_range*, SMALL(IF(*lookup_value* = *lookup_range*, ROW(*lookup_range*) - MIN(ROW( *lookup_range*))+1,""),COLUMN() - *n*)), "")

Where:

*m*is the row number of the first cell in the return range minus 1.*n*is the column number of the first formula cell minus 1.

You already know how to Vlookup for multiple values in Excel based on one condition. But what if you want to return multiple matches based on two or more criteria? Taking the previous examples further, what if you have an additional *Month* column, and you are looking to get a list of all products sold by a given seller in a specific month?

If you are familiar with arrays formulas, you may remember that they allow using asterisk (*) as the AND operator. So, you can just take the formulas discussed in the two previous examples, and have them check multiple conditions as demonstrated below.

IFERROR(INDEX(*return_range*, SMALL(IF(1=((--(*lookup_value1=lookup_range1*)) * ( --(*lookup_value2*=*lookup_range2*))), ROW(*return_range*)-*m*,""), ROW()-*n*)),"")

Where:

*m*is the row number of the first cell in the return range minus 1.*n*is the row number of the first formula cell minus 1.

Assuming the Seller list (*lookup_range1*) is in A3:A30, the Month list (*lookup_range2*) is in B3:B30, the seller of interest (*lookup_value1*) is in cell E3, and the month of interest (*lookup_value2*) is in cell F3, the formula takes the following shape:

`=IFERROR(INDEX($C$3:$C$30, SMALL(IF(1=((--($E$3=$A$3:$A$30)) * (--($F$3=$B$3:$B$30))), ROW($C$3:$C$30)-2,""), ROW()-2)),"")`

This layout may be useful for creating a dashboard, e.g. your users can enter a name in E3, month in F3 and get a list of products in column G:

If you want to pull multiple values based on multiple criteria sets, you may prefer the horizontal layout where results are returned in rows. In this case, use this following generic formula:

IFERROR(INDEX(*return_range*, SMALL(IF(1 = ((--(*lookup_value1=lookup_range1*)) * (--(*lookup_value2* = *lookup_range2*))), ROW(*return_range*) - *m*, ""), COLUMN() - *n*)),"")

Where:

*m*is the row number of the first cell in the return range minus 1.*n*is the row number of the first formula cell minus 1.

For our sample dataset, the formula goes as follows:

`=IFERROR(INDEX($C$3:$C$30, SMALL(IF(1=((--($E3=$A$3:$A$30)) * (--($F3=$B$3:$B$30))), ROW($C$3:$C$30)-2,""), COLUMN()-6)),"")`

And the result can resemble this:

In a similar manner, you can do multiple Vlookup with three, four or more conditions.

Basically, the formulas to Vlookup multiple values with multiple conditions work with the already familiar logic, explained in the very first example. The only difference is that the IF function now tests multiple conditions:

1=((--(*lookup_value1*=*lookup_range1*))*(--(*lookup_value2*=*lookup_range2*))*…)

The result of each lookup_value=lookup_range comparison is an array of logical values TRUE (condition is met) and FALSE (condition is not met). The double unary operator (--) coerces the logical values into 1's and 0's. And because multiplying by zero always gives zero, in the resulting array, you have 1 only for those elements that meet all of the specified conditions. Now, you simply compare the final array with number 1 so that the ROW function returns the numbers of rows that meet all the conditions, an empty string otherwise.

**A word of caution**. All of the multiple Vlookup formulas discussed in this tutorial are array formulas. As such, each formula iterates through all elements of the arrays every time the source data is changed or the worksheet is recalculated. On large worksheets containing hundreds or thousands of rows, this may significantly slow down your Excel.

This is how you Vlookup multiple matches in Excel using formulas. To have a closer look at the examples and probably reverse-engineer the formulas for better understanding, you are welcome to download the Excel Vlookup Multiple Values sample worksheet.

I will be upfront - I don't know a formula to Vlookup duplicates that would output multiple matches in a single sell. However, I do know a formula-free (read "stress-free" :) way to do this, by using two add-ins included with our Ultimate Suite for Excel. If you don't have it in your Excel yet, you can download a free 14-day trial version here, and then follow the steps outlined below.

As shown in the screenshot, we continue working with the dataset we've used in the previous example. But this time we want to achieve something different - instead of extracting multiple matches in separate cells, we want them to appear in a single sell, separated with a comma, space, or some other delimiter of your choosing.

In your main table, enter a list of unique names in the first column, months in the second column, and arrange them like shown in the screenshot below. After that, carry out the following steps:

- Select your main table or click any cell within it, and then click the
**Merge Two Tables**button on the ribbon:

- The add-in is smart enough to identify and pick the entire table, so you just click
*Next*:**Tip.**When using the tool for the first time, it stands to reason to select the**Create a backup copy of the worksheet**box in case something goes wrong. - Select the lookup table, and click
*Next*.

- Choose one or more matching pairs of columns that should be compared in the main table and lookup table (in this example, it's the
*Seller*and*Month*columns), and then click*Next*.

- Select the column(s) from which you want to pull matching values (
*Product*in this example), and click*Next*.

- Tell the add-in how exactly you want multiple matches to be arranged in the main table. For this example, we need the following option:
**Insert rows with duplicate matching values after the row with the same value**. Make sure that no other option is selected and click*Finish*.

At this point, you will have the following result - all matching rows are pulled to the main table and grouped by the values in the lookup columns - first by Seller, and then by Month:

The resulting table already looks nice, but it's not exactly what we wanted, right? As you remember, we are looking to Vlookup multiple matches and have them returned in a single sell, comma or otherwise separated.

To merge "duplicate rows" in a single row, we are going to use another tool - Combine Rows Wizard.

- Select the table produced by the Merge Tables tool (please see the screenshot above) or any cell within the table, and click the
**Combine Rows**button on the ribbon:

- Check if the add-in's got the table right, and click
*Next*:

- Select the key column or columns (
*Seller*and*Month*in this example), and click*Next*:

- Select the column(s) that contains multiple matches (
*Product*in this example), choose the desired delimiter (semicolon, comma, space or line break), and click*Finish*.Optionally, you can enable one of the additional features, or both:

*Delete duplicate values*- if the column to be merged contains a few identical values, the first occurrence will be kept, duplicate matches will be deleted.In this example, we do not check this option, and the add-in returns all found matches. For example, in cell C2, we have this string:

*Lemons, Bananas, Apples, Lemons, Bananas*(please see the result on step 5 below). If you choose to delete duplicates, the result would be:*Lemons, Bananas, Apples*.*Skip empty cells*- self-explanatory :)

- Allow the add-in a few seconds for processing, and you are all set!

This is how you can look up and return multiple values in Excel using our tools. If you are curious to give them a try, a fully-functional evaluation version of the Ultimate Suite is available for download below.

Vlookup Multiple Values - examples (.xlsx file)

Ultimate Suite - trial version (.zip file)

Excel formulas
CSV
Excel functions
Print
Financial functions
Vlookup
Merge data in Excel
SumIf
CountIf
Excel Compare
If statement
Excel Charts
Pivot Table
Conditional formatting
Excel formatting
Excel duplicates
Excel date
Excel tips
Excel macro
Outlook
Google Sheets
Outlook duplicates
Shared email templates

Category: Excel Tips

60+ professional tools for Excel

2019–2010 to do your daily work

2019–2010 to do your daily work

Incredible product, even better tech support…AbleBits totally delivers!

Deborah Bryant

Anyone who works with Excel is sure to find their work made easier

Jackie Lee

The best spent money on software I've ever spent!

Patrick Raugh

Ablebits is a fantastic product - easy to use and so efficient.

Debra Celmer

Excel is at its best now

Annie C.

I don't know how to thank you enough for your Excel add-ins

Jennifer Morningstar

Anybody who experiences it, is bound to love it!

Kumar Nepa

AbleBits suite has really helped me when I was in a crunch!

Nelda Fink

I have enjoyed every bit of it and time am using it

Christian Onyekachi Nwosu

It's the best $100 we've ever spent!

Mike Cavanagh

I love the program, and I can't imagine using Excel without it!

Robert Madsen

One word… WOW!

Dave Brown

Love the products!

David Johnston

It is like having an expert at my shoulder helping me…

Linda Shakespeare

Your software really helps make my job easier

Jeannie C.

Thanks for a terrific product that is worth every single cent!

Dianne Young

I love your product

Brad Gibson

Awesome!!!

Sheila Blanchard

## 258 responses to "How to VLOOKUP multiple values in Excel with one or more criteria"

Good day !

Using the row array formula =IFERROR(INDEX($B$3:$B$13,SMALL(IF($D3=$A$3:$A$13,ROW($A$3:$A$13)- MIN(ROW($A$3:$A$13))+1,""),COLUMN()-4)), ""), let's say that Adam's first line column B is blank rather than "Bananas" - how can I skip the blank and show the next real value in cell E1? (in other words, E1 would be "Oranges")

Thanks!

Did you find the answer to this question?

=IFERROR(INDEX($B$3:$B$13,SMALL(IF(($D3=$A$3:$A$13)*($B$3:$B$13""),ROW($A$3:$A$13)-MIN(ROW($A$3:$A$13))+1,""),COLUMN()-4)), "")

=IFERROR(INDEX($B$3:$B$13,SMALL(IF(($D3=$A$3:$A$13)*($B$3:$B$13 <>""),ROW($A$3:$A$13)-MIN(ROW($A$3:$A$13))+1,""),COLUMN()-4)), "")

Hi

I need some help to do retrieve information from an excel sheet and populate specific columns in another excel sheet within the same workbook. I tried this but I have given up with the formula to get it working.

For examle,

1. Sheet 1 is the master sheet where I enter the information into each cell.

For example,

Device ID Device MAC Hostname Service Tag

FS-001 Z9264F-ON 20040F0E3B4E frxsw-ds01 H5WMXC2

I need help to make a formula to retrieve information from a 'master' sheet (where information is entered manually) and populate specific cells in a 'target' sheet to avoid re-entering this information.

For example,

Master sheet

-----------

A B C D

1 Device ID Device MAC Hostname Service Tag

2 FS-001 00:00:00:00:00:00 device01 12345

Target Sheet

----------

A B C D E

1 Device ID Hostname IP address Device MAC Gateway

2 FS-001 device01 00:00:00:00:00:00

So, if on the 'Target' sheet A2 = 'Master' sheet A2's value of FS-001, then values from Master sheet B2 and C2 will be populated

in the cells of 'Target' sheet D2 and B2 respectively.

Have you found the formula for this? I need a similar one too.

I need help to get Max value from set value but if any data is 0 ( zero) then return result should be 0(zero). Pls see below data & result what I want. Pls advise.

A 20

B 20

C 30

A 40

B 10

D 60

B 35

D 40

A 0

D 0

A 40

Result should be

A 0

B 35

C 30

D 0

This should do it. Assuming your data begins in cell A1. Make sure you hit Ctrl+Shift+Enter when finished as this is an array formula. {=IF(MIN(IF("A"=$A$1:$A$11,$B$1:$B$11,""))=0,0,MAX(IF("A"=$A$1:$A$11,$B$1:$B$11,"")))}

=VLOOKUP(E5,A1:B8,2,0)&", "&INDEX(B1:B8,MATCH(E5,A1:A8,0)+2)

Dear Svetlana

Your pages on Excel are really incredible! I learned a lot, but understand that there is still so much to learn!

I am searching for a solution to retrieve data from an input sheet. When one selects one or more search terms in another sheet (from a pre-defined list) I would like to see all the results that meet the conditions. It is very similar to your examples here, "Vlookup multiple matches based on multiple conditions". But instead of AND I would need OR. Eg: if the search term is 'apples', I would like to see Excel giving me a list of all the apple sales. If the search terms are 'apples' and 'bananas', I would like to see Excel giving me a list of all the sales of apples and bananas. I am searching for hours ans hours, but cannot manage it... Maybe you have the solution?

Thanks in advance!

Hi

I need some help with retrieving value from a table:

min max 1 2 3 4 5 6 7 8 9 10

0 8 0.00% 0.00% 2.50% 4.00% 5.00% 6.00% 7.00% 8.00% 9.00% 10.00%

9 10 5.00% 5.00% 7.50% 9% 10% 11% 12% 13% 14% 15%

11 24 7.50% 7.50% 10% 11.50% 12.50% 13.50% 14.50% 15.50% 16.50% 17.50%

25 49 10.00% 10.00% 12.50% 14% 15% 16% 17% 18% 19% 20%

50 99 12.50% 12.50% 15% 16.50% 17.50% 18.50% 19.50% 20.50% 21.50% 22.50%

100 199 15.00% 15.00% 17.50% 19% 20% 21% 22% 23% 24% 25%

200 >200 17.50% 17.50% 20% 21.50% 22.50% 23.50% 24.50% 25.50% 26.50% 27.50%

I am using the formula: =INDEX($E$7:$N$13,MATCH(C3,D7:D13,2), MATCH(C10,Sheet1!E6:N6,0))

However, everytime it is reading only the first line

Can you help me out. Thanks for reading.

Your information was very helpful, thank you!!! I want to use the formulas to do an "or" statement. I am using the formula {=IFERROR(INDEX('CTE Detail'!$F$2:$F$900000,SMALL(IF('CTE Detail'!$M$2:$M$900000=auto!$F$1,ROW('CTE Detail'!$F$2:$F$900000)-1,""),ROW()-8)),"")}. This works as long as each program only has one subject code. I am bringing 2 spreadsheets together. What I am looking for is all of the students in certain classes by subject code (auto!$F$1). I have some programs that have multiple subject codes, how do I write the formula? I believe the * is for an "and" statement. Thank you,

I need to bring data from a master sheet where there is more than one entry, but might have slightly different data in a number of columns.

i need help

Rujan Shakya Absent

Abhishekh Wosti Present

Santosh Adhikari Present

Kshitiz Pokhrel Present

Rujan Shakya Present

in above details need to just take present name to another sheet

can anyone please help me

Hello - I tried to implement Formula 1 in this article, Vlookup multiple values and return results in a column, using the same data entered in the same cells in an Excel 2019 spreadsheet. But the formula doesn't work. To see why, I stripped out the IFERROR code and just ran the INDEX function without error checking. This showed that the formula produces a #Value error in the result. Does anyone know why the example is not working for me? I'm truly stumped.

Hi Larry,

It's difficult to say what the problem might be without seeing your data and formula. IFERROR is a very simple function and usually it does not cause any issues.

You can download our sample workbook with working formulas and copy/past the desired formula from there to try it on your data.

I have a similar issue, happening with your sample workbook too. I open the sample workbook, all the formulas show exactly as on this site. If I then click in the formula bar and hit enter the calculated values that were there when I open the book disappear. The formula is still there it just returns the error result value. The only difference in the formula bar is that the {} brackets round the formula are no longer there after calculating the formula locally.

James,

You most likely need to re-apply the "Ctrl-Shift-Enter' command to let the formula know it is based on an ARRAY.

Put your cursor in the cell with the formula and press Control-Shift and Enter.

Just a thought...

This was so incredibly helpful! I looked at other tutorials prior to this, and none of them gave the background on "why" or "what" each step did so I couldn't manipulate the other examples to fit my needs. This was the only truly comprehensive guide I came across. Thank you so much!

THANKS FOR YOUR HELP!!

doing similar to Formula 1. Vlookup multiple values and return results in a column

However I have the data in a separate sheet called 'Eg Data' hence I changed my formula to read as follows:

=IFERROR(INDEX('Eg Data'!$B$3:$B$13,SMALL(IF(D$5='Eg Data'!$A$3:$A$13,ROW('Eg Data'!$A$3:$A$13)- MIN(ROW('Eg Data'!$A$3:$A$13))+1,""), ROW()-2)),"")

In example data all is the same as per example above in 'Formula 1. Vlookup multiple values and return results in a column' and using same cells (a3:b13)

the other only difference is that the name in my case 'Adam' is not in cell D2 as per example above but in D5 and I want the results in D6 no in D3 as per example above.

This seems to give a problem when I moved the name from D2 to D5 and results to be in D6 instead of D3

Does the formula need changing?

This was a great tutorial, thank you very much! I got the formulas to work for my database, however I have an additional restriction. Bringing it to this example (I'm doing results in a ROW), let's say the products are listed as ORANGES - APRICOTS - BANANAS - APPLES - LEMONS. So if Adam sold Bananas, Oranges and Lemons, I want his results to show ORANGES - *blank* - BANANAS - *blank* - LEMONS. To make it easier to understand, my categories are time, so I need the results chronologically (6-10-12-14 hours). So if a result appears in 12 and 14, I want to see the 4 columns as blank-blank-result-result, but what I'm getting is result-result-blank-blank. Any thoughts?

Hi Francisco,

I guess you get precisely that result your are looking for by just stripping the SMALL function as follows bellow. It seems that the SMALL function is being used to fill up the blank cells so that the looked up products at the end they come one after another.

=IFERROR(INDEX($B$3:$B$13, IF(D$2=$A$3:$A$13, ROW($B$3:$B$13)-2,""), "").

I hope my reasoning sounds correct.

Sally Oranges

Robert Apricots

Adam Bananas

Tom Apples

Robert Lemons

Tom Apricots

Sally Bananas

Adam Oranges

Tom Bananas

Adam Lemons

Sally Apples

X y

Please suggest what should I do if my range is extended till x & y. Simply changing range in your formula is not working.

Please give me the result column c, as completed or incompleted when lookup from column a into column b.

Column (a) Column (b) Result Column (c)

OTE3‐L1‐IM‐300992‐01 12-Apr Completed

OTE3‐L1‐IM‐300992‐01 13-Apr Completed

OTE3‐L1‐IM‐300992‐02 13-Apr Completed

OTE3‐L1‐IM‐300992‐02 Incompleted

OTE3‐L1‐IM‐300992‐03 Incompleted

OTE3‐L1‐IM‐300992‐03 Incompleted

OTE3‐L1‐IM‐300992‐03 Incompleted

OTE3‐L1‐IM‐300992‐03 Incompleted

Thanks a lot for your effort you saved my time.

Very helpful. Thank You!!!

HI

When i use this code the excel have a error

=IFERROR(INDEX($B$3:$B$13, SMALL(IF(D$2=$A$3:$A$13, ROW($B$3:$B$13)-2,""), ROW()-2)),"")

the core of error begins from==>

IF(D$2=$A$3:$A$13, ROW($B$3:$B$13)-2,"")

i dont know what should i do :-(

thank you

i fund the issue

the problem was in

ROW()

cause of copy pasting the formula it reffer to the cell which is copied and it is not the rank that we want. so should change to 1,2,3 or "ROW()-cell row number+1"

You're the best! This saved me so many headaches, thank you so much!!

I stumbled upon this formula and spent about 2 hours trying to get it to work, but I finally did after seeing an error I was making in the comments (our fault for not reading fully)

My question is, using the very top example with sellers and produce, I manually need to change my cell reference from $D2 to $D3. My spread sheet contains over two hundred sellers and about a hundred different types of produce. So in a separate column I have my sellers, Adam being D2 and Robert D3. I can drag the formula for Adam all 100 rows.

But if I stop at row 25, and switch to D3 for Robert, I cannot copy them down the same way. I have to modify $D3 and ALSO the row to be -24 (when it was originally -2).

Is there a way around this? I'd rather only have to change one aspect instead of two if possible because there are so many. I can't set a predetermined spacing because it changes daily.

Thanks!

Side note, the formula I was using was this one below:

=IFERROR(INDEX($B$3:$B$13,SMALL(IF(D$2=$A$3:$A$13,ROW($A$3:$A$13)- MIN(ROW($A$3:$A$13))+1,""), ROW()-2)),"")

Goal: I would like to vlookup or index match an ID from another workbook using a vba button that then returns the ID name, City, Region, etc. My goal is that I have a button I search the ID # and it returns the criteria that I want that is located in another worksheet. I know to start it with the Workbooks.Open more focused on how to do the index match or vlookup to return the criteria from that sheet. Thanks

The one about Vlookup of multiple values is an absolute beauty. I have needed this function desperately and it worked exactly the way I have needed it. I don't know how to thank you. Still Thanks a million.

IF YOURS WORKS FOR THE FIRST CELL BUT NOT THE REST, CLICK ON THE FORMULA BAR OF THE FIRST CELL AND CLICK - CTR SHFT ENTER - THEN DRAG THAT FORMULA. THE FORMULA SHOULD NOW HAVE {funky brackets}.

=IFERROR(INDEX($B$3:$B$13,SMALL(IF(D$2=$A$3:$A$13,ROW($A$3:$A$13)- MIN(ROW($A$3:$A$13))+1,""), ROW()-2)),"")

can anybody modify this so it does a partial text search

=IFERROR(INDEX($B$3:$B$13, SMALL(IF(ISNUMBER(SEARCH($D$2,$A$3:$A$13)), ROW($B$3:$B$13)-2,""), ROW()-2)),"")

Res ID Date Feedback Res ID Date

18354576 7/17/2019 All good 18354576 7/17/2019

19165239 7/30/2019 All good 19165239 7/30/2019

18354576 7/31/2019 More Than 5 Error 18975236 7/31/2019

19167323 7/31/2019 All good 19167323 7/31/2019

18354576 7/31/2019 Less than 5 Errors 18785226 7/31/2019

19161337 7/31/2019 All good 19161337 7/31/2019

19153708 7/30/2019 Less than 5 Errors 19153708 7/30/2019

19162077 7/31/2019 All good 19162077 7/31/2019

19164149 7/31/2019 All good 19164149 7/31/2019

19164647 7/31/2019 Less than 5 Errors 19164647 7/31/2019

19166599 7/31/2019 All good 19166599 7/31/2019

19161016 7/31/2019 All good 19161016 7/31/2019

Hi

There is multiple entries in res ID and I want vlookup data same as it is in sheet, I mean res id 18354576 having 3 or more entries with different comment and different date.

How can I find exact comment with particular date for all entries.

Hi Svetlana,

When I applied the same example (as a training) in a blank sheet, it didn't work for me. After I read the comments I download the example file, I made a double click on a cell contains the formula to check it, I say ok, then press enter , as surprise I found the word displayed disappear, I don't know really why this is happening. I tried Online Excel to check maybe the excel version I had, has an issue or something.

Please Help

Thanks,

I forgot to say that a note showed up to me when I activate it , it said "inconsistent formula"

Ok the problem solved (CTRL+SHIFT+ENTER not just enter)

thanks

Thank you so much for this! This is exactly the help I needed. Also, I made a change to Formula 2 to make it a little more generic. Changed 'n' to row(first_formula_cell - 1).

Sl.No ROLL NUMBER Student Name Application Id Course /Course Year Rtf Amount

1 14G01A0101 A AJAY 201704689507 BTECH (CIVIL)/4 8750

2 14G01A0101 A AJAY 201704689507 BTECH (CIVIL)/4 8750

3 14G01A0101 A AJAY 201704689507 BTECH (CIVIL)/4 8750

4 14G01A0101 A AJAY 201704689507 BTECH (CIVIL)/4 8750

5 15G01A0201 A AKHILA 201705900172 BTECH (EEE)/3 8750

6 15G01A0201 A AKHILA 201705900172 BTECH (EEE)/3 8750

7 15G01A0201 A AKHILA 201705900172 BTECH (EEE)/3 8750

8 15G01A0201 A AKHILA 201705900172 BTECH (EEE)/3 8750

9 15242-EC-001 A AMRINBANU 201708033297 DIP (ECE)/3 7450

10 15242-EC-001 A AMRINBANU 201708033297 DIP (ECE)/3 3725

11 15242-EC-001 A AMRINBANU 201708033297 DIP (ECE)/3 3725

12 17G01A0401 A B JANANI 201708256249 BTECH (ECE)/1 8750

13 17G01A0401 A B JANANI 201708256249 BTECH (ECE)/1 8750

14 17G01A0401 A B JANANI 201708256249 BTECH (ECE)/1 8750

15 17G01A0401 A B JANANI 201708256249 BTECH (ECE)/1 8750

16 17G01A0301 A B RAJENDRAN 201708197050 BTECH (MECHANICAL)/1 8750

17 17G01A0301 A B RAJENDRAN 201708197050 BTECH (MECHANICAL)/1 8750

18 17G01A0301 A B RAJENDRAN 201708197050 BTECH (MECHANICAL)/1 8750

19 17G01A0301 A B RAJENDRAN 201708197050 BTECH (MECHANICAL)/1 8750

20 16G01A0401 A CHANDANA 201709098907 BTECH (ECE)/2 8750

21 16G01A0401 A CHANDANA 201709098907 BTECH (ECE)/2 26250

22 16242-EE-002 A DILLI BABU 201709114468 DIP (EEE)/2 3725

23 16242-EE-002 A DILLI BABU 201709114468 DIP (EEE)/2 3725

24 16242-EE-002 A DILLI BABU 201709114468 DIP (EEE)/2 3725

25 16242-EE-002 A DILLI BABU 201709114468 DIP (EEE)/2 3725

I prepared this data based on the v look up fromula .now i need name wise total in from four coloumns in one cell in a separate row. To know the individual value. can you help for this

When I replicate this example sometimes it works, sometimes its blank, and I cant figure out why. Any ideas on why the result sometimes comes out blank? No errors or anything just blank.

I'm doing this formula (the second one way up at the top)

=IFERROR(INDEX($B$3:$B$13,SMALL(IF(D$2=$A$3:$A$13,ROW($A$3:$A$13)- MIN(ROW($A$3:$A$13))+1,""), ROW()-2)),"")

What if the results are to return as a , (or ;) separated list in 1 cell?

need to vlookup in accordance with date association. Any idea? Trying to put a week or months worth of data together and write a code to return value associated with a number on each date.

Hi,

I have used Formula 3 with returns in a column.

My dashboard has 3 look up values. The formula only gave 1 result instead of 3 or more.

Please help.

Here's my formula:

=IFERROR(INDEX(Listings!$E$2:$E$1000,SMALL(IF(1=((--($B$4=Listings!$A$2:$A$1000))*(--($C$4=Listings!$B$2:$B$1000))*(--($D$4=Listings!$C$2:$C$1000))),ROW(Listings!$E$2:$E$1000)-1,""),ROW()-3)),"")

Thank you.

Hi,

This has been solved. haha.

I only need to copy the formula to all rows in that specific column.

Thanks

Hi,

I need something similar to the above but I have been going round in circles to find the right formula to use.

I have text in column A2 and I have a list of key words that I'm trying to find in column A2. I'd like the formula to return all of those key words that are contained in column A2. They don't have to be all in the same cell but I just need all of the values returned. Any help would be appreciated!

THanks

=IFERROR(INDEX($B$3:$B$13,SMALL(IF(D$2=$A$3:$A$13,ROW($A$3:$A$13)- MIN(ROW($A$3:$A$13))+1,""), ROW()-2)),"")

Hello, I have something very similar. Let's say you have multiple columns of Seller (Seller1, Seller2, Seller3, Seller4)(Move product to column E) Is there a way that $A$3:$A$13 can automatically change to B, C, or D based off of inputting Seller 1, 2, 3, or 4?

Hello Dan,

Thank you for your comment.

We have just replied to you via email. Please provide us with the requested information and we’ll do our best to help. Thank you.

Hi Dan,

The only working solution I can think of is nesting or concatenating different IF functions. For example:

=IF($H$2=$B$2, IFERROR(...), IF($H$2=$C$2, IFERROR(...), ""))

or

=IF($H$2=$B$2, IFERROR(...), "") & IF($H$2=$C$2, IFERROR(...), "")

If someone has a better idea, please do post your solution here!

OP, I need some explanation regarding your formula ,[IFERROR(INDEX(return_range, SMALL(IF(lookup_value = lookup_range , ROW(lookup_range) - MIN(ROW(lookup_range ))+1,""), ROW() - n)),"")].

So, i copied your formula and succeeded. But my main problem is the formula start capture value from 5th row.

For example, i have value in cell A1:A30. The formula capture the value starting from A5:A30.

FYI, the value i captured is from another sheet.

Thank you OP

Salute to whoever created the Combine Rows feature. 8195 rows have been merged into 96 rows. Thanks!

Hi Svetlana,

Thank you for these wonderful Excel tools and techniques!

I am implementing Formula #2 from "Vlookup with multiple matches returned in rows". Since my data is in different rows and columns from your example, I had to adjust all my values. The only other significant difference was that my Lookup values are located on another Worksheet. The good news is I was able to get the formula to return a value in the first cell. Yea!

The bad news is when I copy down or across, I can't get any other cells to work. I triple checked my relative and absolute references to ensure they are correct and I have made sure that Ctrl-Shift-Enter has been applied to each and every cell containing the formulas. Here is an example of the formula that is working in the first cell:

=IFERROR(INDEX('EMPLOYEE CERTS'!$B$2:$B$12220, SMALL(IF($H2='EMPLOYEE CERTS'!$A$2:$A$12220, ROW('EMPLOYEE CERTS'!$A$2:$A$12220)-MIN(ROW('EMPLOYEE CERTS'!$A$2:$A$12220))+1, ""), COLUMN()-8)), "")

The Lookup data is found on a Worksheet named "EMPLOYEE CERTS"

Column H contains my lookup value (notice only the Column is Absolute, the Row is relative)

Do you see anything that looks incorrect?

Can I send you a file which will make this alot easier for you to see what's going on?

Thank you for your help,

Dave

Hi Dave,

Yes, please send a sample worksheet to our support address (support@ablebits.com) att: Svetlana. At first sight, the formula looks correct. I believe working with the source data directly will help to figure out the issue.

Page No ID Description unit Qty

S2/1/3 A Gypsum ceiling m2 ----

S2/1/3 D Bulkhead including paint finish, 150 mm high m -----

S2/1/3 E Bulkhead including paint finish, 150 mm high m -----

S2/1/3 F Bulkhead including paint finish, 200 mm high m -----I

I need formula for above blank

from following tabel. Table name is MasterBoQ.

Page No ID Description Qty

S2/1/2 C Veneer solid WD-06 56

S2/1/2 Metal trim

S2/1/2 D Golden bronze patina MT-01 9

S2/1/2 Glass panel including fixing, backing, glue, accessories,fittings, trims, etc. complete. The Contractor will allow for laying in pattern and combination of finishes where applicable

S2/1/2 E Clear glass GL-01 2

S2/1/2 F Fluted glass GL-03 7

S2/1/3 BILL NO. 1 - ONE BED SUITE, (CONT'D.)

S2/1/3 FINISHES, (Cont'd.)

S2/1/3 Ceilings

S2/1/3 Drg Ref: DD-ID-63-03 and Detail Drgs.Suspended ceilings including suspension system, cornices, recesses, accessories, recessed light areas, shadow gaps, fittings, paintings, special features, access panels, etc.complete

S2/1/3 A Gypsum ceiling - 99

S2/1/3 B Paint vinyl emulsion PT-01 79

S2/1/3 C Paint vinyl emulsion PT-02 20

I would like to provide you with important formulas on this topic

Alternative formulas for use in the first table:

METHOD 1:

=IFERROR(INDEX($B$1:$B$13;SMALL(IF(D$2=$A$3:$A$13;ROW($B$3:$B$13));ROW()-2));"")

METHOD 2:

=IFERROR(INDEX($B$3:$B$13;SMALL(IF(D$2=$A$3:$A$13;ROW($B$3:$B$13)-ROW($A$3)+1);ROWS(D$2:D2)));"")

USAGE WITH FORMULA "AGGREGATE": (for method 3 and 4)

METHOD 3:

=IFERROR(INDEX($J$2:$J$1000;AGGREGATE(15;6;(ROW($K$2:$K$1000)-ROW($K$2)+1)/($K$2:$K$1000=$N$21);ROWS(N$22:N22)));"")

METHOD 4:

=IFERROR(INDEX($B$3:$B$13;AGGREGATE(15;6;(ROW($B$3:$B$13)-ROW($A$3)+1)/($A$3:$A$13=D$2);ROWS(D$3:D3)));"")

IF YOU DON'T WANT TO USE THE "IFERROR" FUNCTION: (for method 5 and 6)

METHOD 5:

=IF(ROWS(D$3:D3)<=COUNTIF($A$3:$A$13;"="&D$2);INDEX($B$3:$B$13;SMALL(IF(D$2=$A$3:$A$13;ROW($B$3:$B$13)-ROW($A$3)+1);ROWS(D$3:D3)));"")

METHOD 6:

=IF(ROWS(D$3:D3)<=COUNTIF($A$3:$A$13;"="&D$2);INDEX($B$3:$B$13;SMALL(IF(D$2=$A$3:$A$13;ROW($B$3:$B$13)-ROW($A$3)+1);ROW()-2));"")

Type these formulas in cell D3 and than use "CTRL+SHIFT+ENTER"

GREETINGS FROM TURKEY!!!!

Cihan TUNCEL

Industrial Engineer

Offers Industrial Solution

I need help to get the pure unique value here, the A shouldnt be there!

=INDEX($C$3:$C$10,MATCH(0,COUNTIF($G$2:G2,$C$3:$C$10)+($B$3:$B$10$E$3),0))

Category Item Unique distinct list

1 A 1 A

2 A B

1 A D

1 A #N/A

1 B #N/A

1 B #N/A

1 D #N/A

2 C #N/A

Hi, your article is very helpful!!

I have just one question I would like to ask you.

I'm working on my data and curious that it is possible to change the lookup criteria from exact value (==) to something like less than ()

[FORMULAR]

=IFERROR(INDEX(LOT, SMALL(IF(1=((--($Q$6=COMPNAME)) * ( --($B$21<=RES))), ROW(LOT)-1,""), ROW()-23)),"")

[END OF FORMULAR]

When I enter the above formula, Excel managed to get the value, but it is wrong.

Can you help me on this issue?

Thanks a lot

Hello. I am trying to use this formula with wildcards on the lookup values so that partial matches are printed out. I am using the following formula:

=IFERROR(INDEX($C$3:$C$30, SMALL(IF(1=((--("*"&$E$3&"*"=$A$3:$A$30)) * (--("*"&$F$3&"*"=$B$3:$B$30))), ROW($C$3:$C$30)-2,""), ROW()-2)),"")

but don't get any matches. Please help!

I am facing the same problem, any solution?

Can we get a formula 2.5 for "Vlookup multiple matches and return results in multiple rows"? Is this possible

Hi Svetlana. First, thanks for these tutorials; I've learned a lot from them.

I've perfected the formula in Formula 3. Vlookup multiple matches based on multiple conditions, but I have a twist - how do I get the formula to work if there's only one criteria entered? That is, I want to use the same formula if possible, but have it return results even if only one of the requirements is entered (i.e.either seller or month, but not both)?

I have use same table and formula howecer it is not working properly. the result is "0"

i have tried many time but it not working

Hi, I am trying to lookup your first example to populate values under different names but it seems to be that it reduces the value it pulls when the formula is dragged to other columns

This is the formula I am using

=IFERROR(INDEX(Sheet3!$I$2:$I$1298,SMALL(IF(Sheet2!U$3=Sheet3!$H$2:$H$1298,ROW(Sheet3!$I$2:$I$1298)-2,""),ROW()-2)),"")

Please advise

Hi

Please help me to get the formula for the below scenario.

In sheet 1, A column have few name list like below

vijay

Murali

Nandha

karthi

In sheet 2,A column have few names with extra letter like below

Vijay_grp1

britto

Murali_kronos

Sundar

Karthi_abcd

So the question is, what is Vlookup formula to find the values from sheet 1 vs sheet 2.

I like to know the Vlookup formula to put it from sheet 1 to sheet 2 to find the same name in the sheet to. Please help me to solve it.

Hello Vijay,

You'll need th INDEX+MATCH combination for this task. Supposing that column A contains names, column with names with extra letter is column E and the lookup column is F, the formula would be:

=INDEX($F$1:$F$5, MATCH(A1, LEFT($E$1:$E$5, LEN(A1)), 0))

Please check out this article, I beloeve you'll find this information helpful

Hi! I was wondering if we could add the MODE function to this formula!?

So it would place the 4 most common values.

I just can't figure out how to add it :(

Would this be possible?

Thanks in advance for any help you can give me :)

Hello Luc!

For me to be able to help you better, please specify which formula you mean and describe the problem in more detail. Thank you.

Hello Alex! I thought I replied with the reply button but it seems like i didn't.

my response is the next comment! Sorry ;)

Thanks for your reply Alex!

I am using your first example "Formula 1".

=IFERROR(INDEX($B$3:$B$13, SMALL(IF(D$2=$A$3:$A$13, ROW($B$3:$B$13)-2,""), ROW()-2)),"")

I'll use your example except rather than seller names and product names,

its all percentages. And I have 5 columns with different percentages.

example:

LOOKUP TABLE

A B C D E

9% | 10% | 4% | 2% | 36%

9% | 10% | 4% | 2% | 36%

9% | 12% | 4% | 2% | 36%

9% | 12% | 4% | 2% | 36%

9% | 10% | 4% | 5% | 36%

9% | 10% | 4% | 5% | 36%

9% | 10% | 4% | 5% | 36%

9% | 10% | 4% | 6% | 36%

9% | 10% | 4% | 6% | 36%

Let's say I need to know the most common "%" values WITH the 9% from column A

in the D Column. The most common to the least common.

RESULT

2% |

5% |

6% |

The only problem is I have more than 2000 rows

I am using a formula to get the most common values in column A

=MODE(IF(1-ISNUMBER(MATCH($A$2:$A$2000,$K$1:K2,0)),$A$2:$A$2000))

But from there, I have no idea how to extract the most common to least common

values in column D with the 9% value in Column A

I hope this is clear enough for you? :/

Thanks for the help Alex!

Thank you for the clarification, Luc.

Please select the vertical range of 5-10 cells so that there are all the repeating values there. Then enter the following formula right in the formula bar:

=MODE.MULT((D1:D2000) * ((A1:A2000)=0.09))

Since this is an array formula, hit CTRL+SHIFT+ENTER to apply it. You'll see all the most common values with 9% in column D. The rest of the cells will be filled with N/A.

I hope this will help, otherwise please do not hesitate to contact me anytime.

Hi Alex!

I've extracted only the 9% so it would be easier to sort the D column.

I tried your formula and strangely its giving me the most common value(4%) but denying the rest. Lets say the 0% would be the 2nd but instead its 4% for all of them.

I have noticed when the 4% and the 0% had the same amount of occurrence, the result was 1st(4%) 2nd(0%) and #N/A for the rest.

Not sure whats going on. Do you have any idea what I'm missing??

Thanks for the formula and your help!

Hi Luc!

Please note if there is the same number of values (for example, 0 and 4), the first in the list will be the one which is closer to the top of the column. Besides, the range where you insert an array formula should be big enough and contain a sufficient number of empty cells. Otherwise, the data won’t be shown in full. I have double-checked in my table and haven’t found any error. If my advice doesn’t help, please send a sample table of your data (no less 40 rows) along with the description of the expected result to support@ablebits.com. I will try to help you better.

This problem is difficult to explain, but I will try. I’m creating a database to keep track of stock option spread trades that have numerous legs. Some of the trades are simple and only having two legs, with one position being long, and the other being short. But, some of the trades have 8-10 legs, and this creates a problem in calculating the value of each leg. For this example, lets open a credit spread trade and go long an option, and go short an option. This is trade number 500 and each leg is recorded on a separate row. I want to invest $1000 per leg, so we need to take the ABS difference of the two fill prices and divide it $1000. This will tell us the quantity purchased. Eventually, we’ll close the short leg, and open a new short leg. And each time, we need to calculate a quantity by performing the above calculation with the long leg. This cycle of closing the short leg and opening a new short leg can continue until the long leg is finally closed, which ends the trade. So, let’s say we have 8-legs in this trade, the first row contains the long leg, and the 7 rows below contain the short legs. Each row has an ID of 500 to identify all 8-rows as trade number 500. Each time a short leg is closed, and a new short leg is opened, we need to scan the table for ID 500, then scan those rows to find the long leg. We then need to go over 5 columns to locate the long leg fill price to use in the new quantity calculation. Hope all that makes sense! Thanks, Jeff

Hello Jeff!

I am not a stock specialist. Therefore, it is difficult for me to understand the algorithm of your work. Could you send us a small sample workbook with the source data and expected result to support@ablebits.com? Please shorten your tables to 20-30 rows/columns and include the link to your blog comment.

We'll look into your task and try to help.

Hi there, I'm wondering if you might be able to help.=IFERROR(INDEX($B$3:$B$13,SMALL(IF(D$2=$A$3:$A$13,ROW($A$3:$A$13)- MIN(ROW($A$3:$A$13))+1,""), ROW()-2)),"")

I have found this vlookup article and your "How to get a list of unique and distinct values" extremely helpful. What I am trying to do now is understand how I can blend them together.

I am trying to use the Formula 1 :

As it is if there were two lines that listed Adam and Lemons the result column would list lemons twice.

I am thinking adding in a match function is the way to go but so far I've been unsuccessful in figuring out where to inset it.

Thanks in advance for your assistance and thanks again for providing such great content.

Patrick

Amazing tip, I've used this multiple times now!

Every so often I do appear to stumble with a strange error, when applying this formula (adjusted to the specific data table and return spreadsheet) sometimes the formula appears to "skip" 1 or 2 lines of data.

(using this example to explain the error:)

ADAM sells bananas, oranges and lemons -> sometimes my formula would only show Bananas, empty line and lemons.

Any idea why?

Hello Bruno!

I’m sorry but your task is not entirely clear to me. For me to be able to help you better, please describe your task in more detail. Please specify what you were trying to find, what formula you used. Give an example of the source data and the expected result.

It’ll help me understand it better and find a solution for you. Thank you.

I feel like I could use someting similar for my dataset, but cannot figure out how to adopt it. My data are like this:

Coutry1 Country2 Country3 Country4

Italy France

Germany

Sorry, sent before finishing, the full question is:

I feel like I could use something similar for my dataset, but cannot figure out how to adopt it. My data are like this:

Coutry1 Country2 Country3 Country4

Italy France USA

Germany

USA Singapore China Nigeria

USA France

Each row is a project - some of them are one-country and some are international collaborations with differing number of countries. I would need to find and count which other countries each country has collaborated with (and ideally also how many times).

In this example the desired end output would be:

USA: Italy(1)France(2)Singapore(1) China(1) Nigeria(1)

France: Italy(1), USA(2) and so on.

Any help would be appreciated!

make copy of heading "contry 1,2,3,4" on same sheet or another or other excel then use =countif(country 1 full column, first row enter

This is a solution involving vba.

1. Name the range where the countries, and only the countries, are located as "Countries"( as global name). Do not include headings.

2. Press ALT+F11 to open the VBA editor

3. In the menu choose Insert ->Module

4. Paste this code in the resultant window

Public Function CollabCountries(strCountry As String) As String

Dim rCountries As Range, cell As Range, dCollab As Object, _

tmp As String, rRow As Range, IsInRow As Boolean, k, _

result As String

Set rCountries = ThisWorkbook.Names("Countries").RefersToRange

Set dCollab = CreateObject("scripting.dictionary")

For Each rRow In rCountries.Rows

IsInRow = False

For Each cell In rRow.Cells

If cell.Value = strCountry Then IsInRow = True: Exit For

Next cell

If IsInRow Then

For Each cell In rRow.Cells

If cell.Value strCountry And _

Len(cell.Value) > 0 Then _

dCollab(cell.Value) = dCollab(cell.Value) + 1

Next cell

End If

Next rRow

For Each k In dCollab.keys

If dCollab(k) > 0 Then

result = result & k & "(" & dCollab(k) & ")"

End If

Next k

CollabCountries = result

End Function

5. Next to each cell in a range containing th countries names , put the formula CollabCountries(cellAdress). For instance, if the the name USA is in cell B9,cell C9 will contain CollabCountries(B9), showing the countries collaborating with USA in common proyects. Then drag to the rest of countries.

That was in response to Linda´s comment(# 56),above

I did a mistake locating my message

Forgot to say:

6. Save the excel workbook with the .xslm extension

The above code is not showing correctly in the line where appears the following:

If cell.Value strCountry And _

Len(cell.Value) > 0 Then _

it should be:

If Not (cell.Value = strCountry) And _

Len(cell.Value) > 0 Then _

I guess the message viewer has some issues with the vba not equal operator

Pardon me, is xlsm extension

Good day,

I am facing difficulty on the following: I have table with sales values and customer names (one column each). On the sales column, there are values that are duplicate, and they belong to different customers. I am trying to pinpoint the top 10 of those and lookup the top 10 customers, but i get the same customer name, when it comes to duplicate values.

Can you please assist me on this matter?

Thank you

One simple way :

1. In the Costumer column apply Advanced Filter,check "Copy to another Location", check "Unique records Only".

2. In a column next to the new Unique Customer column, put a column with a heading like Sum_of_sales,in the cells below, the formula: =SUMIF(costumer_range,J2,sales_range). Drag to the rest of Sum_of_sales column.

3. Apply Autofilter to the resulting range(consisting of the 2 new columns), then filter the sum_of_sales column,sorting it in descending order.

Hello!

Your question is too complicated and it will not work to explain it on the blog.

Could you please describe your task in more detail and send us a small sample workbook with the source data and expected result to support@ablebits.com? Please shorten your tables to 10-20 rows/columns and include the link to your blog comment.

We'll look into your task and try to help.

Hello, I don't have further questions. I just want to thank you for putting this together. I learnt heaps! Emma

Hi there I'd like to find

I have a list of sizes S,M,L and widths S=50 M =100 and L=200

I would like to test my width is greater than sizes and return options available for example

width of 30 would return S,M,L

width of 90 would return M,L

width of 190 would return M,L

width larger would return "message"

Hi,

I am using Formula 2, and it is great!!!

How would I be able to search multiple worksheets for D3?

Thank you for the wonderful work that you do!

George

Hello!

Read how to VLOOKUP across multiple sheets in Excel here.

Once entered the value in a cell in Excel , how to protect that particular row i.e) for ex once entered the value in a cell F4, how to protect that the row No.4 or protect A4 : F4

Hello!

Please ask questions in the appropriate section. Check out the tutorial on locking cells in Excel. Ask a question there if you don't find the answer.

Thank you so much. I had been trying to figure this out and I think I can do it now.

Hi,

Thanks for the formula, it has been really useful! I have a slightly complicated question related to it:

I have an class attendance spreadsheet that is linked to another master workbook (of all students in the university). I have used formula 2 from above to get the names based on their class and year. So far the formula works great and does what I need it to.

I then have an area where I mark down Y/N depending on the students' attendance. This works well too. My issue arises when a new student is added to the master spreadsheet. This addition is alphabetical. The formula therefore places the new student alphabetically in the list in my class attendance sheet. This means that if I have already marked a student present, but a new student joins the class late, the attendance will not necessarily reflect the correct student.

Is there a way to ensure the students attendance stays linked to the students name?

If needed I can send through my sheets to make it clearer!

Thanks!

Hello!

I don't know which of the many formulas above you used. But to solve the problem of adding new students, I recommend converting your original data to an Excel table or using a dynamic named range.

If you describe in detail the initial data, the desired result and your formula, I will try to give you a more accurate advice.

I used the formulas mentioned on a second worksheet (to pull data from the first). The results step down a row with each new column. I didn't see anyone else with the same problem in the comments. Did I miss a step? I would like to send you my spreadsheet or screen share to show you the problem.

Also, if i increase n by 1 for each new column it produces the correct result. Trying to understand why this works and find a solution to prevent having to change this value in multiple cells.

Hello!

Send us a small sample workbook with the source data and expected result to support@ablebits.com. Please shorten your tables to 10-20 rows/columns and include the link to your blog comment.

We'll look into your task and try to help.

I'm getting #NUM error on the first compact formular (I took out the IFERROR statment to find out why it wasn't working):-

=IFERROR(INDEX($B$3:$B$13, SMALL(IF(D$2=$A$3:$A$13, ROW($B$3:$B$13)-2,""), ROW()-2)),"")

- any ideas on what the problem could be please?

Hello!

Unfortunately, without seeing your data it is difficult to give you any advice. I think the expression ROW()-2 gives a result less than 1.

Check out the #NUM! error.

how can i add AND formula inside this

IFERROR(INDEX(return_range, SMALL(IF(lookup_value = lookup_range , ROW(lookup_range) - MIN(ROW(lookup_range ))+1,""), ROW() - n)),"")

like example

C1 | F | D

A1| B | C

A2 | D | D

B1 | E | C

A3 | B |E

A4 | B | D

IFERROR(INDEX(A1:A6, SMALL(IF(AND(B = B1:B6,D= C1:C6), ROW(A1:A6)- MIN(ROW(A1:A6 ))+1,""), ROW() - n)),"")

the result will show the A1| B | C

that D = C1:C6 will take the data from the 1st line, not same line with B=B1:B6

how can i solve it?

Hello!

If I understand your task correctly, the following formula should work for you:

{=IFERROR(INDEX($A$1:$A$6,SMALL(IF(IF(($B$1:$B$6="B")*($C$1:$C$6="D")=1,TRUE),ROW($A$1:$A$6)- MIN(ROW($A$1:$A$6))+1,""), ROW()-1)),"")}

This is the formula for cell E2.

The AND and OR operators do not work with arrays.

Hi,

Thanks for the clear explanation of these formula's!

I got only a thing I trying to add to the formula, but I'm can't get it how I want it.

If, for example, Adam sells two times banana's and one time apple, Banana's will stand two times in the data.

And the result of the formula will be:

Adam l Banana l Apple l Banana l

But the result I want is that the duplicates are not showed.

I know it is possible to remove duplicates with the remove duplicated button, but I was wondering if it's also possible within the formula.

Hi, I am trying to do a formula like this in a spreadsheet where I utilize multiple tabs to look up some info and yield multiple results in consecutive rows that fir the criteria. The primary tab that these lookup formulas would be in is called the Vacation Chart tab. in cell A8 of that tab would be a dropdown menu consisting of departments. Cell B8 would also be a dropdown menu conisting of shift codes. There are 3 columns I would be working with on this tab that I would want to yield multiple info from the lookups to other tabs: A11:A42 would list every employee number that matches the criteria in A8 and B8. B11:B42 would list names.

The employee numbers can be found in a tab called New bid in A5 and onward, Names can be found in B5 and onward in the same tab. Additionally, The Dept codes assigned to those employees are contained in G5 and onward and the shift codes are in I5 and onward.

So I want to be able to find for example a list of all the employees who are in a dept code called C61C with a shift code of 3.

How could I do this?

Hello!

The examples in this article explain how to VLOOKUP multiple values with one or more criteria. Please check them out.

Hi, how can I get/find the number in column B from the given set of numbers in column A .

Below is the small example of the numbers. Thank you.

A B

223 301

224 304

304 307

310 310

311 320

312

317

318

320

321

Hello!

Here is the article that may be helpful to you: Excel INDEX MATCH function.

I hope I answered your question. If something is still unclear, please feel free to ask.

this formula doesn't work. it show nothing. please help me. below is my data.

DATA

Date Time

01/06/2021 8:01:39

01/06/2021 12:30:42

01/06/2021 13:29:47

01/06/2021 22:00:49

02/06/2021 8:00:24

02/06/2021 12:31:21

02/06/2021 13:29:59

02/06/2021 19:30:22

03/06/2021 8:01:40

03/06/2021 12:30:07

03/06/2021 13:30:52

03/06/2021 19:30:24

04/06/2021 7:56:08

04/06/2021 12:30:04

04/06/2021 13:30:13

04/06/2021 19:30:25

08/06/2021 7:54:51

08/06/2021 13:28:26

08/06/2021 19:30:12

09/06/2021 7:52:59

09/06/2021 12:34:43

09/06/2021 13:26:36

09/06/2021 17:28:22

09/06/2021 20:00:38

11/06/2021 7:58:38

11/06/2021 13:28:32

11/06/2021 19:30:41

12/06/2021 7:55:55

12/06/2021 12:31:20

12/06/2021 13:28:27

12/06/2021 19:30:38

OUTPUT

DATE TIME1 TIME2 TIME3 TIME4 TIME5 TIME6

01/06/2021 8:30:55 12:32:18 13:53:43 21:31:36

02/06/2021 7:30:53 12:31:48 13:42:48 13:17:01 19:31:50 20:32:07

03/06/2021 7:31:51 12:30:27 13:40:55 20:32:33

04/06/2021 7:30:04 12:31:22 13:23:16 20:30:25

05/06/2021

06/06/2021

07/06/2021

08/06/2021 7:22:48 12:43:30 13:28:04 20:30:55

09/06/2021 7:43:20 12:30:02 13:40:55 20:31:15

10/06/2021 7:37:03 12:33:04 14:33:19 20:30:21

11/06/2021 7:41:30 12:35:39 14:13:33 20:31:19

12/06/2021 7:30:08 12:31:05 13:15:40 20:01:44

Hi!

I’m sorry but your task is not entirely clear to me. Could you please describe it in more detail? What result do you want to get? What formula are you talking about?

Hi, Thanks for helping and passing on your knowledge. Based on the example given on the page it relates to a problem I am trying to resolve in Excel. Could you explain the calculation that would create a list of all the sellers that had sold Bananas as an example, many thanks for any assistance.

Hi Chris,

The formula is very similar to the one discussed in the first example. You just swap the lookup and return ranges:

=IFERROR(INDEX($A$3:$A$13,SMALL(IF(D$2=$B$3:$B$13,ROW($B$3:$B$13)- MIN(ROW($B$3:$B$13))+1,""), ROW()-2)),"")

Where:

D$2 - lookup value (bananas)

$A$3:$A$13 - return range (seller names)

$B$3:$B$13 - lookup range (products)

Hi, I am looking for a formula that can calculate YTD numbers for sumifs. Im trying to use offset and sumifs together but seems to be not functioning. Sumif its apple and clean and YTD to May only

Jan,Feb,Mar,Apr,May, Jun, Jul

Apple Clean

Banana Clean

Apple Rotten

Apple Clean

YTD May for Apple & Clean: ____________

Please help. looks like there is no formula for the above scenario

Hi!

I am not sure I fully understand what you mean.

There are no numbers in your example. What do you want to calculate? What formula are you using? What are the problems?

Hi, upon using the following formula,

=INDEX(Sheet2!$B$4:$B$11,SMALL(IF(1=((--(Summary!$A$2=Sheet2!$C$4:$C$11))*(--(Summary!$B$1=Sheet2!$D$4:$DA$11))),ROW(Sheet2!$B$4:$B$11)-3," "),ROW(Summary!$B$2)-1))

a #VALUE error appears, it seems to be related to the logical_test argument of the IF function. It can't seem to process lookup_value=lookup_range, can someone help with this please.

Hello!

It is very difficult to understand a formula that contains unique references to your workbook worksheets. Hence, I cannot check its work, sorry.

Here is the article that may be helpful to you: How to evaluate and debug formulas in Excel

I hope it’ll be helpful.

Is this applied for Excel 2007?

I applied the formula, only the first data came out, not multiple data.

Thank you.

Hi Kaede,

Did you press Ctrl + Shift + Enter to complete the formula? In Excel 2007 - 2019, it only works as an array formula.