VLOOKUP All Matches with this Crazy Simple Trick

Ever want to VLOOKUP but show multiple matching results? The standard Excel VLOOKUP formula has a limitation and it will only show the first matching result. But what if you need to see all matches in one place?
In this video, let me present you a simple formula to get all matches in a list format.
Sample file here - chandoo.org/wp/wp-content/upl...
More on VLOOKUP & Other functions (My Skillshare course):
Check out the Skillshare course on "Excel LOOKUPS for data analysis" and master these functions in just one hour. Here is your sign up link - skl.sh/3hbR49d
Learn more about FILTER function:
FILTER function basics and beyond - • I don’t use filters in...
Other ways to get multiple results with VLOOKUP:
• VLOOKUP multiple match...
Excel VLOOKUP basics: • Excel VLOOKUP - Compl...
XLOOKUP basics: • STOP using INDEX MATCH...
Thank you for watching & reading. Have a beautiful day 😀🌞🌼
#VLOOKUP

[@msmadnesseverywhere]

Thank you so much for explaining this in such an easy manner. This is exactly what I was looking for :)

[@Cezinharaksa]

WOW! Fantastic video! Thank you very much for sharing. Simple and easy.

[@ezenwafoster6270]

Thank you so much for this video. I've been trying to do this for 2days now before I finally found this.

[@careeyam8873]

Thank you very much Chandoo I watched your other video on 8 ways to report in pivot table and it help a great deal to prepare my report. God bless you

[@user-cn8gs3co7s]

you're a life saver! thank you!

[@andreashermle2716]

Dear Chandoo, another great video from you. Thank you, you are doing a great job

[@DinoDelight]

Thats great, was try various formulas to do this

[@mike9483]

Thank you!

[@shivsharma9153]

Love your work man!

[@chandoo_]

Glad to hear it!

[@masonpillay4197]

Thank you, I have really help me.

[@sonalinaralkar5267]

Sir, ur excels videos are so awesome and these videos are very helpful to me improve skills about excel. Thank you Sir!!!!

[@chandoo_]

Glad to hear that

[@jaiganesh6402]

Trust Me.. you made my life much easier... Thanks a ton

[@imjupiterbn1244]

Awesome tips

[@user-kr3ft7im2s]

Excellent classes

[@Sk28222]

Thanks for Sharing, great one.

[@shreyanshdangi4366]

Thanks for this informative video...

[@taizoondean689]

Thanks 🙏

[@mrrishiraj88]

Great thanks, Chandoo.

[@chandoo_]

😀😀😀

[@amanmalalo8653]

Appreciated. Thanks.

[@chandoo_]

You're welcome!

[@erikanichole1899]

helpful tnx

[@manikumarpragada2441]

Nice information Tq Ji....

[@chrism9037]

Very good video Chandoo!

[@chandoo_]

Thank you Chris :)

[@FaysalEasyExcel]

awesome trick on xlookup sir.

[@chandoo_]

Thanks and welcome

[@prabhupbu]

Hi Chandoo.. I feel that pivot table data will serve the purpose of filter formula..?

[@frenan82]

Thanks!

[@chandoo_]

You are welcome :)

[@shopinurhidayanti8104]

can u tell me how do I select excel rows at the same time? at minute 0:42 please. btw thanks for your video, is very crazyyyyy good

[@ghulamqadirchandio2484]

Hi, how can i use multiple Lookup_value in same formula if not found based on first look_up value? should i use if or?

[@vince6383]

Is there a way to list results or concatenating results instead of adding them?

[@muhammadyasirawan6529]

Dear Chandoo, thanks for a very informative video. My question is: how can I use the "spilled" multiple results of this function if I use the FILTER function in an embedded formula. For example if I need to get the first 6 characters with =LEFT(FILTER(xxx,...),6), when I ran it in your file it dodnt work. So please make a video of how to use the spilled results of new excel functions when embedded. Thanks.

[@chandoo_]

You can try and it should work. I explained the concept in my FILTER video here - kzbin.info/www/bejne/gKa3lZ1on5Jsgck

[@rexbk]

Excellent

[@chandoo_]

Thank you so much 😀

[@mohammadrezwanurrahman6616]

If I want to select first two or last 3 numbers of Ram Mahesh, what should I do? Please suggest

[@andrewegerton1962]

Hey Chandoo. This is exactly what I need, thank you. In my requirements I am returning text rather than values. When I edit my 'Sales Person' column the result of the Filter does not return values in alphabetical order. Please can you help on this sir? Many thanks for everything.

[@munaazna8986]

Can u help me? How to name the table like on the video, he name it sales table

[@srikanthm6233]

Hi chandoo it was around up for last times filter session 👍. I would like ask any session on query function which is similar to SQL !?

[@chandoo_]

Thanks Sri... You can watch my Power Query video for this - kzbin.info/www/bejne/hpqpcpSVn8-Ue6s

[@srikanthm6233]

Thank you chandoo ❤️

[@Kishore-ct7zs]

It was informative Mr.Chandoo , I love your name btw 😁

[@chandoo_]

Thanks Kishore... :)

[@nyongcahngapak]

Terima kasih boss

[@girijavarma5271]

much needed

[@chandoo_]

😀

[@waqarshaikh86]

Chandoo, since not everyone have Office 365 installed as of now. Could you please guide how to achieve this in earlier Excel version. Thanks.

[@chandoo_]

You can use array formulas in older versions.

[@mahmoudhassan6627]

Sir , May Allah bless you >> In fact your simplification is awesome >> Thanks Chandoo

[@Surajk927]

Hi if we use countif with vlookup than it was showing multiple results

[@sureshbabubabu9235]

Thank sir exactly iam waiting for this vlook up is same name repeated twice

[@oktayyldz1906]

With this method can we see multiple name's product and amount total

[@Ashish_Bhatia]

Can i get the value horizontally

[@mikeser1999]

If you want to display all sales values in the same cell, you can take his formula and put =SUM( in front of it

[@siddhantpawar3886]

#chandoo... I need help with vlookup. While using vlookup in excel 2013 i am getting wrong value as it is giving me the value of upper cell instead of the actual answer of my data.i have checked the formula, sorted data in alphabetical order. How can i correct this issue...!!?

[@chandoo_]

Check if you have FALSE or TRUE as the last option for VLOOKUP. Usually that is the culprit.

[@thinkhelpservice]

this is exactly what I have been looking for however my work PC is not using EXCEL 365, how do I achieve this on a non 365 excel??????

[@chandoo_]

Thank you. In older versions of Excel, you can use Array formulas to do this. Here is an old article I wrote - chandoo.org/wp/vlookup-second-value/

[@thinkhelpservice]

@@chandoo_ thank you for the suggestion however it only partially replicates the results I want returned

[@sureshbabubabu9235]

Sir I want same video with two different sheets with(with employee names repeated using filter and vlookup ) plz do a video

[@jayarpenaranda6381]

so it means this is not applicable on the basic excell?

[@obscene187]

What if I'm trying to do a partial match with hundreds of potential partial matches? I have a long list of partial strings in $K$28:$K$400, and I want to see if any of those cells are a partial match to $D5. Been searching online all week but cant find anything? Any suggestions?

[@obscene187]

Also I cant use VBA as I'm on a mobile excel version.

[@govardhanp8333]

Dear Chandoo, Thanks a lot for the very informative video. it is really simplifying our work. I wanted to ask that, in this video, you are using D4 cell as base, so whatever you alter in D4, accordingly the results will be displayed below. What if we have multiple D4 values to be extracted? In other words, I have sheet A with multiple sales person names (eg. suresh, mahesh, ramesh), and sheet B with their sales data(eg: suresh data for 3 rows, mahesh data of 6lines, & ramesh data of 4 lines).. sheet B also has extra names which is not necessary . So into sheet A i need to extract all the sales data. I cannot do one by one as my data is huge. I want to lookup all the data from sheet B to sheet A in one shot, without altering D4(in ur video) everytime. Appreciate if you can help. THanks

[@Rajj20]

i think u can use data validation of a list of names in a cell. then u can directly click on dat cell n choose the name and the results will change accordingly.

[@HariKumar-ru9no]

Hi chandoo. Thumbnail showing vlookup. But content about filter function...😉😉😉

[@chandoo_]

That is because when people search for this question, they ask "how do I vlookup multiple results?". If they know FILTER, they wouldn't be asking this question.

[@HariKumar-ru9no]

@@chandoo_ Yes.. Thank you chandoo..I wish u do more video like this...🥰🥰🥰,Have a good day..

[@theav.1313]

Could you please do a video using Google Sheets for Data Analyst?

[@chandoo_]

I don't Google Sheets at all, so unable to do a video at this point. All the best.

[@949surferdude]

How can I get result showing Ram's result for $1624 along with 70% dark bites and New Zealand?

[@JJ_TheGreat]

2:47 But what if the columns which you want to filter for aren't side-by-side? Will it still work?

[@RAVISHARMA-hn1iv]

This is what i was too looking for

[@chandoo_]

You can either use two separate FILTER functions or one giant FILTER inside INDEX with SEQUENCE, like this: =INDEX(FILTER(sales, sales[Sales Person]=D4),SEQUENCE(COUNTIFS(sales[Sales Person], D4)),{4,5,1}) This will return columns 4,5,1 in that order where sales persons name is in D4.

[@user-kr3ft7im2s]

SQL data analyst videos cheyandi sir

[@Adriaan.J.L]

I don’t think I’ll need it, but just for in case, what function can you then use for HLOOKUP?

[@chandoo_]

It is the same function. FILTER works in horizontal mode too...

[@Adriaan.J.L]

@@chandoo_ OK, Thanks

[@sarangborse9314]

Is filter available for 2021

[@chandoo_]

No. It is only available with Excel 365 / on web.

[@shamskhawaja1667]

What if we don’t have Excel 365. Then what should we do

[@munaazna8986]

Why “sales” how to save table with name like that? On my excel thats not work

[@anikettembe6451]

Can you please guide me how to solve for version except office 365

[@thinkhelpservice]

yes im also wanting an alternate solution for non 365 users

[@chandoo_]

You can use array formulas in older versions of Excel. Here is an article on that - chandoo.org/wp/vlookup-second-value/

[@barindersingh5931]

I don't have office 365 so I am facing the same problem, please do help me to solve this problem in another way

[@IjazSarwer]

can u just hold the screen, where it is. zoom in & zoom out. i cant see the table.

[@engr.engr.7205]

How can i return filter values horizontally instead of vertically

[@chandoo_]

You can send the output to TRANSPOSE function.

[@engr.engr.7205]

@@chandoo_ actually i tried to put the unique values as transpos(horizantal) but excel giving error columns not enough so i am querying to have the output in horizantal instead of vertical

[@chandoo_]

Excel can only show 65,000 columns. If your unique data has more than that or there is a spill error. You can fix spill errors by clearing the cells right of your formula.

[@super_man.]

How to add thag person salary

[@medhaparab5074]

I have query in this can anyone help ??

[@serenemary873]

1:55

[@barindersingh5931]

I have office 2013

[@canirmalchoudhary8173]

Just shift in thinking and you will use FILTER

[@rajj5303]

They are deducting money , skill share

[@chandoo_]

It is a paid online learning platform. There is no such thing as free lunch my friend.

[@coordinacionnacionaloev7282]

This is not VLOOKUP.

[@just4funTV72]

but its useful

[@apolovzla_ccs]

Only if you're looking for a wrongly titled video of a function you can only use if you pay for Office 365.

[@roynotintown]

Useless formula, I would prefer to use pivot instead 👎🏻