MS Excel LOOKUP Formula: Return Multiple Values
Вставка
- Опубліковано 14 бер 2023
- 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...
Exactly what I was looking for. Thanks!
Great to hear that 😀
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.
THIS IS EXACTLY WHAT I WAS LOOKING FOR. THANK YOU!
😬
Welcome! 😀
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! 😀
Exactly what I'd been looking for 2 days!! Grateful this shorts popped up on my feed😊
Glad I could help! 😀
Thank you, just what I was looking for
That's good to hear, @tommyharris5817 😀
Thank you so much! I appreciate you getting right into it which helps me to keep working :)
You are so welcome, @SaritaMoreno! 😀
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"))
Thank you. Exactly what I'd been looking for.
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.
Just exactly what I need. Thanks a lot.
Great to hear, Minh! 😀
Oh heck!! I wanted something like this the other week!!! Thank you
Big thanks. The video is short and super informative.
You're welcome, @senya1696! 😀
what if under company 1 there's another company on that that row, will automatically add rows?
your smile makes me happy
This is amazing
I'm using office 2016 which does not have filter option. What is the alternative to it?
This video is very helpful. Thank you.
Glad it was helpful! 😀
Amazing! Thank you.
Thank you too! 😀
This was really helpful thankssss
Glad it helped! 😀
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?
Thanks a lot
Thanks lot 😊
Welcome 😊
👏👏👏
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 a lot.. amazing
Most welcome 😊
thanks 👍👍
Welcome 👍
Amazing thank you sir for your efforts
All respects
Thanks, Youcef! :)
Found this really handy. But is there a way to copy the filter column to rest of s/sheet?
Dont have a filter function in my excel. What should i do?
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
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?
Is there a way to automate this for each unique value in the company and return concanated values, to split into separate columns later.
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. ua-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
Is this function available for Excel 2013
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. ua-cam.com/video/irR9bUKzTw8/v-deo.html
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 i get this in rows instead of column?
Hi , what if i require the dropdown list ? Kindly explain
Is this doable in google sheets?
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
Filter is epic but doesn't work on older versions of excel
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.
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.
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. 🙂
I dont have this fuction "=filter" in my Microsoft Excel. What should I don?
How to use if you add more data?
What if I what to look 2 criteria to return 1 value?
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 to ignore the empty cell meaning don't import them to my list ?
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! 😀
Which screen recording tool you have used for this?
iPhone 12 Pro
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!
When I put "not found" it doesn't work.... I don't understand
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.
Tried on Google sheet, but it won't work :')
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 😊😊😊😊😊
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.
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! 🙂
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
only in office 365
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!
Rip excel 2016 users