When using Microsoft Excel for data analysis, you may often find yourself in situations when you need to get all matches based on 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. Continue reading
Comments on: How to Vlookup multiple values in Excel with criteria
by Svetlana Cheusheva, updated on
Comments page 2. Total comments: 342
Hi,
First, thank you for sharing!
Can you please provide a solution to the case below:
I have 3 columns (A: Containing reference information, B: Containing information to be searched, C: Search result)
Column A Column B Column C
File Name Name File Name, Document Name, Element Name, Name
Element ID Element Element ID, Element Name, Changed Element
Column Location
Document Name
Element Name
Name
Category
Warranty
Slope
Changed Element
What I need is to search in column A for any partial match with cell B2 (Name) or B3 (Element) and get the result in one cell,
Thank you, Behzad
Hello!
To extract from column A the value that contains string from cell B2, use the INDEX MATCH formula:
=INDEX(A1:A10, MATCH(TRUE,ISNUMBER(SEARCH(B2,A1:A10)), 0))
Hope this is what you need.
Hello!
Firstly, I appreciate your effort in trying to help everyone who comments.
I was hoping you might be able to help write a formula to look for a designated value/name across a table, and where it is found (often on multiple rows), add together all associated number(s) in the adjacent cell(s), and display the product on a separate table.
For example, I have a table which lists recorded fuel costs for cars. Column A has car reference which may repeat for multiple entries ie car1, car2, car1, car3, car1. Column B has the cost associated with the entry. In this example, I referenced car1 having 3 separate cost incidents. I then want to make a table which combines costs for all cars ie in column A, car1 through car3, and in column B the total cost associated with each car.
I had been using Vlookup, but of course it only returns the first value for car1 in the above example and ignores the other 2 entries for car1.
Hopefully that makes sense! Much appreciated.
Adam.
Hello!
To create a table with the costs for all cars, I recommend using a pivot table. Using formulas, you can calculate the costs for each car using the SUMIFS function. Use this guide: Excel SUMIFS and SUMIF with multiple criteria – formula examples.
I hope I answered your question. If something is still unclear, please feel free to ask.
It is great read.
However, i have one need wherein I am checking two sheets (or you can call it basically two tables) wherein, one has more data than the other it has around 10Mn records and is bit inconsistent records. For example to present you a bit of complexity, I need help on how this can be approached. Below is a example table data.
Sheet B
item item# type method value
a 1 dog exp 1
a 1 cat exp 1
b 2 mat exp 1.1
b 2 dat std 1
b 2 fat exp 1
b 2 cat exp 0.8
b 2 dog exp 1.1
c 3 dat std 1
c 3 fat exp 1
d 4 fat exp 1.25
d 4 cat exp 1.1
d 4 dog exp 0.9
f 6 sat exp 1
Sheet A - This is master data
item item # type method value
a 1 dog exp 1.1
a 1 cat exp 1
a 1 bat exp 1
a 1 rat std 1.1
b 2 rat std 1
b 2 bat exp 1
b 2 mat exp 1
b 2 dat std 1.2
b 2 fat exp 0.8
b 2 cat exp 1.1
b 2 dog exp 1
b 2 yak exp 1
b 2 track std 1
b 2 owl exp 1
b 2 ram exp 1.25
b 2 sat exp 1
c 3 mat exp 0.9
c 3 dat std 1
c 3 fat exp 1
c 3 cat exp 0.83
d 4 fat exp 1.1
d 4 cat exp 1
d 4 dog exp 1
e 5 mat exp 1.09
f 6 sat exp 1.1
g 7 rat std 0.9
g 7 owl exp 0.83
g 7 ram exp 1
g 7 sat exp 1
What i see is if we do IFERROR and VLOOKUP and MATCH on the INDEX, we get the difference, but as we know this is row by row matching is not possible in this case, how can i take the delta of the above tables.
Thank you Alexander, is there another way apart from the ready-made free solution. The reason why i propose to have this is because business needs the solution as POC and we will be implementing this same on data model in sagemaker or other alike AI tool.
TA
Hello!
We have a ready-made solution for your task. Compare Tables for Excel searches for duplicates and unique values in any two tables. It is available as a part of our Ultimate Suite for Excel that you can install in a trial mode and check how it works for free.
Purchased items in bulk at different prices with different quantity on different dates.
Selling those items in retail.
Purchase sheet and sales sheets are different.
Both Sheets contain Item Code, Qty, Date and Rate
Want a solution to get purchase rate in front of sale until stock of that purchase ends.
Hi!
Your task does not have a detailed description and cannot be solved with a single formula. If you have a specific question about the operation of a function or formula, I will try to answer it.
Hi, i have a question; i have 2 coloumns
A B
a x
b y
c z
a, b z, x
c, a y, z
How can i get results as;
a x z, x y, z
b y z, x
c z y, z
??
Pls help
Hi!
I don't think your problem can be solved by one formula. You can write a formula for each row using this guide: Combine text strings, cells and columns in Excel. You can extract a part of a string from a cell using substring functions.
Actually it's like
=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)), "")
formula but i have to specify IF($D3=$A$3:$A$13 section as "look in coloumn A, whichever cell contains spesific text this is the reference one, continue formula" i cannot solve this
Hi!
What do you want to calculate exactly? Your question is not entirely clear, please specify.
Thank you for your concern, i am sorry for my english. İt is not fluent...
I want to see
A______B______
a______x,y____
b______z______
a______z, y____
c______x,z_____
b,a____x,y_____
c,b____x,y,z___
a,b,c__x,y,z___
as a result of:
D________E________F_________G________H
a________x,y______z,y________x,y______x,y,z
b________z________x,y________x,y,z____x,y,z
c________x,z______x,y,z_______x,y,z________
i cannot seperate A and B coloumn cells with =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)), "") formula. It is static referenced.
I told false, sorry.
This input :
A______B______
a______x,y____
b______z______
a______z, y____
c______x,z_____
b,a____x,y_____
c,b____x,y,z___
a,b,c__x,y,z___
should transform to this output:
D________E________F_________G________H
a________x,y______z,y________x,y______x,y,z
b________z________x,y________x,y,z____x,y,z
c________x,z______x,y,z_______x,y,z________
Thank you very much for your answer. I will try this.
Hi!
In C1, write the letter "a". Select the range D1:H1 and enter the array formula in the formula bar:
=IFERROR(INDEX($B$1:$B$10, SMALL(IF(ISNUMBER(SEARCH(C1,$A$1:$A$10)), ROW($A$1:$A$10)-0,""), COLUMN()-2)),"")
The answer to your question can be found in this article abowe.
Read it carefully.
Thank you very much, i willl read them.
Hi, I want to Check something 1000 of Restricated Words From 1 line of Discription(200-300)Alphabet .Is there's any Formula to do that Please help Me in this. Thank you in advance
Hello!
If you want to know if at least one word from the list occurs in a text, use the formula:
=SUM(IFERROR(SEARCH(B1:B1000,A1),0))>0
If you want to specify which words are found, try to enter the following formula in cell С1 and then copy it down along the column:
=ISNUMBER(SEARCH(B1,$A$1))
You can learn more about SEARCH function in Excel in this article on our blog.
Hello thank you for the insights!
Btw, i'd like to ask about how to apply the array formula automatically througout certain range of cells?
It would be easier for me rather than dragging the formula all the way thank you.
I'm trying to work on a formula to look up the start date and rank of an employee in table 1 and then look up table 2 and if their start date is less than 12 months return a % based on their rank but if their start date is more than 12 months ago return a different % based on their rank.
Table 1 has start date, rack and % as column headers
Table 2 Has Ranking A B C D as headers, Rows are 'Before 9/02/2021' and 'after 09/02/2021'. The columns are filled in with various percentages in both rows.
I would like to look up the start date and rank in table 1 then look up table 2 and if the date on table 1 is 'before 9/02/2021' return the percentage from table 2 that matches the rank in the row 'before 09/02/2021' . If the date on table 1 is after 09/02/2021 return the percentage from table 2 that matches the rank in the row 'after 09/02/2021'
The date in the rows on table 2 are changing on a daily basis. As table 2 rows refer to either in the last 12 months or prior to 12 months.
I've tried a vlookup and match formulas but can't seem to get it to work. Any suggestions or better way to do this?
Hello!
This is a complex solution that cannot be found with a single formula. If you have a specific question about the operation of a function or formula, I will try to answer it.
Please have a look at this article - IF VLOOKUP in Excel: Vlookup formula with If condition.
Can I use this across the wordsheet?
I mean the database in one sheet, and the result in other sheets.
I try but it did not work.
Hello!
Here is the article that may be helpful to you: VLOOKUP across multiple sheets in Excel.
I have sets of data in 3 column and I need both values on column B & C to appear as an aswer when the answer is transposed:
APPLE ABC 123
APPLE BCD 231
APPLE CDE 321
ideally the answer should be:
APPLE ABC 123 BCD 231 CDE 321
is this possible so far my formula is this but I am only getting answer as: APPLE ABC BCD CDE
=INDEX($B$2:$B$3650,SMALL(IF($F2=$A$2:$A$3650,ROW($A$2:$A$3650)-ROW($A$2)+1),COLUMN(A1)))
Thank you
Hello!
To join multiple values into a string of text, use the TEXTJOIN function
=TEXTJOIN(" ",TRUE,IFERROR(INDEX($B$2:$C$13, SMALL(IF(F$2=$A$2:$A$13, ROW($A$2:$A$13)-1,""), ROW($A$2:$A$13)-2),{1,2}),""))
The formula works in any cell of 3 rows.
Hi Alexander,
Thank you for looking into my query. Is it possible to get Location and Quantity below to appear in separate columns? With the original formula I could only extract location horizontally but I needed to find a way that the formula would return 2 values each time it finds unique reference is in column A and the answers I would like to get are in columns B and C and returning every instance horizontally in separate columns each time. Is this possible?
Produce Location Quantity
APPLE BOX123 50
ORANGE BOX124 70
APPLE BOX125 50
LEMON BOX125 60
ORANGE BOX123 60
APPLE BOX124 50
Answer for example:
Produce Location Quantity Location Quantity Location Quantity
APPLE BOX123 50 BOX125 50 BOX124 50
ORANGE BOX124 70 BOX123 60
LEMON BOX125 60
Thank you very much.
Hello!
Use a formula and get the result in the cell. Then replace the formula with its values using Paste Special.
Split strings in the cells by separators, as described in this guide.
I hope it’ll be helpful.
Have you You Tube Channel?
Hello!
Here is the link to our YouTube Channel.
Hi, thanks for this helpful post.
but i have a problem.
when my data is more than ~~3000, the formula dont work & shows empty cell.
can you help my please?
Hi!
Your question is not very clear. What formula are you using? What does 3000 mean?
I have a list of sales interactions by client, with several interaction dates on separate rows for each client name. I've successfully used Formula 2 to populate the interaction dates in a row from newest to oldest for each client. However, I would like to add rows to my interaction list for new interactions as they occur. This expands the range beyond what is currently in the formula (for instance, if my range is $D$2:$D$100, and I add a date for a new interaction into cell D101, it doesn't get captured). Is there a way to make the range of cells automatically update to include the newly added rows? Thanks!
Hello!
I recommend using a dynamic named range. Please check out this article on our blog.
Also, you can convert your data to Excel table and use structured references.
Dynamic named range and Excel spreadsheet will automatically grow when new data is added.
This information helped automate a process that has been plaguing my brain for the last 4 months. Thank you!
Hi,
I am using the same formula on the same dataset as you have it's just that the first table is in one file and the second one is in another file and it doesn't pick up. Please help.
Thank you.
Result: in file 1
Seller Month Product
Adam Jan-21 Lemons
Apricots
Oranges
Oranges
Actual Data: in file 2
Seller Month Product
Robert Mar-21 Bananas
Adam Jan-21 Oranges
Adam Jan-21 Lemons
Sally Jan-21 Apricots
Adam Jan-21 Apples
Sally Mar-21 Oranges
Adam Jan-21 Bananas
Adam Jan-21 Oranges
I tried this using exactly the data in the example in Excel for Mac 16.51. It does not work correctly. In the test IF(D$2=$A$3:$A$13, ROW($B$3:$B$13)-2,"") the formula ROW($A$3:$A$13) produces the same result regardless of the value of D2.
Maybe this is another instance of Excel for Mac and Excel for Windows being divergent.
Hello
I have the same issue with a regular PC version, MS Office Professional Plus 2016.
Any suggestion?
Hello!
I cannot say anything about Excel for Mac - did not have a chance to test the formulas there.
In my Excel 365, all the formulas work fine, exactly as described in this tutorial.
Tibor, you can downlaod our sample worsheet to check the behavior in your Excel.
This is almost exactly what I have been looking for. Is there a way to modify these formulas to work with a horizontal array instead of a vertical one?
Thanks
Hello!
Here is the article that may be helpful to you: Excel HLOOKUP function with formula examples.
You can also use the XLOOKUP function.
Thanks, I'll give those a try
I should add that this is the formula I would like to modify but have not been successful so far. =IFERROR(INDEX($B$3:$B$13, SMALL(IF(D$2=$A$3:$A$13, ROW($B$3:
$B$13)-2,""), ROW()-2)),"")
Thanks
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.
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.
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, 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, 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 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,
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.
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.
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.
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.
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 having the same issue the results step down a row.
Did you figured out the issue?
P.S. I like so much your very detailed posts for most issues i ever had
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.
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.
Thank you so much. I had been trying to figure this out and I think I can do it now.
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.
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.
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
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.
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.
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