VLOOKUP Return Multiple Matching Rows and Columns
File Download: www.excel-university.com/yt_s...
Learn Excel the FAST way -- www.excel-university.com/yt
Watch the VLOOKUP Hacks playlist here • VLOOKUP Hacks Series
Subscribe here 👉 bit.ly/exceluniversityyoutube and turn on all notifications 🔔 for MS Excel time hacks that will make you more productive!
Watch VLOOKUP vs SUMIFS Playlist here • VLOOKUP vs SUMIFS Series
Watch Dynamic Arrays playlist here • Dynamic Arrays Series
Watch Treasure Maps playlist here • Treasure Maps Series
Related blog post:
www.excel-university.com/vloo...
In this video, we'll discuss a way to simulate using VLOOKUP to return multiple matching rows and/or columns. What do you mean by "simulate" Jeff? Well, VLOOKUP is designed to return a single value, not multiple values. That is, VLOOKUP scans down the lookup range and stops at the first matching row ... ignoring any additional matching rows. Once VLOOKUP finds a matching row value, it shoots to the right to retrieve the related value from a single column. But let's say we want to return the values from multiple columns and all of the matching rows. Well, this is where VLOOKUP breaks down. So, we'll simulate this functionality by turning to Power Query.
Пікірлер: 100
i have been searching so crazy long for this solution, thank you very much, subscribed!
@ExcelU
Жыл бұрын
Glad it helped ... and welcome to my channel :)
Simple and straightforward. As good as a tutorial can be! Thanks!
@ExcelU
2 жыл бұрын
Thank you!
Thanks a lot for this video!!! This highly saved my time on a project.
Awesome video! Easy to follow and I have just half the time for a major task and de-risking some work processes! Thank you!
Superb sir. I’ve been searching this for many months. Thanks.
@ExcelU
Жыл бұрын
Thanks, glad it helped!
I have been trying to figure this out for longer than I even want to admit. Thank YOU!!!
@ExcelU
Жыл бұрын
Glad it was helpful :)
You're BRILLIANT, sir. this solved my problem perfectly. Thank you!!!!
Your video was absolutely fabulous in teaching me how to do what I needed! Thank you so much for taking the time to record this.
@ExcelU
2 жыл бұрын
Glad it was helpful!
This was very slick. Probably best Excel video I've seen! Thank you!
@ExcelU
2 жыл бұрын
Thank you!
I have never been more satisfied by the solution for my query than this. this was absolutely spot on what i wanted. Thank you :)
@ExcelU
Жыл бұрын
Thank you for your kind comment, I sincerely appreciate it! And I'm glad the video was helpful :)
Than you, this is great! I used it on multiple references that would return multiple values each. You could imagine the pain that I went before finding this video, the scalability of this method is quite good! 👍
@ExcelU
3 жыл бұрын
Thank you, I'm glad the video was helpful!!
Thank you Jeff! You definitely deserve 10M subscribers.
@ExcelU
Жыл бұрын
Thank you for your kind note :)
You are a life saver. This saved me tons of man hours. Very powerful tip and clear instruction
@ExcelU
2 жыл бұрын
Thanks ... glad it was helpful!!
This was immensely helpful. This was the last step of a huge data sorting project and you helped make it a quick one! Appreciate it.
@ExcelU
2 жыл бұрын
Thank you so much for your kind note, I'm glad it was helpful!
@AffJanderson
2 жыл бұрын
@@ExcelU I know what channel I'm going to for any future Excel tutorials! Hope you have a great rest of your week.
Thanks alot it's was a wonderful tutorial 😊
@ExcelU
11 ай бұрын
Thank you!!
THANK YOU SO MUCH!, this really helps and saving hours of tedious work
@ExcelU
2 жыл бұрын
Excellent ... love to hear that it will save time :)
Just ruined my Saturday! Now I gotta try this for my work! Thanks!!
Brilliant tutorial. I came here after hours trying to bash a square VLOOKUP into a round data alignment, and was able to apply your method to an enormous spreadsheet within half an hour (the data needed some tidying). Thank you.
@ExcelU
Жыл бұрын
Thanks John … glad it helped!
This guy is my hero for making me look like a stud at work
@ExcelU
3 жыл бұрын
Glad I could help :)
I really can't thank you enough. Amazing work, thank you.
@ExcelU
2 жыл бұрын
Thank you I'm glad it was helpful!
great, being looking for this for some time now...got yourself a new subscriber
Amazing video I went through atleast 2 dozen combinations of vlookup and they couldn't do what I needed not only did this solve my issue but did it super easily and quick.
@ExcelU
2 жыл бұрын
Glad it helped!
Glad I could help :)
PERFECT. I have gone through number of video's and couldn't achieve the result that I was looking for.. however this one has awesomely helped me to get things done, really appreciate it, thank you.
@ExcelU
2 жыл бұрын
Thanks ... glad it was helpful :)
This video is so underrated! You explained this perfectly! Thank you! I just have one question: Is there a way to delete duplicates on the final items list as well?
@ExcelU
6 ай бұрын
Thanks for your kind note :) Yes, you can remove duplicates in a Power Query step by going to Home > Remove Rows > Remove Duplicates. Hope it helps!
Brilliant!!
very useful and this is what I was looking for , thanks im not how im gonna do this as have never done power query before :(
Excellent and clear
Great tuturial! Helped me out a lot.
@ExcelU
9 ай бұрын
Glad to hear it!
Great technique Jeff...I just need to remember that Table.Column function next time I need it 👍
@ExcelU
2 жыл бұрын
Thank you!
Actually, it's a wonderful tutorial.
@ExcelU
9 ай бұрын
So glad it helped!
This is great!! Thankyou so much
@ExcelU
2 жыл бұрын
Thank you glad it was helpful!
Great video - thank you very much
@ExcelU
2 жыл бұрын
Thank you, glad it was helpful :)
Hey , this video is great want to add one query in this same, how to fetch not a matching column as well putting not matched text in specific row?
Thank you sir... This helped a lot :)
@ExcelU
2 жыл бұрын
Most welcome!
how would you be able to determine the actual quantity of each line item with this? Any way to break it out?
Hi will you be able to explain same method with Xlookup formula? I can xlookup a value from 2 different sheets. However, how can I xlookup if there are 30 rows in one go? Thank you very much.
how can we change the formula to concatenate two columns before displaying them as a list @5:45
Hey I actually have another question, are you able to use 2 matching collums when merging?
New subscriber.... Wonderful video
@ExcelU
2 жыл бұрын
Thanks for your kind note and I'm glad it was helpful!
That was really helpful - thanks! Is it possible to have the multiple results in separate columns, rather than comma separated in one column ?
@zenitsu1646
10 ай бұрын
A bit late but for people searching for the answer to this question, the last step would be utilizing the "Text To Columns" functionality in excel. Search it up :)
Awesome!
@ExcelU
2 жыл бұрын
Thank you!
how get item label in seperate columns
Does the new XLOOKUP have this feature?
could we just do it with normal pivot table?
Customs column formula is not working for me
Can we return more than 1 result ? At your Exp, you returned list col, but if i need to return more than 1 col, how i can make like that.
** Any One Can you please Help me with this Error Expression.Error: The column 'item' of the table wasn't found. Details: item
I have watched 25 times and hit problem at 6:15. He clicks on Extract Values and then selects a delimiter. This feature is no longer visible/available in the latest version of Excel for Mac. It is the FINAL step I need. Help please!
you saved me :D
@ExcelU
3 жыл бұрын
I'm glad the video was helpful!!
What if i have same value in the item column will it add?
@nabsnabs8622
3 жыл бұрын
Pls help me with this sir😥
Got till "Added Custom Column" but not able to Extract the values, when clicking it is automatically getting expanded
Is this work for excel 2013 or 16
Unfortunately Mac doesn't has that option
What version of excel are you using for this demo?
@ExcelU
2 жыл бұрын
Excel for Windows O365
how to use Power VI in Daily working
Is power query an add-on ?
@ExcelU
Жыл бұрын
It is included in modern versions of Excel for Windows :) Just head to the Data tab, and look for the commands in the "Get & Transform Data" group. These initiate Power Query :)
i dont know why its showing error???? 5:50
@mugas2002
Жыл бұрын
wow its working now thnx
Does anyone knows how to create an Excel Macro that Searches Entire Workbook and Returns All Records which match a certain value from a drop down list? e.g. if we have a list of many large organization in a worksheet and have a list of all of their employees ( many per organization) in another worksheet. How we can see the records of all employees in an organization by selecting an individual organization from a list or drop down box, etc. I would appreciate if your insights.
Amazing! Even for me that I programm y C,C++, visuam basic, the formulas for the matrix vlookup are complex to understand, but this method saved my ass, thank you so much! I need some sleep haha.
@ExcelU
3 жыл бұрын
Glad it helped!!
aaku paaku karrepaaku, kyaav re baaawa kya bolare tu, sir ke uper se nikalgaya mama
Nooo, just vlookup 1 column, after that in another row, copy formula and replace 2 with 3. If you want more date, reapet.. easy and last for 10 sec.
Great video for an absolutely broken product -I cannot believe this task is so very difficult in Excel. Why can they not create a simple SQL query structure to return your data object and that way you can change a cell to change the data. These Pivot Tables and Power Query all miss the point of working from the Cells to create the query. It's absolutely crazy that we have to create a redundant table, massive clicking to create this "power query" string and then when the dust settles, now we can finally call a VLOOKUP on this thing Microsoft seems to have the audacity to call a separate data object. As complicated as that task was, its far quicker to teach someone to SELECT * FROM sometable WHERE stuff... Why can't we do this in Excell?
@ExcelU
2 жыл бұрын
Hi Dan! It would be AWESOME to have a SQL function that enabled us to create select queries!!! In the meantime, the FILTER function may be able to provide the capability you are seeking. It enables us to select range to return (SELECT * FROM tbl_name) and define one or more conditions (WHERE ...). More info: www.excel-university.com/filter-vs-vlookup-index-match-xlookup-sumifs/ And all my FILTER posts: www.excel-university.com/tag/filter/ Best, Jeff
FAR TOO MUCH WORK!