MS Excel LOOKUP Formula: Return Multiple Values
ฝัง
- เผยแพร่เมื่อ 3 ต.ค. 2024
- In this tutorial video, learn how to use the LOOKUP function in Microsoft Excel to return multiple values.
Discover how to retrieve data from a table using multiple criteria, and how to apply the formula to a variety of scenarios.
With step-by-step instructions and helpful tips, this video is perfect for anyone looking to improve their Excel skills and enhance their data analysis capabilities. Watch now to master the LOOKUP formula and take your Excel proficiency to the next level.
Enjoying this Excel tutorial from @ExcelCampus? We have a TON of #msexcel tutorials for Excel beginners and experts (and everything in between!)
Are you always learning something new when it comes to Excel? We definitely are.
This is why we created a playlist dedicated entirely to our Excel new tips and tricks: • Tips & Shortcuts
They'll help you master Excel so you nail that next interview, or at least find formulas, macros, VBA, and pivot tables a little less confusing...
Wow. At last a clear,slow explanation. I want to follow your teaching for sure. Thank you.
You have no idea how your awesome video removed hours of anxiety and fruitless formula building. I am so grateful to you! God Bless! :)
Thanks for your feedback, glad I could help. 😀
This helped a lot. Thank you!
But if anyone like me nedeed the results to be in a single row, different collums, just use the "transpose" function in front of the whole thing. After this you can also easily separate then by commas or another character with another text function, all in a single cell.
Thank you!!!
Hero!!! Thank you!
If you want to make these values comma separated, etc. The TEXTJOIN function is the easiest way.
Thank you guys, I really needed this
This saves my life! Thank you guys
Thanks a lot, i was looking for this simple formula, i had complicated one. You showed the easiest way, Thanks a lot and GOD bless you.
Glad to hear that, Venkataramu! 😀
THIS IS EXACTLY WHAT I WAS LOOKING FOR. THANK YOU!
😬
Welcome! 😀
wow that helped so much and it only took 1 min. thank you so much for helping and saving time
Exactly what I was looking for. Thanks!
Great to hear that 😀
thank you brother
Thank you, just what I was looking for
That's good to hear, @tommyharris5817 😀
Oh heck!! I wanted something like this the other week!!! Thank you
Thank you. Exactly what I'd been looking for.
Thank you so much! I appreciate you getting right into it which helps me to keep working :)
You are so welcome, @SaritaMoreno! 😀
Exactly what I'd been looking for 2 days!! Grateful this shorts popped up on my feed😊
Glad I could help! 😀
Just exactly what I need. Thanks a lot.
Great to hear, Minh! 😀
your smile makes me happy
Thanks It works and Yes you can transfer data between sheets.
Been looking for this. Thanks a lot!
Awesome, thanks! 🙂
Excellent instruction, thanks.
PS if you did a separate video with the complex functions as well, that would be also very helpful.
This is amazing
Thanks, helped a lot! In my case I wanted the result to be a single string separating additional returns with a coma in an additional row, so my final formula became this:
=TextJoin(Char(10)&",", TRUE, Filter(I2:I22, G2:G22 = B2, "Not Found"))
Big thanks. The video is short and super informative.
You're welcome, @senya1696! 😀
Awesome 👏🏽 Love this, how would I pull the numbers and names?
Amazing! Thank you.
Thank you too! 😀
This video is very helpful. Thank you.
Glad it was helpful! 😀
This was really helpful thankssss
Glad it helped! 😀
That was great! Thanks.
You're welcome! 😀
I'm using office 2016 which does not have filter option. What is the alternative to it?
Thanks a lot
what if under company 1 there's another company on that that row, will automatically add rows?
Thanks lot 😊
Welcome 😊
thanks 👍👍
Welcome 👍
Thanks a lot.. amazing
Most welcome 😊
Amazing thank you sir for your efforts
All respects
Thanks, Youcef! :)
The filter function is great but what do i use when i have multiple orders?
How could I do this for multiple companies at a time?
Say you needed FlashDog and another company? Is there a way to auto fill this function?
Question: how would you then aggregate all those values in one cell seperated by comma's? Thx for the tutorial
Would love to know if you figured this out? :)
used power query instead 😅@@adamweeks5005
this is so useful for me thank you! however, i have a list of multiple criteria's and each value spills over into the cell below. how do can i do multiple values at once to go into one cell?
Thanks, I have a question about your INCLUDE argument. G2:G22=B2, use the array and set it = B2. Why not skip the array and just use =B2? What is the benefit of using the array and setting it to = B2?
It will work only for one filter word, but if you have list of more then one word then it is better to wrap that result in one cell.
How can one wrap it in a cell?
@@lukmanaliyu7386He had only replied on the same also. Later I saw that here. th-cam.com/users/shortstnK3cnrxbrk?si=pwYxZFI6MSsMzDcJ
Do you have any solution to wrap result in one sell
@@TrangNguyen-lk7miI think you do that with wrapping the same formula under =textjoin function.
@@dharmmu I just found out that textjoin will work but it have limited range (around 3x.xxx rows). Do you know any solution that works for more than that? Thank you so much
Found this really handy. But is there a way to copy the filter column to rest of s/sheet?
Is there a way to pull multiple vales into multiple names and not just one? Thank you
This is wonderful, but what if I'm working with a list of things I want to match. For example, what if I had a list of companies, and I wanted to capture all of the phone numbers. I don't have room for them to be listed vertically because I am working with a list. Can this be used to list horizontally?
Great question! Yes, you could use the TRANSPOSE or TEXTJOIN functions to display the list horizontally. I'll add this to our list for future videos. Thanks! 🙂
Exactly, I needed the same! :) to prevent data overwrite..
I was needing the exact same thing. I came across this video. Hope it helps. th-cam.com/video/irR9bUKzTw8/w-d-xo.html
I don't have the Filter Function, what can I do?
Is there a way to automate this for each unique value in the company and return concanated values, to split into separate columns later.
Dont have a filter function in my excel. What should i do?
Is this function available for Excel 2013
Can i get this in rows instead of column?
Is this doable in google sheets?
Is there a way of getting the Output values to remain in a single cell (I.e Phone no. 1, phone no. 2, ....) rather than spilling into 4 cells
Great question, Stevie! 😊 Wrap the FILTER formula in TEXTJOIN using a comma and space as the delimiter. For example, you could replace the formula in Cell C2 from the video with the formula below:
=TEXTJOIN(", ",TRUE,FILTER(I2:I22,G2:G22=B2,"Not Found"))
You may need to wrap that with an IFERROR function to handle additional cases. 🙂
The last part where you hit ENTER, does not work properly on my WPS excel.
For example if it is to bring out three like yours it only brings one then if I try to drag down, it brings completely different stuff
So how do I solve that issue
My office 2019 not have filter formula 😢
Hi , what if i require the dropdown list ? Kindly explain
I think im struggling. I'm attempting to use the filter function to show multiple locations for the same asset. I've got it to kinda work but it does work on the assets found at multiple location. Any tips?
can you have that output go into a single cell like in "word wrap" or output in a single row instead of a single column? might see if there is a way to use transpose within the function or something, but if there is a way someone please let me know. thanks.
got it, =transpose(filter function) will output in a single row using multiple columns. Would be better if could stay in a single cell though for my use case.
and using =concat(filter function) and word wrap I was able to somewhat get what I needed... why post all this in a nested comment chain on a random youtube video? because it helps me think and work out my problems and hopefully it may help someone or myself when I forget and come back here for help again.
I have one date 02-Jan in a column A1 and one stock name (Let's say Apple) in second column B1.
and other side I have one big history list of the apple stock where A1&A2 columns are same and additionally given "C1- Open, D1- High, E1- Low, F1- Close)
Now I want to fill the Open, High, Low, Close of previous date 01-Jan at right side of date A1 and stock name column B1. In column C1,D1,E,1,F1 to be filles previous Day 01-Jan prices (Open, High, Low, Close)
How I can pick the upper row of my date row into the new column.??
Please help
How do put the output in different columns instead of rows?
Great thought, Arpit! Wrap the formula in TRANSPOSE(). Something like =TRANSPOSE(FILTER(I2:I22,G2:G22=B2,"Not Found")) should work. Hope that helps!
How to use if you add more data?
I got #N.A with the same sample that you did. Do you know why? it says the missmatched range
Hi LyonJerry! 😀The FILTER function has three arguments: array, include, and if_empty (optional). The #N/A error occurs when there is a problem with the include argument. Check what was typed very closely to see if there are any stray character typos. Hope that helps! 😀
👏👏👏
Filter is epic but doesn't work on older versions of excel
What if I what to look 2 criteria to return 1 value?
Hi, How to get multiple lookup value in single cell ?
Hi Rupesh! 😀 Use the TEXTJOIN function to join multiple results into one cell. For example, putting the formula =TEXTJOIN(" | ",,C2#) into Cell D2 in the video would join all four of the FILTER results from the spill range into one cell, Cell D2, separated by pipes. I hope you get to use it! 😊
Hi. Can you suggest alternate formula of TEXTJOIN , bcz in microsoft excel 2013 TEXTJOIN formula not available.
My excel is probleming when i write this formula they showing error
Please and my question
If I am giving to A Rs 15000, I will write as (-15000) ; now again I have to give to A Rs 8000 so will write as (-8000) in next cell of Excel. How I will get result as I will have to give to A Rs 23000 , how this figure will shown as (-23000)
Reply please
When I put "not found" it doesn't work.... I don't understand
Which screen recording tool you have used for this?
iPhone 12 Pro
there is no filter function excel 2019?
Hi Ankit! Microsoft rolled out the FILTER function with other dynamic array functions in 2020 to Office 365 subscribers. FILTER is available on Excel 2021 and all later versions (Microsoft 365). Converting the data into a Table or using a PivotTable may be good workarounds for filtering items in the meantime.
How to ignore the empty cell meaning don't import them to my list ?
That formula is good, but all i need is the fisrt and last entry. Please help 😅
Just wrap it with choosecol().
1. Convert the dataset to table
2. Filter(array = dataset, include = criteria or condition)
3. Choosecol(filter(.....), list the column indexes)
Voila, easy just like taking break fast 😊😊😊😊😊
Tried on Google sheet, but it won't work :')
In Google Sheets, this doesn't work. =FILTER(condition 1, conditino 2, cond... cond.,.......)
Not in Excel2019?
FILTER should be available in Excel 2019. At the top of this page you can see a list of the versions FILTER is available on. support.microsoft.com/en-us/office/filter-function-f4f7cb66-82eb-4767-8f7c-4877ad80c759?
I hope that helps. Thanks again and have a nice day! 🙂
Can we not use pivot table instead?
Hi Abhi, Great question! Yes, you could also use a pivot table and add the company name field to the Filters area. Or use a slicer.
One current advantage of the FILTER function is that it automatically updates. Pivot tables require a refresh. But it really just depends on your scenario and workflow.
I hope that helps. Thanks again and have a nice day! 🙂
@@ExcelCampus Thanks for the detailed reply!
There is a function called LOOKUP. Please change the title to reflect the fact you're actually using the FILTER function. I am trying to help someone with old Excel, and this falsely implied it could help.
only in office 365
Why not just sort your table?
Rip excel 2016 users
I dont have this fuction "=filter" in my Microsoft Excel. What should I don?