MS Excel LOOKUP Formula: Return Multiple Values

Рет қаралды 365,911

Жыл бұрын

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

Пікірлер: 114

@user-tv6rl4yt6f 6 ай бұрын

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

@lucasmatsuoca 3 ай бұрын

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 23 күн бұрын

If you want to make these values comma separated, etc. The TEXTJOIN function is the easiest way.

@Maya-vb9sj 4 сағат бұрын

Thank you guys, I really needed this

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

@highwayempire1 8 ай бұрын

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

@ExcelCampus 8 ай бұрын

Welcome! 😀

@walterjohnson8192 23 күн бұрын

Exactly what I was looking for. Thanks!

@ExcelCampus 21 күн бұрын

Great to hear that 😀

@tommyharris5817 Жыл бұрын

Thank you, just what I was looking for

@ExcelCampus Жыл бұрын

That's good to hear, @tommyharris5817 😀

@fanp1188 Жыл бұрын

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

@SaritaMoreno 10 ай бұрын

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

@ExcelCampus 10 ай бұрын

You are so welcome, @SaritaMoreno! 😀

@elymX 7 ай бұрын

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

@stephyjames7169 5 ай бұрын

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

@ExcelCampus 5 ай бұрын

Glad I could help! 😀

@minhhieubui8557 Жыл бұрын

Just exactly what I need. Thanks a lot.

@ExcelCampus Жыл бұрын

Great to hear, Minh! 😀

@jester5027 11 ай бұрын

Been looking for this. Thanks a lot!

@ExcelCampus 11 ай бұрын

Awesome, thanks! 🙂

@shadowcat6lives639 Жыл бұрын

This is amazing

@suppoo7 3 ай бұрын

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

@antoniobacic5674 5 ай бұрын

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

@senya1696 Жыл бұрын

Big thanks. The video is short and super informative.

@ExcelCampus Жыл бұрын

You're welcome, @senya1696! 😀

@kmonnier 5 ай бұрын

your smile makes me happy

@mcabreli Ай бұрын

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"))

@gtoley1 9 ай бұрын

Amazing! Thank you.

@ExcelCampus 9 ай бұрын

Thank you too! 😀

@mainulhossain76 Жыл бұрын

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

@Shiiies 8 ай бұрын

This was really helpful thankssss

@ExcelCampus 8 ай бұрын

Glad it helped! 😀

@abdulrahmanmuslih141 Жыл бұрын

Thanks a lot

@primemateplastic8407 5 ай бұрын

This video is very helpful. Thank you.

@ExcelCampus 5 ай бұрын

Glad it was helpful! 😀

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

@whitestonemusic777 8 ай бұрын

Thanks lot 😊

@ExcelCampus 8 ай бұрын

Welcome 😊

@Thetiredman 10 ай бұрын

thanks 👍👍

@ExcelCampus 10 ай бұрын

Welcome 👍

@YoucefYoucef-ft9ce Жыл бұрын

Amazing thank you sir for your efforts All respects

@ExcelCampus Жыл бұрын

Thanks, Youcef! :)

@jadabkumarnayak7435 5 ай бұрын

Thanks a lot.. amazing

@ExcelCampus 5 ай бұрын

Most welcome 😊

@MrGillyWilli 10 ай бұрын

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

@user-fk6cw1sg3x 9 ай бұрын

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?

@dharmmu 11 ай бұрын

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

@JenniferMccarthy-ff8jk Жыл бұрын

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

@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. kzfaq.info/get/bejne/n9iCbMWGsOC4p2w.html

@High_Goblin_King 10 ай бұрын

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?

@Kylee_Meera 2 ай бұрын

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

@AyadoooKK 3 ай бұрын

👏👏👏

@allanongful 4 ай бұрын

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

@YuvarajRokkam Жыл бұрын

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

@suganpal1142 7 ай бұрын

Can i get this in rows instead of column?

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

@citaahmat Күн бұрын

My office 2019 not have filter formula 😢

@nitishagarwal9108 3 ай бұрын

Is this function available for Excel 2013

@Mikej1592 2 ай бұрын

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.

@ryecell 3 ай бұрын

Is this doable in google sheets?

@andresflorespineda6286 Жыл бұрын

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

@ai_is_a_great_place Жыл бұрын

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

@stevieakien2898 3 ай бұрын

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

@Mayor001_ 5 ай бұрын

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

@irltung 4 ай бұрын

How to use if you add more data?

@being.explorer 6 ай бұрын

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!

@HelaluzzamanAyon Жыл бұрын

Which screen recording tool you have used for this?

@SithLord2066 Жыл бұрын

iPhone 12 Pro

@heart2heart941 5 ай бұрын

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

@LyonJerry 6 ай бұрын

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

@abdellatifboudelal167 11 ай бұрын

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

@user-yu7mk2xr4g 6 күн бұрын

My excel is probleming when i write this formula they showing error

@Care-for-health-with-taste 7 ай бұрын

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

@ANKITSINHA1 6 ай бұрын

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.

@rupeshthakur9094 6 ай бұрын

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.

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

@InKY09 5 ай бұрын

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.

@ratulmitra347 2 ай бұрын

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

@melcruz2840 9 ай бұрын

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

@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-kn2rb4cs6v 5 ай бұрын

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

@user-yz9io3iv7n 5 ай бұрын

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

@user-tl9si7mp9f 4 ай бұрын

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