by 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!
How to do multiple Vlookup in Excel using a formula
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.
Formula 1. Vlookup multiple matches and return results in a column
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 the UNIQUE function in Excel 365 or a more complex formula to extract distinct values in older versions.
- 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 drag the formula down to a few more cells (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
=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)),"")As you see, the 1st formula is a bit more compact, but the 2nd 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:
How this formula works
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 3rd, 8th and 10th 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 1st 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).
Note. Please notice the proper use of absolute and relative cell references in the formula. All references are fixed except for the relative column reference in the lookup value (D$2), which should change based on a relative position of a column(s) where the formula is copied to return matches for other lookup values.
Putting all this together, we get the following generic formulas to Vlookup multiple values in Excel:
Formula 1:
Formula 2:
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.
Note. In the above example, both n and m are equal to "2" because our return range and formula range both begin in row 3. In your worksheets, these may be different numbers.
Formula 2. Vlookup multiple matches and return results in a row
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 5th column, n is equal to "4" (5-1=4).
Note. For the formula to get copied correctly to other rows, mind the lookup value references, absolute column and relative row, like $D3.
Wrapping up, here are the generic formulas for Vlookup with multiple results returned in rows:
Formula 1:
Formula 2:
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.
Formula 3. Vlookup multiple matches based on multiple conditions
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.
Return multiple matches in a column
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:
Return multiple results in a row
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:
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.
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.
How these formulas work
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:
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.
If you need to get matches from several sheets, use this guide: How to VLOOKUP across multiple sheets.
How to Vlookup to return multiple values in one cell
I will be upfront - I don't know an easy way to lookup and return multiple matches in a single sell with formulas. 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. The detailed steps follow below.
Source data and expected result
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.
Pull rows with multiple matches to the main table
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.
Combine duplicates rows into one row
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 :)
- 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.
- 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.
Available downloads
Vlookup Multiple Values - examples (.xlsx file)
Ultimate Suite - trial version (.exe file)
342 comments
Hi there I'd like to find
I have a list of sizes S,M,L and widths S=50 M =100 and L=200
I would like to test my width is greater than sizes and return options available for example
width of 30 would return S,M,L
width of 90 would return M,L
width of 190 would return M,L
width larger would return "message"
Hello, I don't have further questions. I just want to thank you for putting this together. I learnt heaps! Emma
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.
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
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
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
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.
Hi there, I'm wondering if you might be able to help.
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 :
=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)),"")
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
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.
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.
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 ;)
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 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
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 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)?
Can we get a formula 2.5 for "Vlookup multiple matches and return results in multiple rows"? Is this possible
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?
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
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
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