Excel Lookup to Return Multiple Values with FILTER Function



Check out the complete Excel Course covering the brand new functions:

You’d like to do a VLOOKUP but return ALL match results instead of just the first match? You can use Excel’s new FILTER function.
What if you’d like to lookup multiple criteria and return ALL match results? You can use the new Excel Dynamic Array FILTER Formula! It’s like the dynamic or automatic version of Excel’s advanced Filter feature. You can return Filtered results based on AND as well as OR conditions.

The Excel Filter function filters your data based on criteria, and returns all the matching records. This can be one or more records. If more than one match is found, the results will “spill” vertically. If there is a blockage (i.e. your results are spilling into already filled cells) then you will get the #SPILL error. You can easily get rid of this error by removing the cells that are blocking the FILTER formula.

If FILTER doesn’t find any matching records it will return a #CALC error. You can suppress this error with the text or value of your choice by adding this to the last argument of the FILTER formula.

* Availability of Excel FILTER function: This feature is available on Excel for Office 365 – also for Excel on the Web and Mobile.

Quick Navigation:
1. Advantages of Excel Filter function: 0:45
2. Excel Filter Function Explained: 1:46
3. Excel FILTER #CALC Error: 3:15
4. Excel #SPILL Error and how to overcome it: 5:40
5. Excel FILTER Function with Table References: 6:13
6. Excel FILTER formula with multiple lookup criteria: 9:20

⯆ DOWNLOAD the workbook here:

Return Multiple Match Results (Excel Legacy Method):
More about Excel Dynamic Arrays:
Excel Dynamic Arrays Playlist:

★ My Online Excel Courses ►

✉ Take this Quiz to choose the right Excel course:

EXCEL RESOURCES I Recommend:

Get Office 365:
Microsoft Surface:

GEAR
Camera:
Screen recorder:
Microphone:
Lights:

More resources on my Amazon page:

Let’s connect on social:
Instagram:
Twitter:
LinkedIn:

Note: This description contains affiliate links, which means at no additional cost to you, we will receive a small commission if you make a purchase using the links. This helps support the channel and allows us to continue to make videos like this. Thank you for your support!

#MsExcel

Nguồn: https://speakforchange.org/

Xem thêm bài viết khác: https://speakforchange.org/tong-hop/


Article Categories:
Tổng Hợp

Comments

  • What do you think of the FILTER function? It's slowly being rolled out to Excel "regular" desktop users with Office 365 (Make sure you're set to receive the latest updates) – some of you already have it. If you don't have it yet, go to Excel Online or Excel Mobile and test it out. Let me know what you think.

    Leila Gharani June 30, 2020 1:26 am Reply
  • Hi! I noticed an hour ago that functions like SORT, UNIQUE and FILTER are unavailable now. I have Office 365 Business and these functions are missing. Anyone know why?

    zarko tripunovic June 30, 2020 1:26 am Reply
  • دستت طلا

    rexalex09 June 30, 2020 1:26 am Reply
  • This is fantastic. Thanks for posting this video.

    SujithGJ June 30, 2020 1:26 am Reply
  • Amazing! Best video ever!!!!!!

    David Schubach June 30, 2020 1:26 am Reply
  • I felt it will be rude if I go out from this valuable video without subscribing, clicking like and saying many thanks to you Leila.

    Khaled Alzahrany June 30, 2020 1:26 am Reply
  • is there any filter function in excel 2016 version?

    Md Tausif Iqbal June 30, 2020 1:26 am Reply
  • how do you do the filter function if there are empty cells? thank you

    Henky Pranajaya June 30, 2020 1:26 am Reply
  • The tip on returning lists based on 2 criteria is very helpful.

    A R June 30, 2020 1:26 am Reply
  • how I can add this function in Excel 2016 through VBA =FILTER(array, include, [if_empty]) | netdera at the rate of gmail dot com

    Net Dera June 30, 2020 1:26 am Reply
  • Excellent.

    Rohit Arora June 30, 2020 1:26 am Reply
  • Watching yr video again and I saw a sheet called 'Single'. One question…..I do not have the SINGLE function in my version. What do I do to get it?? Thanks for taking the time to answer. : )

    John Borg June 30, 2020 1:26 am Reply
  • Hi Leila, another good explanation but I have a doubt. Is there a way to not spill all the columns? For example, I have 4 columns of data but I want to get the data of 1,3,&4th column only. Like, the example sheet has Division, Region, and Revenue columns and suppose I add Salesman column between Region and Revenue columns. Now, with the Filter formula, can I extract the data only Divison, Salesman, and Revenue columns?

    M T June 30, 2020 1:26 am Reply
  • Thank you so much for your wonderful videos😊
    Are there big improvements for powerquery Wittenheim office 365 ? If so, may we have a new vid ? Thx

    Benoit GAUTHEROT June 30, 2020 1:26 am Reply
  • Is there a way to populate FILTER results horizontally (columns) rather than vertically (rows)?

    snakeoids1 June 30, 2020 1:26 am Reply
  • Hey this is. A great learning vehicle. But tell me. What’s the benefit versus putting a filter on the data?

    ronaldbear1 June 30, 2020 1:26 am Reply
  • What formula can I use to replace the filter function if it's not available on my excel?

    james scott June 30, 2020 1:26 am Reply
  • very very handy

    SEGUN DAMILOLA June 30, 2020 1:26 am Reply
  • Thank you so much for all these courses !! Really appreciate it. I do have one question, if i manage to add weblinks to one of the dataset, would it reflect in the after i filter?

    Sumi J June 30, 2020 1:26 am Reply
  • sorry, I can't use this =filter formula in my Excel.

    Chheng Tola June 30, 2020 1:26 am Reply
  • I don't have the filter function yet but how can I achieve this without the filter function?

    Deji Bodunwa June 30, 2020 1:26 am Reply
  • You video tutorials are always inspiring, please I seem not to be having that formula in my version of excel. I am using Microsoft Office Professional Plus 2019, but seem like the excel filter function is not valid as they say, what should i do?

    Kengah Charles June 30, 2020 1:26 am Reply
  • The only problem I have with this is I can't use it! 🙁 Sadly, I only have the 2013 and 2016 versions. But this is just TOO COOL! I can't wait until I'm able to try these features out! Thank you for sharing 😀

    Cassandra Bishop June 30, 2020 1:26 am Reply
  • Hello, I am using Excel 365 and i do not get to see this function. i am trying to use it as "=filter(" and it says the function is not available. Please advise.

    Hemchandra Narendranath June 30, 2020 1:26 am Reply
  • Many thanks for this video, very helpful. Enjoy your very clear and well explained tutorials. On this particular video, all the search criteria need to be filled out, otherwise no result. How can the formula be amended in such a way that not all the criteria have to be filled out each time, in other words left blank. With AND function, all the criteria have to be fulfilled, with OR, only one. Have been trying to build a formula that will FILTER basis more than two criteria filled out, and when left BLANK that part of the formula should be ignored, however not very successful thus far. Any chance you can shine some light on this?

    Jo Sc June 30, 2020 1:26 am Reply
  • Hi Leila,

    Apprectiate all your good work.
    I have office 365 but still, I can't find filter function. can you help on how to add the function?
    Thanks in advance.

    Madhan kumar June 30, 2020 1:26 am Reply
  • Thank you 😀

    Gohary936 June 30, 2020 1:26 am Reply
  • Hi, I tried typing =FILTER, but it does not seem to be available in my excel 🙁

    Sandeep Thomas June 30, 2020 1:26 am Reply
  • This has to be the most wanted formula ever! 🙂
    Unfortunately I can only access this in the browser version of Excel (for now) 🙁
    This platform limitation may or may not be the problem I have. I need to return the filtered dataset to a dynamic table(like the one you use for the base data) but get the #spill error, is there any way to return data to a dynamic table ? (results need to be imported into an application that reads tables)

    XAD566 June 30, 2020 1:26 am Reply
  • How can i merge multiple workbooks in a single worksheet… Is there any function or process to have this done?? Please inform me

    rayhan hossain June 30, 2020 1:26 am Reply
  • Hi there, I want to type a staff name in one empty cell and filter the entire table so it only shows up rows with the staff name I am searching for. I also want the background colour to appear in the filtered results. Thank You.

    P Chidambaram June 30, 2020 1:26 am Reply
  • How AND OR functions can be used with the filter.
    Use of * and + looks out of normal method

    Abhijit Joshi June 30, 2020 1:26 am Reply
  • I am not able to find the Filter function in my excel though I am using MS Office 365 Pro Plus

    Bhaskar Chatterjee June 30, 2020 1:26 am Reply
  • Is it possible to utilize the Filter function in a data validation drop down list. So far, I have not been able to achieve this and am thinking these don't play nice together.

    John Welsh June 30, 2020 1:26 am Reply
  • This is exactly What I have been looking for
    The last one,Finding Division and region together

    Ajay Dhodiya June 30, 2020 1:26 am Reply
  • Thank You so much

    Ajay Dhodiya June 30, 2020 1:26 am Reply
  • How do I return only specific columns from the table using the FILTER function that are not nearby to each other? From your example, how can we return just the columns "Division" and "Revenue" (which are not near to each other) using the same above criteria?

    Dinesh Kumar June 30, 2020 1:26 am Reply
  • How to filter numbers contains a particular nth digit.
    Say :
    134543
    724654
    985675
    874609
    The 3rd from left of every no contains ' 4 ' needed to filter.
    How,?

    Arun Anand June 30, 2020 1:26 am Reply
  • I'm just loved this new Function! Revolutionary! Thank you for the amazing video!

    Bruno Shoiti Natsumeda June 30, 2020 1:26 am Reply
  • Whenever I have a problem in excel, I generally look for your videos and always found a solution for the problem.

    balu9999999999 June 30, 2020 1:26 am Reply
  • Is there a way for excel to return all filtered results in just one cell, where you can divide them into new lines instead?

    Jan Von Victor Alcoba June 30, 2020 1:26 am Reply
  • Great tutorial! If I may ask one question? Is it possible to filter (or lookup) a range of cells/values instead of one or even two values like shown in your tutorial?

    Dennis Achten June 30, 2020 1:26 am Reply
  • HELLO LEILA…JUST A SHORT Q..WHY MY EXCEL DOSENT HAVE FILTER FUNCTION?ONLY FITERXML SHOWN UP

    Berkat Sabak Sdn Bhd June 30, 2020 1:26 am Reply
  • can I compare against rows of yet another table ? Eg. FILTER for Asia or Europe -> would then put Asia and Europe in a separate table serving as lookup.

    Sam Szafranski June 30, 2020 1:26 am Reply
  • Awesome…

    Waheed Makbul June 30, 2020 1:26 am Reply
  • Amazing tool of-course from amazing trainer.

    This will save me so much time.

    Thank you Leila

    Bandar Zoghbi June 30, 2020 1:26 am Reply
  • can we do same stuff without filter formula or office 365?

    Muhammad Abdullah June 30, 2020 1:26 am Reply
  • Very easy and helpful!

    Ron Cohen June 30, 2020 1:26 am Reply

Leave a Comment

Your email address will not be published. Required fields are marked *