For more videos that cover lookup concepts see the playlist at kzitem.info/door/PL-n8f1cY_Qw95JHWjCjPofsCd7hCWjI8l
@DougHExcel
4 жыл бұрын
...
@McNICKS49
8 жыл бұрын
Exceptional Doug! I've tried so many variations of this formula from other websites, but none of them have worked. This is going to save my coworkers and I countless hours. Thank you!
@DougHExcel
8 жыл бұрын
Thanks for sharing your experience; glad this is going to help your team out!
@marcreisberg2716
4 жыл бұрын
Thank you Doug. This is exactly what I am looking for! I greatly appreciate the time you took to record and post this.
@DougHExcel
4 жыл бұрын
You’re welcome!
@lucasbusard564
5 жыл бұрын
Great Job! You helped me. I quite enjoyed your calming voice, especially considering I'd been fighting this formula for 2 hours prior. Feeling mighty frustrated!
@DougHExcel
5 жыл бұрын
Hi Lucas Busard, thanks for the kind words!
@KumarAnalytic
4 жыл бұрын
*You're great sir, awesome explained. I'm from India but your English is little bit confusing me but following your instructions it helps me to understand.*
@DougHExcel
4 жыл бұрын
Thanks S Kumar, glad it helped!
@bencacace5491
8 жыл бұрын
Doug, Thanks for the description of the functions and how they interact with each other. This was very helpful. Good job!
@gaylecox8868
8 жыл бұрын
Thanks Doug! You have an excellent way of teaching "tricky" excel formulas with wonderful clear explanations. I learnt so much from this short video! Thank you for sharing your knowledge, it's appreciated :)
@gaylecox8868
8 жыл бұрын
+Doug H Just to let you know I've already used the formula and it worked perfectly :) So glad I found your video!
@DougHExcel
4 жыл бұрын
Thanks gayle cox, glad it helped!
@alexandrosmavratsas4562
10 жыл бұрын
Thank you Doug. Very clear explanation for a very useful function.
@maarten4132
3 жыл бұрын
Thank you very mush... Struggled first, because my data was not in the same columns and rows... But now it's working well... Thanks a lot....
@DougHExcel
3 жыл бұрын
You're welcome!
@apk204
7 жыл бұрын
You are a spreadsheet God!! Utterly amazing!! Thank you
@DougHExcel
7 жыл бұрын
You're welcome....thanks for the comment!
@azonicin
6 жыл бұрын
Thanks a ton Doug H for such a wonder formula. It worked for our requirement. Due to this formula you have saved many work days and saved the data computation and other associated resources too.
@DougHExcel
6 жыл бұрын
Thanks Azonic Infotech, glad it helped!
@Jack7thst1
9 жыл бұрын
Fantastic, Doug! Thanks very much.
@joshuamcguire4832
7 жыл бұрын
Exactly what I was looking for. Great stuff man. Looking forward to many more videos from your channel. Thanks
@DougHExcel
7 жыл бұрын
Hi Joshua McGuire, glad you liked it, thanks for commenting!
@wongeubing
7 жыл бұрын
Thanks for this. Helped with my work tremendously. Good explanations on each part of the formula, which often is lacking in many tutorials.
@DougHExcel
7 жыл бұрын
Hi Bing, glad you liked it, thanks for commenting!
@charleso2345
7 жыл бұрын
Thanks Doug. This was really good. Is there a way to display the scores vertically instead of horizontally? My project would work better that way. Thanks in advance.
@DougHExcel
7 жыл бұрын
The easiest way to do this is to copy and paste transpose. This video has the different paste options kzitem.info/news/bejne/0m6q4ICmbqOWe6Q. The transpose section starts around the 17 minute mark.
@navudivina
5 жыл бұрын
Thanks. Helped a lot. Appreciated.
@DougHExcel
5 жыл бұрын
Thanks Juvou Ni Nabo, glad it helped!
@robertmartucci9594
5 жыл бұрын
Awesome Video. Is there a way to make the Lookup Value drag down Vertically for the same value instead of Horizontal? ie: switching Names with "Score 1,2,3....."
@DougHExcel
5 жыл бұрын
power query might help...see kzitem.infosearch?view_as=subscriber&query=power+query+lookup
@ajay13178
3 жыл бұрын
Thank you very much. You solve my too many problems
@DougHExcel
3 жыл бұрын
Glad to hear that!
@reankour764
6 жыл бұрын
THANK YOU VERY MUCH FOR THIS SHARING, THIS IS WHAT I FIND FOR DECENT OF YEAR.
@DougHExcel
6 жыл бұрын
Hi Siheang...you're welcome, glad you liked!
@Adnegaify
5 жыл бұрын
Just wanted to let you know that this helped me a lot! Thank you and have a super day!
@DougHExcel
5 жыл бұрын
Thanks Seth Fyrus, glad it helped!
@lancevermilion7313
9 жыл бұрын
Very nicely done!
@davidandselena
9 жыл бұрын
Thanks. Very useful
@amirkalim9196
8 жыл бұрын
Very helpful! Thank you so much for sharing this. I appreciate the efforts that guys like you put in and share the knowledge. ThumbsUp.
@DougHExcel
8 жыл бұрын
Thanks for you kind words!
@poomdizzle
8 жыл бұрын
Hi Doug, 1. are we able to select the entire column by selecting the column header so that if we add data, the table would automatically account for new entires? 2. Are we able to return multiple values, but unique as in resulting in a unique list of values? Thanks!
@cfapalakkad3153
7 жыл бұрын
Thanks... its very useful for our day to day works...
@DougHExcel
7 жыл бұрын
Glad it helped; thanks for commenting!
@kjaan
9 жыл бұрын
Good explanation but what if you have multiple items like your example but have to list in one column rather than 3 different column? I am trying to extract data from large file that match ABC company with its contribution.
@sudhakarpr2107
8 жыл бұрын
+KJAAN : Hey hi have u achieved it.. let me know what technique used
@MrUpdaters
6 жыл бұрын
Here you go: {=INDEX($A$2:$B$10,SMALL(IF($A$2:$A$10=$D$5,ROW($A$2:$A$10)-1),ROWS($E$5:E7)),2)}
@ailafaeabrigana432
6 жыл бұрын
@@MrUpdaters hi, what is e5:37 in your formula?
@megganlandis1549
8 жыл бұрын
Hi Doug! Thanks for this. It was very helpful. Do you have any examples out there that will return values vertically instead of horizontally? I am working on a project I am struggling with. Thank you!
@laimlofgreen7266
7 жыл бұрын
Thanks for the info, Doug! I got it to work except I have to manually type in my item numbers on the data table. My data is from a spreadsheet generated by an ERP system and it can't find the item number unless I manually type in the exact same item number into the data. Is there a way to make this formula read a value pasted cell?
@laimlofgreen7266
7 жыл бұрын
Nevermind! I figured it out!
@DougHExcel
4 жыл бұрын
Hi Laim Lofgreen, thanks for the comment!
@Raf-lp9rh
8 жыл бұрын
Hi Doug, Nice if you could include the formula to do copy and paste on our sheet, but it is great help!. Thanks much!
@jchew-pr5je
10 жыл бұрын
This is so helpful! I have a question - can a wildcard character be used in this formula to search for a portion of text in column A? To use your example, if the name "Ike" were in E4, how could the formula be altered so that it returned cells B5, B6, B7? Not a useful application in this table, but asking hypothetically - how can the formula find instances where the *string* of text in column E (for example, "*E2*") appears in column A? Many thanks!
@saravanakumark9860
4 жыл бұрын
Hi, This is awesome one. Can you please help me to get formula to show only unique values.
@DougHExcel
4 жыл бұрын
some of these may give insights kzitem.infosearch?query=unique+values
@saravanakumark9860
4 жыл бұрын
I need same formula to show only Unique values. The formula shown in video gives all values even its duplicated. Can you help me with it
@evpack
7 жыл бұрын
Thank you. Worked a treat.
@DougHExcel
7 жыл бұрын
Thanks for the comment!
@Invincible46DR
8 жыл бұрын
This saved me today..... Thanks Doug
@DougHExcel
4 жыл бұрын
Hi Damodar Raju, you're welcome!
@saeed19317
5 жыл бұрын
The explanation was very clear and I managed to do what i needed. thank you very much.
@DougHExcel
5 жыл бұрын
You’re welcome!
@xmxiang
8 жыл бұрын
THanks man, really helpful ... this just saved me lots of manual labor
@DougHExcel
4 жыл бұрын
Thanks Xi Michael Xiang, glad it helped!
@manuelfraustro984
9 жыл бұрын
Hi +Doug H, I hope you are doing fine. Can this be done in Excel 2013 as well? I have been trying the keyboard sequence without success. Thanks in advance!
@technico686
5 жыл бұрын
Thank you very much, This is the one that i've been looking for!
@DougHExcel
5 жыл бұрын
Thanks NhutTruong Nguyen, glad it helped!
@JoshuaWilsonNZ
6 жыл бұрын
Thanks Doug :) worked in my test environment. Now to apply in mass :)
@DougHExcel
6 жыл бұрын
Hi Joshua Wilson...you're welcome, glad you liked!
@SquatingCow
8 жыл бұрын
Hi! This is a great video. Is it possible to do this with multiple criteria rather than one lookup value?
@DougHExcel
8 жыл бұрын
Give the DGET function a try kzitem.info/news/bejne/kWOAm2qDsnykm4Y
@juliandr92
8 жыл бұрын
any idea on how to manage really big files with this formula, so you can save it quicker and excel dont freeze that much?
@lambaah1
10 жыл бұрын
Hi Doug, This is pretty cool. However, I have a further doubt. What if i wanted to lookup from right to left. i.e. Names were in column B and scores in column A. Rest of it remains the same.. How would the formula change?
@chloeng7169
9 жыл бұрын
Thanks so much its very clear!
@manasmohapatra7847
8 жыл бұрын
THanks a lot. This was quite helpful
@DougHExcel
4 жыл бұрын
Thanks Manas Mohapatra, glad it helped!
@petarmilutin7044
6 жыл бұрын
Very helpful if you need number as a return value, what if you need text?
@DougHExcel
6 жыл бұрын
it'll do it too!
@brogers108
8 жыл бұрын
Thanks for the video, is there a way return the sum/average of all the scores without dragging the formula to display each individual score.
@brogers108
8 жыл бұрын
+Doug H Thanks I figured how to accomplish what I needed using the sumif function.
@DougHExcel
4 жыл бұрын
Hi Brandon Rogers, thanks for the comment!
@Theozzie11
10 жыл бұрын
What part of the formula do I need to change in order to get the information going down instead of across?
@akshaymandora9213
5 жыл бұрын
dude super helpful
@DougHExcel
5 жыл бұрын
Thanks Akshay Mandora, glad it helped!
@shailesh0092
11 жыл бұрын
It was really very helpful............... thanks waiting for some more new ideas like this..:)
@fariborz1361
5 жыл бұрын
Thank you for the video, what if I want to present average of Mike's Scores?
@DougHExcel
5 жыл бұрын
a couple ways...the following won't give the full solution but offer some insights... you can concatenate the functions together - kzitem.info/news/bejne/kmtm3JeLhWihqXY perform the lookup with another aggregate function - kzitem.info/news/bejne/u3yLyYCIp55zrI4
@jawwad4020
8 жыл бұрын
This turned out to be sooooo useful for me! Thanx a lot bro :-) , keep uploading such awesome videos and helping people like me ;-)
@DougHExcel
8 жыл бұрын
Glad you found this useful...thanks for the comment!
@222libertarian
10 жыл бұрын
Will this work when the data-values are located on a multiple-tabbed workbook, and returned to a main tab for printout?
@222libertarian
10 жыл бұрын
***** Thanks. I've got an Invoice (main tab) that I want to return multiple items (listed on another tab) using a persons name as the search item. Rather than the final 'COLUMNS' function in your array (that yields a ROW of values), how would I return the values in a column? Simply switch that term to ROW?
@thomasginisty9571
8 жыл бұрын
Hi! Thanks for posting this, it's of great help! Is there a way to use this formula for partial match too? I've tried using using Wildcards in the IF statement but it doesn't work. Thanks!
@DougHExcel
8 жыл бұрын
You can actually perform wildcard searching in the FIND feature. This video provides some insights kzitem.info/news/bejne/za-n3npsg2RppaA
@AT-hc1zd
7 жыл бұрын
Your video is very helpful. Thanks very much!!
@DougHExcel
7 жыл бұрын
Thanks A T, glad it helped!
@kartikraga
4 жыл бұрын
Hello Doug, When I use this Index look up for large look up values and arrays, its take so much time. Is it possible to make this automatic using VBA macro, so that the time will reduce.
@DougHExcel
4 жыл бұрын
i'm not good at VBA, but this can be done with Power Query kzitem.info/news/bejne/xa6a2mZre4ZmoHY
@mikkojonzuu3019
8 жыл бұрын
Thanks, great help and direct solution to my working file
@DougHExcel
4 жыл бұрын
Thanks Mikko JONZUu, glad it helped!
@CaptRoverman
7 жыл бұрын
Hi, love the video, it has helped me heaps :) I just have a quick question regarding the '=VLOOKUP' function. If we go off your example, If there are 2 or more people with the same amount of returns (ie: both or more have 3 returns each), the '=VLOOKUP' function in the 'display' tab gives me a '#NA' result. is there anyway of preventing and getting it to show something? any help will be greatly appreciated
@DougHExcel
7 жыл бұрын
If you don't want to see those pesky #N/As, you can wrap the VLOOKUP in one of the is/if error functions. See kzitem.info/news/bejne/qY5s26CncJ1_m2k kzitem.info/news/bejne/zpCcp2WAZoKAg5w
@CaptRoverman
7 жыл бұрын
thanks for those videos, they were interesting to watch and will help with other projects but lm still having a little bad luck with my spread sheet. the sheet is a footy tipping comp, at the end of each weekend we update the tips from that weekend and then the total sum auto adjusts to the correct total but what l am looking for is the vlookup function but if just say place one is on 10 tips but then second place is shared between 3 people all who have 8 tips it only accpets one persons score and the others go into error mode
@acousticalan
5 жыл бұрын
very well explained. thank you so much
@DougHExcel
5 жыл бұрын
You're Welcome!
@carlpolk3554
5 жыл бұрын
Great find.. question how do about the formula being case insensitive
@DougHExcel
5 жыл бұрын
maybe these videos will give insight kzitem.infosearch?query=case+sensitive
@neelalam
5 жыл бұрын
how to find out how many instances may have in big database, here u have shown for three as easy to count. Thanks.
@DougHExcel
5 жыл бұрын
with large DB try Power Query kzitem.infosearch?view_as=subscriber&query=lookup+power+query
@y2raza
8 жыл бұрын
Doug fantastic tutorial; however if there were two instances of Mike=73 how can I extract the unique value, that is get 73 only once and not twice? Your help is much appreciated.
@DougHExcel
8 жыл бұрын
You may want to remove duplicates first (if possible) kzitem.info/news/bejne/l5ehqoqCn4SijKQ and then use the lookup on the updated table.
@y2raza
8 жыл бұрын
Thank You
@AlamdarAli
6 жыл бұрын
excellent explanation, thanks
@DougHExcel
6 жыл бұрын
Hi Alamdar Ali, glad you liked it, thanks for commenting!
@ailafaeabrigana432
6 жыл бұрын
Hi! Thanks for this but can all the value just be printed in one column instead of one value per column? Given that I know how many values I'm looking for
@DougHExcel
6 жыл бұрын
how about something like this kzitem.info/news/bejne/12-hnnelg4Neo2U
@TheJeldij
7 жыл бұрын
Hi Doug, very clear thanks a lot! After viewing your video, I've got the following question: In your video it's clear that the maximum of scores is three. So you've only created three columns in your Excelsheet. But as you mentioned: sometimes you have thousands of rows in your array. How do you make sure that the function reports all values (scores)? Is it possible to report all the values in one single cell, separated by comma's or another separator? Thanks again!
@DougHExcel
7 жыл бұрын
With large datasets Power Query would be a good option kzitem.info/news/bejne/mGdo04pvo6l-f6Q
@Lisa-yx3ze
7 жыл бұрын
exactly what i need!! thanks!
@DougHExcel
7 жыл бұрын
Hi Lisa Liu, glad you liked it, thanks for commenting!
@alicekalani2628
9 жыл бұрын
Very useful, but I'm trying to return multiple values from a data validation lookup. For example, Cell E2 would contain a drop down with "Mike, Alice, Chris" and the scores would populate accordingly. It seems to search all in list as opposed to the chosen item in the drop down. Can you assist?
@wasimakram522
9 жыл бұрын
+Miss Esther Kim it's easy to do
@sakan_arm
9 жыл бұрын
Thank you so much really helpful
@sakan_arm
9 жыл бұрын
:-)
@ashwindurga
11 жыл бұрын
Great video. Explains it really well. However, I am facing an issue since I dont have numbers. At time of 4:57, you get SMALL (FALSE, FALSE, FALSE,4,5,6). Instead I get SMALL (1,1,1,1,2,3,4,1,1) - The first 3 values should be false and the last 2 should also be false. Not sure why it is throwing up 1 instead of false. I am finally getting a #REF error.
@FrikkieBossert
8 жыл бұрын
Thanks saved my bacon :) - appreciate the help
@simoneroman546
9 жыл бұрын
Getting a circular error on formula, how do you reference the cell F2 in the formula when you are in it?
@DarkHorse2305
7 жыл бұрын
Thank you so much, you explained it really well :)
@DougHExcel
7 жыл бұрын
Thanks for the comment!
@waseemabuassi713
7 жыл бұрын
amazing video, it deserves our attention. please help me if I can do it by columns not by row .... thanks
@DougHExcel
4 жыл бұрын
try inpivot to turn it into rows kzitem.infosearch?query=unpivot and then doe the multiple lookup
@ahmadilman1583
8 жыл бұрын
Thanks boss :) really help
@DougHExcel
4 жыл бұрын
Thanks Ahmad Ilman, glad it helped!
@raycarilimdiliman5003
8 жыл бұрын
Thank you!
@DougHExcel
4 жыл бұрын
Hi Ray Carilimdiliman, you're welcome!
@lesterzhang1689
8 жыл бұрын
thanks, it is very useful.
@DougHExcel
4 жыл бұрын
Hi Lester Zhang, you're welcome!
@juliandr92
8 жыл бұрын
thanks man your rock!
@Dronedaddy22
11 жыл бұрын
Is there a way to return the values into a single cell and have them separated by commas?
@wai-yeechoy9366
9 жыл бұрын
hi does this work on a mac laptop using open office as it keeps coming up with err:508 thx
@saravanakumark9860
4 жыл бұрын
I need same formula to show only Unique values. The formula shown in video gives all values even its duplicated. Can you help me with it
@DougHExcel
4 жыл бұрын
maybe one of these will help kzitem.infosearch?view_as=subscriber&query=unique+values
@QQ-nb2ic
4 жыл бұрын
How can you do a vlookup with word wrap? So that you can search for one item and it brings up all items within same cell word wrapped?
@DougHExcel
4 жыл бұрын
maybe a keyword lookup could help kzitem.infosearch?query=key+word
@chazgarrett2312
9 жыл бұрын
I have a large spreadsheet, over 9000 lines. I cannot get this to work. It works on one page for one smaller set of data, but when i do it on the larger it doesnt work. Is there something Im missing w the index/match function? Does it need to be alphabetical do blanks cause problems?
@colorina416
9 жыл бұрын
+Doug H This video was wonderful thank you for posting, very helpful! +Chaz Garrett I WOULD LOVE TO KNOW THE ANSWER TO THIS AS WELL =)
@iankarremans9397
8 жыл бұрын
What would you do if the names and scores table wasn't filtered from A-Z and you wanted to sum all the scores for one person? I am building a attendance calendar and each person will be documented for which day they have off. Example - in the wednesday column Bob is off 2 Wednesdays in a row and in the cell next to it, it will show how many hours and there will be a calculating table on another page.
@iankarremans9397
8 жыл бұрын
I already have it show how many times Bob has been off for which ever month you are on with a "countif" Bob appears on this table. Now I just need the same thing but with hours
@DougHExcel
8 жыл бұрын
Have you tried to put the range into a pivot table and sum the hours?
@iankarremans9397
8 жыл бұрын
no I havent tried that, I am not to familiar with pivot tables
@anjalisharma-lq5ho
6 жыл бұрын
thanq so much sir. it helped me alot...
@DougHExcel
6 жыл бұрын
Thanks anjali sharma, glad it helped!
@shau78
8 жыл бұрын
Thanks for this video Doug. But I have a minor challenge. I understand how to lookup multiple items, and how to get number 1 for the first row like you have done in your video. But I have come across formulas like this one:=INDEX($C$4:$E$17,LARGE(IF($C$4:$C$17="Jamie",ROW($D$4:$D$17)),ROW(A1))-3,3). In this formula, the author managed to get the correct answer although serial number for the first row is 4. I am confused. Please help
@DougHExcel
4 жыл бұрын
Hi Shau_fx, sorry 🙁....but try a post on the mrexcel.com forum!
@harisurrehman4476
8 жыл бұрын
Thanks Doug. I tried to use the same formula but getting "#VALUE!" error. Even i tried the same example you used in you tutorial. can you help me to fix it?
@DougHExcel
8 жыл бұрын
#VALUE error are usually data type mismatches like the formula is expecting a value but gets a text string. You may want to check you inputs.
@hmong3392
7 жыл бұрын
Let's say there are two 73 scores for Mike. How do I include one more function to the index formula to remove duplicate scores (ie. to show only unique result)?
@DougHExcel
7 жыл бұрын
Depends what you want to accomplish. If it doesn't matter what row the 73 is in then use index/match or just vlookup to return the first one it encounters (kzitem.info/news/bejne/xWOHvXyZeqyIfW0). Cleaning up the data in the beginning is also another option with remove duplicates feature (kzitem.info/news/bejne/l5ehqoqCn4SijKQ)
@alexandrasantos2462
7 жыл бұрын
This was great! Thanks!! =)
@DougHExcel
7 жыл бұрын
Hi Alexandra Santos...you're welcome, glad you liked!
@noahhadro8213
10 жыл бұрын
in my look up column I am a looking up numbers and am unable to change the numbers to text? The above example only works if I am looking up text and it will then return multiple formulas but my file is to big to type each number into text. Help please?
@jacovanderwatt3841
7 жыл бұрын
Hi Doug, thanks very much this tutorial has helped me in a big way. I still have a problem and need some help please :). Where you have name i have a invoice number followed by details of a product sold in the next 8 columns. With some invoices created it will be 15 rows with the same invoice number. I made a re print invoice sheet. How do i change this formula so that when i enter the invoice number on the re print invoice sheet, it will pull all the product info for that invoice number from my invoice summary sheet back into the re print invoice sheet. Hope you can help. Kind regards.
@DougHExcel
7 жыл бұрын
You may want to try with advanced filter kzitem.info/news/bejne/pIdrzaKEgGKlmIY
@minabmulugheta9751
10 жыл бұрын
what if you need the values to go down by rows instead of columns
@andyhanson1188
7 жыл бұрын
Do you have a video tutorial on creating a pivot table to add dollars in different columns based on year paid? For example, A1 Date 1: 4/5/15, B2 Date 2: 1/5/16, C2 Date 3: 2/1/17. Then D2 $400, E2 $600, F2 $1,000. Then you have rows and rows of data that could have different years and paid amounts. I want to be able to sum the payments by year even though the data in the Date cells could be any year and spans multiple columns. I also want to be able to refresh this easily each month. Thanks
@DougHExcel
7 жыл бұрын
This is probably better to put this into a proper table format (first row fields and later rows all the records). It become a two column table with date | amount and use the Table feature (see kzitem.info/news/bejne/rKShxYF6mJeAo5g) Then you can turn it into a pivot table (see kzitem.info/news/bejne/u4euzY2qpWVkq44)
@thebioagency3372
7 жыл бұрын
Do you know of a way to do the same thing but instead of scores being shown, it's text results? I'm trying to automate a status report
@DougHExcel
7 жыл бұрын
Whether it's number values or text the step should apply.
@zionpremkumar8178
9 жыл бұрын
friends pls help me in this. i have multiple items from A to Z in a a column in one sheet and and same A to Z in another sheet and i can vlookup the same but my B item is repeated thrice with three different value..now advice me to get the values in my second sheet
@scottmehaffey6849
6 жыл бұрын
Cheers, legend!
@DougHExcel
6 жыл бұрын
Hi Scott Mehaffey, thanks for the comment!
@lillianasanchez5984
8 жыл бұрын
Hello Doug, I hope you can help me. I'm having trouble with the following formula: =IF(SEARCH("green",B2),"Green",IF(SEARCH("Red",B2),"Red","")) From the following sample Product Colour Red Apple #VALUE! Green Apple Green The formula finds the first IF/Search properly but the second nested IF/search I get a #VALUE error where searching for "red". I can't seem to figure out my mistake. Can you send me in the right direction? Thanks a bunch.
@lillianasanchez5984
8 жыл бұрын
Thank you so much Doug, you don't know how much you helped me, have a fantastic week ahead
@DougHExcel
4 жыл бұрын
Hi Lilliana Sánchez, thanks for the comment!
@elloco121212
11 жыл бұрын
I try it in office 2003 and I get errors #NUM! What I like to make is a bank page in sheet1 and in sheet2 have Motor Exp in Sheet3 Payments in sheet4 Fee etc. but I do not seem to get it with 2003, 1+1=2 have the formulas changed between upgrades? here is a new lesson for you Doug, thanks fr the tips
@mycraigslistadmaker2398
7 жыл бұрын
Hi Doug, I know this is old, was wondering if you could lend a helping hand? I was wondering how would you add an additional logical, say i want to find all of the marys, then once I got them I want to them find the ones with a specific DOB, then return the values? I tried the following, however it only returns one value ( I have a larger dataset for testing, i have matches that can be seen, the formatting is correct).... using excel on mac doesn't really provide "Evaluate Formula" to see where I'm going wrong =INDEX($A$2:$C$10, SMALL(IF(AND($C$2:$C$10=$F2, $B$2:$B$10=$E2),ROW($A$2:$A$10)-1), COLUMNS($G2:G2)),1) This is of course before i hit cmd+shtf+enter. any help would be extremely helpful, would love to know how to do this with matching different row values with the dataset.
@DougHExcel
4 жыл бұрын
maybe one of these? kzitem.info/news/bejne/x7CctGxqpHaimoo
@jeyakumarvj
7 жыл бұрын
THANK U SO MUCH BRO👍😇
@DougHExcel
7 жыл бұрын
Hi SIVA JK...you're welcome, glad you liked!
@slademd5098
11 жыл бұрын
Could you tell me please. When I close the Doc and reopen it, I have to pres the cell and press ctrl+shift+enter. How can I avoid this thing? Thank you in advance
@maycy92
8 жыл бұрын
I get and error after I apply the array! :/ I get #NUM! Help please! My formula looks just the same as yours.
@franz42
6 жыл бұрын
Have you find the solution? I get the same situation as you..... the formula is just same as the video...
@paulkirkpatrick9378
6 жыл бұрын
i was getting this at first but i realized i left out the close paren that comes after the -1) in the ROW statement =INDEX($A$2:$B$7,SMALL(IF($A$2:$A$7=$E2,ROW($A$2:$A$7)-1),COLUMNS($F2:F2)),2) maybe try copy pasting this into your sheet. It worked on mine
@socialworkuc-main2760
5 жыл бұрын
after typing the formula, press CTRL+SHIFT+ENTER. :) hopefully, it helps.
@DougHExcel
4 жыл бұрын
Hi Miguel Armenta, thanks for the comment!
@priyalpandya4130
7 жыл бұрын
How can we use one unique ID in one spread sheet (In column A for example) and obtain data multiple row of data with that unique ID from different spreadsheet (all columns)? spreadsheet A has one unique ID- ABC and spreadsheet B have multiple (rows) of records with same unique ID-ABC. I want to copy those multiple records in different tab. unfortunately i can just copy first row of the record and not sure how to obtain all records using this formula.
@DougHExcel
7 жыл бұрын
You may want to try with advanced filter kzitem.info/news/bejne/pIdrzaKEgGKlmIY
Пікірлер: 268