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
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.
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.
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.
#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...!!?
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.
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?
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.
@@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
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.
