MS Excel LOOKUP Formula: Return Multiple Values
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...
Пікірлер: 112
You have no idea how your awesome video removed hours of anxiety and fruitless formula building. I am so grateful to you! God Bless! :)
@ExcelCampus
Ай бұрын
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.
@justincarrencerahardjo1671
2 ай бұрын
Thank you!!!
@laurencsc8393
Ай бұрын
Hero!!! Thank you!
@walterjohnson8192
21 күн бұрын
If you want to make these values comma separated, etc. The TEXTJOIN function is the easiest way.
Exactly what I was looking for. Thanks!
@ExcelCampus
18 күн бұрын
Great to hear that 😀
THIS IS EXACTLY WHAT I WAS LOOKING FOR. THANK YOU! 😬
@ExcelCampus
8 ай бұрын
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.
@ExcelCampus
Жыл бұрын
Glad to hear that, Venkataramu! 😀
Exactly what I'd been looking for 2 days!! Grateful this shorts popped up on my feed😊
@ExcelCampus
5 ай бұрын
Glad I could help! 😀
Thank you, just what I was looking for
@ExcelCampus
Жыл бұрын
That's good to hear, @tommyharris5817 😀
Oh heck!! I wanted something like this the other week!!! Thank you
Thank you so much! I appreciate you getting right into it which helps me to keep working :)
@ExcelCampus
10 ай бұрын
You are so welcome, @SaritaMoreno! 😀
Been looking for this. Thanks a lot!
@ExcelCampus
11 ай бұрын
Awesome, thanks! 🙂
This is amazing
Thank you. Exactly what I'd been looking for.
Just exactly what I need. Thanks a lot.
@ExcelCampus
Жыл бұрын
Great to hear, Minh! 😀
your smile makes me happy
Big thanks. The video is short and super informative.
@ExcelCampus
Жыл бұрын
You're welcome, @senya1696! 😀
Excellent instruction, thanks. PS if you did a separate video with the complex functions as well, that would be also very helpful.
what if under company 1 there's another company on that that row, will automatically add rows?
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"))
Amazing! Thank you.
@ExcelCampus
9 ай бұрын
Thank you too! 😀
This was really helpful thankssss
@ExcelCampus
8 ай бұрын
Glad it helped! 😀
Thanks a lot
This video is very helpful. Thank you.
@ExcelCampus
5 ай бұрын
Glad it was helpful! 😀
Thanks lot 😊
@ExcelCampus
8 ай бұрын
Welcome 😊
thanks 👍👍
@ExcelCampus
10 ай бұрын
Welcome 👍
I'm using office 2016 which does not have filter option. What is the alternative to it?
Thanks a lot.. amazing
@ExcelCampus
5 ай бұрын
Most welcome 😊
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?
Amazing thank you sir for your efforts All respects
@ExcelCampus
Жыл бұрын
Thanks, Youcef! :)
👏👏👏
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?
Found this really handy. But is there a way to copy the filter column to rest of s/sheet?
Is there a way to automate this for each unique value in the company and return concanated values, to split into separate columns later.
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?
Question: how would you then aggregate all those values in one cell seperated by comma's? Thx for the tutorial
@adamweeks5005
9 ай бұрын
Would love to know if you figured this out? :)
@MrGillyWilli
9 ай бұрын
used power query instead 😅@@adamweeks5005
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?
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.
@lukmanaliyu7386
11 ай бұрын
How can one wrap it in a cell?
@dharmmu
11 ай бұрын
@@lukmanaliyu7386He had only replied on the same also. Later I saw that here. kzread.infotnK3cnrxbrk?si=pwYxZFI6MSsMzDcJ
@TrangNguyen-lk7mi
4 ай бұрын
Do you have any solution to wrap result in one sell
@dharmmu
4 ай бұрын
@@TrangNguyen-lk7miI think you do that with wrapping the same formula under =textjoin function.
@TrangNguyen-lk7mi
2 ай бұрын
@@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
Dont have a filter function in my excel. What should i do?
Can i get this in rows instead of column?
Hi , what if i require the dropdown list ? Kindly explain
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?
@ExcelCampus
Жыл бұрын
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! 🙂
@peevee2552
Жыл бұрын
Exactly, I needed the same! :) to prevent data overwrite..
@user-um5sv4rx4r
Жыл бұрын
I was needing the exact same thing. I came across this video. Hope it helps. kzread.info/dash/bejne/m6aGm8SOe926p5s.html
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
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.
Is this doable in google sheets?
What if I what to look 2 criteria to return 1 value?
Filter is epic but doesn't work on older versions of excel
How to use if you add more data?
My excel is probleming when i write this formula they showing error
I dont have this fuction "=filter" in my Microsoft Excel. What should I don?
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
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
@ExcelCampus
3 ай бұрын
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. 🙂
How to ignore the empty cell meaning don't import them to my list ?
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.
@Mikej1592
2 ай бұрын
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.
@Mikej1592
2 ай бұрын
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.
When I put "not found" it doesn't work.... I don't understand
Which screen recording tool you have used for this?
@SithLord2066
Жыл бұрын
iPhone 12 Pro
How do put the output in different columns instead of rows?
@ExcelCampus
6 ай бұрын
Great thought, Arpit! Wrap the formula in TRANSPOSE(). Something like =TRANSPOSE(FILTER(I2:I22,G2:G22=B2,"Not Found")) should work. Hope that helps!
I got #N.A with the same sample that you did. Do you know why? it says the missmatched range
@ExcelCampus
6 ай бұрын
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! 😀
Tried on Google sheet, but it won't work :')
In Google Sheets, this doesn't work. =FILTER(condition 1, conditino 2, cond... cond.,.......)
there is no filter function excel 2019?
@ExcelCampus
6 ай бұрын
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.
Not in Excel2019?
@ExcelCampus
Жыл бұрын
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
Hi, How to get multiple lookup value in single cell ?
@ExcelCampus
6 ай бұрын
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! 😊
@rupeshthakur9094
6 ай бұрын
Hi. Can you suggest alternate formula of TEXTJOIN , bcz in microsoft excel 2013 TEXTJOIN formula not available.
That formula is good, but all i need is the fisrt and last entry. Please help 😅
@osoriomatucurane9511
Ай бұрын
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 😊😊😊😊😊
only in office 365
Can we not use pivot table instead?
@ExcelCampus
Жыл бұрын
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! 🙂
@abhibarua5437
Жыл бұрын
@@ExcelCampus Thanks for the detailed reply!
Rip excel 2016 users