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...

[@user-tv6rl4yt6f]

You have no idea how your awesome video removed hours of anxiety and fruitless formula building. I am so grateful to you! God Bless! :)

[@lucasmatsuoca]

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]

Thank you!!!

[@elymX]

what if under company 1 there's another company on that that row, will automatically add rows?

[@srisharadasamskruthikasangha]

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! 😀

[@stephyjames7169]

Exactly what I'd been looking for 2 days!! Grateful this shorts popped up on my feed😊

[@ExcelCampus]

Glad I could help! 😀

[@tommyharris5817]

Thank you, just what I was looking for

[@ExcelCampus]

That's good to hear, @tommyharris5817 😀

[@SaritaMoreno]

Thank you so much! I appreciate you getting right into it which helps me to keep working :)

[@ExcelCampus]

You are so welcome, @SaritaMoreno! 😀

[@highwayempire1]

THIS IS EXACTLY WHAT I WAS LOOKING FOR. THANK YOU! 😬

[@ExcelCampus]

Welcome! 😀

[@NK-vw4ms]

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?

[@suppoo7]

Thank you. Exactly what I'd been looking for.

[@antoniobacic5674]

Excellent instruction, thanks. PS if you did a separate video with the complex functions as well, that would be also very helpful.

[@jester5027]

Been looking for this. Thanks a lot!

[@ExcelCampus]

Awesome, thanks! 🙂

[@fanp1188]

Oh heck!! I wanted something like this the other week!!! Thank you

[@kmonnier]

your smile makes me happy

[@mainulhossain76]

I'm using office 2016 which does not have filter option. What is the alternative to it?

[@shadowcat6lives639]

This is amazing

[@minhhieubui8557]

Just exactly what I need. Thanks a lot.

[@ExcelCampus]

Great to hear, Minh! 😀

[@senya1696]

Big thanks. The video is short and super informative.

[@ExcelCampus]

You're welcome, @senya1696! 😀

[@Kylee_Meera]

Is there a way to automate this for each unique value in the company and return concanated values, to split into separate columns later.

[@gtoley1]

Amazing! Thank you.

[@ExcelCampus]

Thank you too! 😀

[@primemateplastic8407]

This video is very helpful. Thank you.

[@ExcelCampus]

Glad it was helpful! 😀

[@allanongful]

Dont have a filter function in my excel. What should i do?

[@Shiiies]

This was really helpful thankssss

[@ExcelCampus]

Glad it helped! 😀

[@abdulrahmanmuslih141]

Thanks a lot

[@whitestonemusic777]

Thanks lot 😊

[@ExcelCampus]

Welcome 😊

[@user-fk6cw1sg3x]

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?

[@Thetiredman]

thanks 👍👍

[@ExcelCampus]

Welcome 👍

[@MrGillyWilli]

Question: how would you then aggregate all those values in one cell seperated by comma's? Thx for the tutorial

[@adamweeks5005]

Would love to know if you figured this out? :)

[@MrGillyWilli]

used power query instead 😅@@adamweeks5005

[@JenniferMccarthy-ff8jk]

Found this really handy. But is there a way to copy the filter column to rest of s/sheet?

[@jadabkumarnayak7435]

Thanks a lot.. amazing

[@ExcelCampus]

Most welcome 😊

[@High_Goblin_King]

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?

[@nitishagarwal9108]

Is this function available for Excel 2013

[@suganpal1142]

Can i get this in rows instead of column?

[@YoucefYoucef-ft9ce]

Amazing thank you sir for your efforts All respects

[@ExcelCampus]

Thanks, Youcef! :)

[@HarshMelon]

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. kzbin.info/www/bejne/n6O1apWLgN-HrZo

[@AnthonyHaywood]

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?

[@YuvarajRokkam]

Hi , what if i require the dropdown list ? Kindly explain

[@Mayor001_]

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

[@ryecell]

Is this doable in google sheets?

[@AyadoooKK]

👏👏👏

[@dharmmu]

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]

How can one wrap it in a cell?

[@dharmmu]

​@@lukmanaliyu7386He had only replied on the same also. Later I saw that here. kzbin.infotnK3cnrxbrk?si=pwYxZFI6MSsMzDcJ

[@TrangNguyen-lk7mi]

Do you have any solution to wrap result in one sell

[@dharmmu]

​@@TrangNguyen-lk7miI think you do that with wrapping the same formula under =textjoin function.

[@TrangNguyen-lk7mi]

​@@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

[@ChrisThompson-ft7xd]

Why is this so narrow? Is vertical video helping here?

[@heart2heart941]

I dont have this fuction "=filter" in my Microsoft Excel. What should I don?

[@Mikej1592]

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]

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]

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.

[@irltung]

How to use if you add more data?

[@stevieakien2898]

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]

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. 🙂

[@andresflorespineda6286]

What if I what to look 2 criteria to return 1 value?

[@Care-for-health-with-taste]

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

[@ratulmitra347]

When I put "not found" it doesn't work.... I don't understand

[@melcruz2840]

That formula is good, but all i need is the fisrt and last entry. Please help 😅

[@LyonJerry]

I got #N.A with the same sample that you did. Do you know why? it says the missmatched range

[@ExcelCampus]

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! 😀

[@being.explorer]

How do put the output in different columns instead of rows?

[@ExcelCampus]

Great thought, Arpit! Wrap the formula in TRANSPOSE(). Something like =TRANSPOSE(FILTER(I2:I22,G2:G22=B2,"Not Found")) should work. Hope that helps!

[@ai_is_a_great_place]

Filter is epic but doesn't work on older versions of excel

[@HelaluzzamanAyon]

Which screen recording tool you have used for this?

[@SithLord2066]

iPhone 12 Pro

[@abdellatifboudelal167]

How to ignore the empty cell meaning don't import them to my list ?

[@user-yz9io3iv7n]

Tried on Google sheet, but it won't work :')

[@user-kn2rb4cs6v]

In Google Sheets, this doesn't work. =FILTER(condition 1, conditino 2, cond... cond.,.......)

[@user-tl9si7mp9f]

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

[@rupeshthakur9094]

Hi, How to get multiple lookup value in single cell ?

[@ExcelCampus]

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]

Hi. Can you suggest alternate formula of TEXTJOIN , bcz in microsoft excel 2013 TEXTJOIN formula not available.

[@ANKITSINHA1]

there is no filter function excel 2019?

[@ExcelCampus]

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.

[@ajnaspp211]

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! 🙂

[@abhibarua5437]

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!

[@user-hm7ss9ol9l]

only in office 365

[@InKY09]

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.

[@ChrisThompson-ft7xd]

There is no LOOKUP formula mentioned here wtf

[@chrisanthony1194]

Rip excel 2016 users