Very nice work! I have been trying to customize this formula for my needs but this stuff is slightly over my head! I have a setup almost identical to this, but i only need to look up 1 value, but it is partial text so i am trying to use SEARCH. I have a column of "part numbers" and a column of "descriptions" I want to search "descriptions" for a partial string of text "COS", then return the "part number" AND the contents of the "description" cell containing the partial text.. Any ideas?
hey.. I need help... I need a formula that extracts "46" from "46;"...
I need the ";" gone and return me back the 46... I can get the formula for two values but i need to work even for "345;" or "1234;" as the value I input are random n will only get up to thousand's.Example : 46; 125; and 1456; no matter how many value there are i want them to return only the numbers...
Can anybody help me with this useful feature, I need to link data from two separate tables, but I can get it working with and function and row, they are no friends. Any suggestions ?
I have 111,222,333,444 in my sheet 1, column A. In sheet 2, column A has aaa111zzz,bbb222zzz,ccc333zzz,ddd444zzz,aaa111zzzff. And sheet 2, column B has 100,200,300,400 and 500 respectively. How do i lookup 111 inside sheet 2 column A and return multiple values? Thanks!
Excellent lesson! Would you use a similar approach to consolidate data from multiple weeks on a single summary worksheet? In other words, lets say you added an "All" option to the Week drop down and wanted to see all Wins or Losses across all weeks on a single worksheet? I've tried to do this with Indirect/Offset/Choose (I feel like a dunce!) by resetting the =IF(ROWS(B$2:B2).. portion of the formula when the bottom of each week is reached but can't seem to make it work. Thanks again!
hi there!!! like i told you yesterday, i download the file EMT358-369.xls and i open it in excel 2003 but the formula doesnt work, in every result says: #¿NOMBRE?.... so i dont know what to do... only work under excel 2007. ¿is there any other way so this works for excel 2003????
@ExcelIsFun hi there!!! like i told you yesterday, i download the file EMT358-369.xls and i open it in excel 2003 but the formula doesnt work, in every result says: #¿NOMBRE?.... so i dont know what to do... only work under excel 2007. ¿is there any other way so this works for excel 2003????
Check for the counting formula in cell C3 - use that one instead of the COUNTIFS. You can send an e-mail to my excelisfun YouTibe account and then I will send e-mail so you can send workbook and then I will take a look.
@ExcelIsFun Hi!! Its me again... i've been working with this formula but know i would like to see in the final result a list that involves just one criteria, for example, if you select criteria#1: 11 and criteria #2: W you have as a result 2 variables, but if i want to see all the results that envolves just one criteria, for example, criteria#1: 11 and criteria #2: nothing... it has to show all the bet teams that have Week #11 no mather what result they have... how can i do this????
@ExcelIsFun ok, what happens is that the formula performs a search when you select two variables from the drop-down tables giving a result and that's fine. For example, in your table, when you select Criteria #1: Week 1 and Criteria #2: W your result is 3 teams.
But in my database I need to do a search of information simply by selecting one of the variables from the drop-down table and if I need more specific results, choose the second variable from the other drop-down table.
I do not know. Try posting your question to this site:
mrexcel[dot]com/forum
When you post at this site, be sure to say how your actual data is set up, what your question is and what results you would like to get. Do not reference this video in your post, just ask your own question - that way people who have not seen the video (99.99% of the people) can answer your question.
Be sure to send me the link so that i can follow along and learn too!
Thanks!!! Awesome!!!! this formula is so great... just a little issue, can we do this in office 2003 version??? because when i save the file in 97-2003 version, the formula doesnt work! :( can you help me?????
ok Thanks! i allready download the file, i'll try to use it in the 97-2003 version and i'll let you know.... ;)
i have this problem because when i finish doing the formula in office 2007 and i save it in 97-2003 format it says that some formulas where invalid in this version and they will not work :s
@ExcelIsFun ok Thanks! i allready download the file, i'll try to use it in the 97-2003 version and i'll let you know.... ;)
i have this problem because when i finish doing the formula in office 2007 and i save it in 97-2003 format it says that some formulas where invalid in this version and they will not work
Check for the counting formula in cell C3 - use that one instead of the COUNTIFS. You can send an e-mail to my excelisfun YouTibe account and then I will send e-mail so you can send workbook and then I will take a look.
@ExcelIsFun hi there!!! like i told you yesterday, i download the file EMT358-369.xls and i open it in excel 2003 but the formula doesnt work, in every result says: #¿NOMBRE?.... so i dont know what to do... only work under excel 2007. ¿is there any other way so this works for excel 2003????
This formula looks great, just one problem, my excel 2007 has trouble with data comparisons like "if('358'!$d$16:$d$39="w""part, this result of this comparison lead to #value. Do you have a clue why?
This formula looks great, just one problem, my excel 2007 has trouble with data comparisons like "if('358'!$d$16:$d$39="w""part, this result of this comparison lead to #value.
I love your videos.They are so useful and easy to understand. Can I use this Magic trick so that it returns multiple values from a table which I am constantly adding to? I want to be able to create a summary of items much like you have done, based on the name of the person who has received items, but I am constantly adding information into the table.No person will have more than a certain amount of items at any stage,but there are always people being added to the database who receive new items.
@ExcelIsFun . Awesome. This worked really well! Nice one. The only problem now is that some of my columns are returning in my Summary table have the dreaded #REF in them. I used individual Dynamic Ranges for all the columns in my database table. Any ideas? I am using Mac. Is that a factor?.
#REF! means it is looking at a cell reference that no longer exists - so it is probably a problem with the dynamic ranges. For example, if the dynamic range starts in A2 and you tell rnage to look 4 cells up, there is no A-2 cell.
WOW! This is HUGE. I followed this video and have applied it to my workbook and it is now saving me tons of time. Before I had to pull in my data (it was random) and then sort, copy, paste, etc...Now, I just paste it into my work page and Excel does the rest.
I have to say a huge THANK YOU for posting this video. This is awesome.
Hey thanks for posting this stuff it is great! I was wondering how you would go about returning items based on one lookup value that was not consistent. So for instance I have a list that has a variety of project numbers plus their extensions. so 123415.11.15, 123415.11.12,123415.23.35, etc and I want to return all rows which contain 123415 regardless of extension- what do i do?
Very nice video and very good channel. I was searching at Google about this and after 4 hours i thought to search youtube and i found everything i wanted at your channel. Thank you!
I love your videos and I do productivity spread sheets. I can't seem to get the "Countifs" formula right. I have a column of employees name and tons of data in other column about each one. The formula I need would be to count, say how many times Steve took between 60 and 90 minutes to complete a job. All times have been reduced to minutes, but I just can't to get the correct answer....could you help?
oops, I think I know what the problem was - should have been Indirect("'"&$B$1&"'!$C$16:$C$39"). The most obvious things are always the most difficult! (at least sometimes)
I am glad that you got it working! It is true what you say - the best way to learn is to practice a lot and it is obvious from your comments that you have practiced a lot - do are doing EXCELlent!!!!
The only success I had was changing the "Sumproduct" function in your file - it worked well with the "Indirect" function inside (this would not work well with the "countifs" function, as the calculated "Countifs" showed the incorrect number).
By the way - great video - as always. Watching your tutorials (and practicing) has taught me a LOT!
I have a question. How to do the same using the "Indirect" function for which the parameter would be WK number. Lets assume that I have sheets called 1, 2, 3, 4 ..., so if cell B2 is 1, then all the data should be taken from spreadsheet "1". So in the formula instead of the reference to range '358'!$C$16:$C$39, the range would be created dynamically. I tried Indirect("'"&B1&"'!$C$16:$C$39") but then the formula would not work (B1 is WK number).
Thank you! Trick 801 did exactly what I need!
greatyazer 1 month ago
@greatyazer , cool!!
ExcelIsFun 1 month ago
Very nice work! I have been trying to customize this formula for my needs but this stuff is slightly over my head! I have a setup almost identical to this, but i only need to look up 1 value, but it is partial text so i am trying to use SEARCH. I have a column of "part numbers" and a column of "descriptions" I want to search "descriptions" for a partial string of text "COS", then return the "part number" AND the contents of the "description" cell containing the partial text.. Any ideas?
greatyazer 1 month ago
@greatyazer , try these videos:
Excel 2010 Magic Trick 798: Partial Text Lookup Formula To Return Multiple E-mail Records
Excel 2007 or 2003 Magic Trick 801: Partial Text Lookup Formula To Return Multiple Records
Excel Magic Trick 802: Helper Column To Do Partial Text Lookup Formula To Return Multiple Records
Excel Magic Trick 323: Partial Text VLOOKUP (Fuzzy Match)
ExcelIsFun 1 month ago
I BLOODY LOVE YOU!!!
You have helped me so much doing all these Excel projects at work!
You're a life saver!
TheDoctor8007 2 months ago
@TheDoctor8007 , I am glad thet the video helps!
ExcelIsFun 2 months ago
hey.. I need help... I need a formula that extracts "46" from "46;"...
I need the ";" gone and return me back the 46... I can get the formula for two values but i need to work even for "345;" or "1234;" as the value I input are random n will only get up to thousand's.Example : 46; 125; and 1456; no matter how many value there are i want them to return only the numbers...
SOMEONE HELP ME THX!
Nauttos 3 months ago
@Nauttos , I do not know, try:
mrexcel [dot] com/forum
ExcelIsFun 3 months ago
Can anybody help me with this useful feature, I need to link data from two separate tables, but I can get it working with and function and row, they are no friends. Any suggestions ?
JarodLTify 5 months ago
@JarodLTify , post Q to:
mrexcel [dot] com/forum
ExcelIsFun 2 months ago
I have 111,222,333,444 in my sheet 1, column A. In sheet 2, column A has aaa111zzz,bbb222zzz,ccc333zzz,ddd444zzz,aaa111zzzff. And sheet 2, column B has 100,200,300,400 and 500 respectively. How do i lookup 111 inside sheet 2 column A and return multiple values? Thanks!
taikhint 5 months ago
@taikhint , post questions to :
mrexcel [dot] com/forum
ExcelIsFun 5 months ago
Here is a good question. How do you use negative numbers as criteria?
evilinjuries 6 months ago
Excellent lesson! Would you use a similar approach to consolidate data from multiple weeks on a single summary worksheet? In other words, lets say you added an "All" option to the Week drop down and wanted to see all Wins or Losses across all weeks on a single worksheet? I've tried to do this with Indirect/Offset/Choose (I feel like a dunce!) by resetting the =IF(ROWS(B$2:B2).. portion of the formula when the bottom of each week is reached but can't seem to make it work. Thanks again!
motoracerx34 7 months ago
@motoracerx34 , post Q to:
mrexcel [dot] com/forum
ExcelIsFun 2 months ago
very very nice video!!!
SongForRich 7 months ago
Is there a way to populate the output result into a dropdown list in a cell?
aprima 7 months ago
Maybe this video:
Excel Magic Trick 549: Dynamic Data Validation List Drop-Down OFFSET or Table Feature?
ExcelIsFun 7 months ago
This has been flagged as spam show
hi there!!! like i told you yesterday, i download the file EMT358-369.xls and i open it in excel 2003 but the formula doesnt work, in every result says: #¿NOMBRE?.... so i dont know what to do... only work under excel 2007. ¿is there any other way so this works for excel 2003????
ecastillo969 1 year ago
@ExcelIsFun hi there!!! like i told you yesterday, i download the file EMT358-369.xls and i open it in excel 2003 but the formula doesnt work, in every result says: #¿NOMBRE?.... so i dont know what to do... only work under excel 2007. ¿is there any other way so this works for excel 2003????
ecastillo969 1 year ago
Check for the counting formula in cell C3 - use that one instead of the COUNTIFS. You can send an e-mail to my excelisfun YouTibe account and then I will send e-mail so you can send workbook and then I will take a look.
ExcelIsFun 1 year ago
@ExcelIsFun thanks!!!!! it works!!!!!! im so happy!!! thank you so much for your help!!!!!! :)
ecastillo969 1 year ago
You are welcome!!
ExcelIsFun 1 year ago
@ExcelIsFun Hi!! Its me again... i've been working with this formula but know i would like to see in the final result a list that involves just one criteria, for example, if you select criteria#1: 11 and criteria #2: W you have as a result 2 variables, but if i want to see all the results that envolves just one criteria, for example, criteria#1: 11 and criteria #2: nothing... it has to show all the bet teams that have Week #11 no mather what result they have... how can i do this????
ecastillo969 1 year ago
I am not following what you are asking. See this video for information about more than one criteria:
Excel Magic Trick 703: Extract Records Multiple Criteria (AND OR logic) Filter, Formula, Adv. Filter
ExcelIsFun 1 year ago
@ExcelIsFun ok, what happens is that the formula performs a search when you select two variables from the drop-down tables giving a result and that's fine. For example, in your table, when you select Criteria #1: Week 1 and Criteria #2: W your result is 3 teams.
But in my database I need to do a search of information simply by selecting one of the variables from the drop-down table and if I need more specific results, choose the second variable from the other drop-down table.
ecastillo969 1 year ago
I do not know. Try posting your question to this site:
mrexcel[dot]com/forum
When you post at this site, be sure to say how your actual data is set up, what your question is and what results you would like to get. Do not reference this video in your post, just ask your own question - that way people who have not seen the video (99.99% of the people) can answer your question.
Be sure to send me the link so that i can follow along and learn too!
ExcelIsFun 1 year ago
Thanks!!! Awesome!!!! this formula is so great... just a little issue, can we do this in office 2003 version??? because when i save the file in 97-2003 version, the formula doesnt work! :( can you help me?????
ecastillo969 1 year ago
It should work in any version. Please download the workbook and check out the files I provide. Watch this video title to learn how to download:
excelisfun Search & Find Excel Videos, Playlists, Download Excel Workbooks
ExcelIsFun 1 year ago
ok Thanks! i allready download the file, i'll try to use it in the 97-2003 version and i'll let you know.... ;)
i have this problem because when i finish doing the formula in office 2007 and i save it in 97-2003 format it says that some formulas where invalid in this version and they will not work :s
ecastillo969 1 year ago
@ExcelIsFun ok Thanks! i allready download the file, i'll try to use it in the 97-2003 version and i'll let you know.... ;)
i have this problem because when i finish doing the formula in office 2007 and i save it in 97-2003 format it says that some formulas where invalid in this version and they will not work
ecastillo969 1 year ago
Check for the counting formula in cell C3 - use that one instead of the COUNTIFS. You can send an e-mail to my excelisfun YouTibe account and then I will send e-mail so you can send workbook and then I will take a look.
ExcelIsFun 1 year ago
@ExcelIsFun hi there!!! like i told you yesterday, i download the file EMT358-369.xls and i open it in excel 2003 but the formula doesnt work, in every result says: #¿NOMBRE?.... so i dont know what to do... only work under excel 2007. ¿is there any other way so this works for excel 2003????
ecastillo969 1 year ago
This formula looks great, just one problem, my excel 2007 has trouble with data comparisons like "if('358'!$d$16:$d$39="w""part, this result of this comparison lead to #value. Do you have a clue why?
xiaomianyang1956 1 year ago
I have no idea. Send workbook that you are having trouble to:
excelisfun[at]gmail[dot]com
ExcelIsFun 1 year ago
This formula looks great, just one problem, my excel 2007 has trouble with data comparisons like "if('358'!$d$16:$d$39="w""part, this result of this comparison lead to #value.
xiaomianyang1956 1 year ago
I love your videos.They are so useful and easy to understand. Can I use this Magic trick so that it returns multiple values from a table which I am constantly adding to? I want to be able to create a summary of items much like you have done, based on the name of the person who has received items, but I am constantly adding information into the table.No person will have more than a certain amount of items at any stage,but there are always people being added to the database who receive new items.
CJTCarter 1 year ago
try this video:
Excel Magic Trick 584: Dynamic Range for Periodic Data Dumps into Excel OFFSET & Defined Names
ExcelIsFun 1 year ago
@ExcelIsFun . Awesome. This worked really well! Nice one. The only problem now is that some of my columns are returning in my Summary table have the dreaded #REF in them. I used individual Dynamic Ranges for all the columns in my database table. Any ideas? I am using Mac. Is that a factor?.
CJTCarter 1 year ago
#REF! means it is looking at a cell reference that no longer exists - so it is probably a problem with the dynamic ranges. For example, if the dynamic range starts in A2 and you tell rnage to look 4 cells up, there is no A-2 cell.
I know nothing about a Mac.
ExcelIsFun 1 year ago
WOW! This is HUGE. I followed this video and have applied it to my workbook and it is now saving me tons of time. Before I had to pull in my data (it was random) and then sort, copy, paste, etc...Now, I just paste it into my work page and Excel does the rest.
I have to say a huge THANK YOU for posting this video. This is awesome.
Croz1007 2 years ago
You are welcome!!
It is amazing what Excel can do!
Search for this "playlist":
Excel Extract Records From Database Table / List
This playlist has many videos on this topic.
ExcelIsFun 2 years ago
I can get the first value to appear, but when I drag the formula down the value does not change.
misslady1908 2 years ago
How can I do this with named ranges from a table? Thanks!
misslady1908 2 years ago
For the first argument in the INDEX function, you would have to use Mixed Cell References in your named range.
ExcelIsFun 2 years ago
Comment removed
maxdr21 2 years ago
excellent video; learned a lot and enjoyed it; thanks
sljee 2 years ago
I am glad that it was useful!
ExcelIsFun 2 years ago
Hey thanks for posting this stuff it is great! I was wondering how you would go about returning items based on one lookup value that was not consistent. So for instance I have a list that has a variety of project numbers plus their extensions. so 123415.11.15, 123415.11.12,123415.23.35, etc and I want to return all rows which contain 123415 regardless of extension- what do i do?
bstratm 2 years ago
There are many ways to do this. One way:
1) If extensions in column A, use a formula like this:
=LEFT(TRIM(A2),6)
2) copy formula down (this extracts the first 6 characters - TRIM is in case there are leading spaces)
3) Sort on column with formula
4) Copy Paste.
You could also Filter, then copy and paste
You could also do Advanced Filter
You could also do a nasty array formula (but only if the data set changed a lot).
ExcelIsFun 2 years ago
You can send me an e-mail to:
excelisfun[at]gmail[dot]com
and I can send an example.
ExcelIsFun 2 years ago
Very nice video and very good channel. I was searching at Google about this and after 4 hours i thought to search youtube and i found everything i wanted at your channel. Thank you!
fougas0 2 years ago
You are welcome!
ExcelIsFun 2 years ago
Very interesting. I was looking for this solution. thank you
exceluserintobago 2 years ago
You are welcome!
ExcelIsFun 2 years ago
I love your videos and I do productivity spread sheets. I can't seem to get the "Countifs" formula right. I have a column of employees name and tons of data in other column about each one. The formula I need would be to count, say how many times Steve took between 60 and 90 minutes to complete a job. All times have been reduced to minutes, but I just can't to get the correct answer....could you help?
OldestOzzyFan 2 years ago
Your formula would have to have 2 criteria:
Range equal Steve
Range greater than 60
Range less than 90
ExcelIsFun 2 years ago
oops, I think I know what the problem was - should have been Indirect("'"&$B$1&"'!$C$16:$C$39"). The most obvious things are always the most difficult! (at least sometimes)
mach82xl 2 years ago
I am glad that you got it working! It is true what you say - the best way to learn is to practice a lot and it is obvious from your comments that you have practiced a lot - do are doing EXCELlent!!!!
ExcelIsFun 2 years ago
the comment at the bottom is the first one - I couldn't create a comment with more than 500 letters :)
mach82xl 2 years ago
The only success I had was changing the "Sumproduct" function in your file - it worked well with the "Indirect" function inside (this would not work well with the "countifs" function, as the calculated "Countifs" showed the incorrect number).
By the way - great video - as always. Watching your tutorials (and practicing) has taught me a LOT!
mach82xl 2 years ago
I have a question. How to do the same using the "Indirect" function for which the parameter would be WK number. Lets assume that I have sheets called 1, 2, 3, 4 ..., so if cell B2 is 1, then all the data should be taken from spreadsheet "1". So in the formula instead of the reference to range '358'!$C$16:$C$39, the range would be created dynamically. I tried Indirect("'"&B1&"'!$C$16:$C$39") but then the formula would not work (B1 is WK number).
mach82xl 2 years ago