For more videos that cover more lookup concepts see the playlist at kzread.info/head/PL-n8f1cY_Qw95JHWjCjPofsCd7hCWjI8l

@DougHExcel

4 жыл бұрын

...

This did not just help.....it saved a life..thanks so much Doug H....

@DougHExcel

3 жыл бұрын

Glad it helped!

Lord have mercy, Doug! I don't have time to watch multiple long advertisements, just to find out if this particular video is going to answer my question! Even if it does answer my question, that's too much advertising time! Sheeeez. At least let me opt out of the ads after 30 seconds!

This tutorial was extremely useful to me because of my work as a business controller. I work with big loads of data and every now and then I need to bring data from other tables and in most cases I will get duplicates if I don't do what you just taught me, and you even gave alternative ways in a 10 minute tutorial! Amazingly useful and effective tutorial. Yet again, thanks a lot Doug. P.S. I am now officially a contextures.com subscriber :-)

@DougHExcel

7 жыл бұрын

Hi Alex Rosén, glad you liked it, thanks for commenting! Debra at contextures is great!

Omg! It was that simple! Thank you so much for this video and for explaining how this works so clearly. 👍

@DougHExcel

5 жыл бұрын

Thanks!

very good vid! clear and well-thought detailed instructions! cheers

@DougHExcel

6 жыл бұрын

+Zhao Zhang thanks....glad u liked!

Very helpful for me. Thanks a lot for such unique function.

@DougHExcel

5 жыл бұрын

Thanks Muhammad Ahmad, glad it helped!

This is Great..I love excel videos from this man

@DougHExcel

6 жыл бұрын

Hi Pascal Torvic, glad you liked it, thanks for commenting!

Solved a MASSIVE headache for me. It took me far too long to find it because I had to learn the limitations of VLOOKUP before I could even understand how to entertain this type of solution. Excellent explanation, wonderful video. Thank you, thank you, thank you!

@DougHExcel

4 жыл бұрын

Thanks Nateamus, glad it helped!

This was a tremendous help! Thank you!!!!!

@DougHExcel

6 жыл бұрын

You're Welcome!

Very well presented and explained !!!

@DougHExcel

5 жыл бұрын

Hi Neeraj Kumar Gupta, glad you liked it, thanks for commenting!

Thanks for this simple explanation

@DougHExcel

4 жыл бұрын

You're Welcome!

Thank you! It's really helpful

@DougHExcel

6 жыл бұрын

You're welcome!

Thank you very much for this tutorial :)

@DougHExcel

6 жыл бұрын

You're Welcome!

Thank you, this helped me tremendously. Cheers.

@DougHExcel

Жыл бұрын

Thanks, glad it helped!

It's very important to me, very helpful, thks bro

@DougHExcel

6 жыл бұрын

Thanks Batbayar Nergui, glad it helped!

Thank you so much, this was very helpful.

@DougHExcel

5 жыл бұрын

Hi Tope Iyiola, glad you liked it, thanks for commenting!

Super clear, thanks for the guide!

@DougHExcel

4 жыл бұрын

Hi TheRoseFloyd, thanks for the comment!

literally was stuck for 3 hours, thank you! thank you very much!!

@DougHExcel

5 жыл бұрын

Thanks Azooz Syr, glad it helped!

Great solution. Thanks for the providing multiple solutions!! 👍

@DougHExcel

4 жыл бұрын

Hi SANUP VAIDYA, glad you liked it, thanks for commenting!

This video is what I have been looking for. Thank you very much very clear

@DougHExcel

5 жыл бұрын

Hi Andrew Scott, glad you liked it, thanks for commenting!

Excellent here the example is so simple and effective thanx alot

@DougHExcel

5 жыл бұрын

Thanks ibrahim ezz!

Great video. Thank you sooo much. i watched bunch of videos and yours was so into point and easy to understand. Now my problem solved. Keep up the great work

@DougHExcel

4 жыл бұрын

Glad it helped!

Thank you!! This help me so much

@DougHExcel

5 жыл бұрын

Thanks Dimas, glad it helped!

Amazing absolutely, you solved one of my biggest problem

@DougHExcel

7 жыл бұрын

Hi Vanshika Chawla, glad you liked it, thanks for commenting!

thanks allotted sir very useful

@DougHExcel

5 жыл бұрын

Hi Harinadh Sharma, glad you liked it, thanks for commenting!

Ahhhh! You went into spooky land with the array formulas. 😄 Great stuff!!!

@DougHExcel

6 жыл бұрын

Thanks Oz! yeh...i try not to get toooo deep with array formulas cause they make my mind melt😵

Thank you so much. That is what I was looking for.

@DougHExcel

5 жыл бұрын

Glad it helped

@sasavienne

5 жыл бұрын

Yes indeed. It is more than amazing. 🌟 🌟 🌟 🌟 🌟

Thank you sir after a long hours search,I got this video. I work for Transport industry which has different location with different prices. It is hectic job, I hope implementing you ideas may work. Thanks

@DougHExcel

6 жыл бұрын

Hi Tamim SAJID RASHEED, glad you liked it, thanks for commenting!

I've been searching for this solution for hours! Thanks so much. The only critique is that the ads were placed in really inconvenient spots

@DougHExcel

5 жыл бұрын

Thanks Jonny Henderson, glad it helped!

Thanks finally it help me alot

@DougHExcel

4 жыл бұрын

Glad it helped!

you saved me man thank you so much

@DougHExcel

5 жыл бұрын

You're Welcome!

Mind blown!

@DougHExcel

5 жыл бұрын

Hi Jason Jackson, thanks for the comment!

Great!!!!

@DougHExcel

5 жыл бұрын

Hi varuninnz, thanks for the comment!

I would've done it with advanced filters but it's always interesting to see how people approach these problems.

@DougHExcel

3 жыл бұрын

Hi Enny Kraft, thanks for the comment!

Thanks

@DougHExcel

5 жыл бұрын

You're Welcome!

Smart!

@DougHExcel

5 жыл бұрын

Hi ziyi zhou, thanks for the comment!

fantastic

@DougHExcel

5 жыл бұрын

Hi rishav chowdhury, thanks for the comment!

First tip with helper coloumn = pure magic!!!

@DougHExcel

5 жыл бұрын

Hi Jens Søgaard, thanks for the comment!

It worked! I am curious, when you entered "Match(1,"... exactly what is it that the "1" (row 1) is doing?

@DougHExcel

4 жыл бұрын

The "1" will try to match the number one with the product of the bunch of TRUEs and FALSEs. When the array of TRUEs and FALSEs are multiplied together it becomes an array of zeros or ones. The MATCH of the number one is looking at that array and when it matches, it will bring back the position number of where it matched.

How to use MAX fun. with including this formula I have table that (Text, Text, Date, Text, Number) how can i find the max size in number column with combination of INDEX Fun. used

@DougHExcel

3 жыл бұрын

maybe countif or maxif kzread.info/dash/bejne/o6B5x8l7eM-2fco.html kzread.info/dash/bejne/maiosLuvl8m3dbw.html

can a format cell applicable using this vlookup?pls answer this.

@DougHExcel

3 жыл бұрын

the output will determine the conditional formatting

Just one thing. I didn't understand why we add 1 in the match function??

@DougHExcel

6 жыл бұрын

when the multiplication of the columns occur in the 2nd argument of match it's TRUE/FALSE that essentially become 1s or 0s. The 1st argument in the MATCH function is looking up that number 1 to match those 1s or 0s.

great yaar

@DougHExcel

5 жыл бұрын

Hi +William Peter, thanks for the comment!

@DougHExcel

5 жыл бұрын

Hi +William Peter, thanks for the comment!

@DougHExcel

5 жыл бұрын

Hi +William Peter, thanks for the comment!

Index match is awesome

@DougHExcel

4 жыл бұрын

Yes it is!

Hi there, is there a way to get an output but only based on one of the criteria? lets say you only put in Green, how would it show you a list of all possible greens? I would want to use that in combination to bring up other columns as well, so if I type in green in the cell, it would show widget A small green and widget b medium green (in multiple rows if possible) showing ALL things that contain green? Thanks, I am trying to figure this one out myself.

@DougHExcel

6 жыл бұрын

For your situation, maybe try advanced filter kzread.info/dash/bejne/dYVpydCHd5PalbQ.html

Is there way to do this with two different formats of data? I want to lookup against a name and a date to return clock in / clock out data, but the data auto formats to an integer when combined..

@DougHExcel

6 жыл бұрын

when number value are combined with text, it should end up as a text string. See these other video for insight kzread.infosearch?view_as=subscriber&query=lookup+two

@waynestrydom6522

6 жыл бұрын

Thanks!

Thanks for your videos!! Would this work into a data validation list? What I'm trying to do is (using your data set as the example) list all of the various colors for WidgetB, size med in a drop down. Beyond that, let's consider that one of those (say the Blue WidgetB) was a large, not a medium. How would I go about doing this? What I would also like to do (if possible), is create size data validation list based on WidgetB, that removes any repeat answers. So, what I'm running into using an OFFSET formula, is that I'm getting the answers, but I would get this: [Med, Med, Large, Med] as my data validation list, when I would like to only get [Med, Large]. Thanks so much for your time!!

@DougHExcel

4 жыл бұрын

Hi Markus Winter - CDPS, sorry I don't do consulting. But this video might give some insight kzread.info/dash/bejne/dXuupsyjo9jHic4.html

I have oe problem when i use this method to look up 4 conditons. It says N/A when i look up for mobile phone. If i seperate the row that has problem to a new sheet and rewrite the array lookup, it matches

@DougHExcel

3 жыл бұрын

Sorry, don’t understand the comment/ question...

Wonderful, but i want to know why you put 1 in the lookup value in match equation.

@DougHExcel

7 жыл бұрын

The multiplication within the match functions returns a bunch of true/false which relates to 1s and 0s. We want to match the trues (1s) so that is why the number 1.

@amirsultan2877

7 жыл бұрын

Thank you very much. It is really helpful tutorial.

Is there a way to sum up the quantity after you the criteria has been identified? Let's say, we need to know the total quantity under multiple criteria - "Widget A" and "Small" as per your example, and add up the quantity.

@DougHExcel

7 жыл бұрын

See if this video gives some insights kzread.info/dash/bejne/jHqJxa6Lns-oqbw.html

sticking with your example is there anyway for me to lookup ALL widgetBs and have them listed in multiple rows so that you can see all of the data on all of the widgetBs no matter size?

@DougHExcel

5 жыл бұрын

there are several ways kzread.info/dash/bejne/YmF-l5iGqa3ZmLQ.html kzread.info/dash/bejne/qZegz6uchNbIpMo.html kzread.info/dash/bejne/lqyY1pRucrebnaQ.html

What if I want to look up the total quantity for ALL WidgetB’s? I’m trying to figure that out but having the hardest time in doing so!

@DougHExcel

5 жыл бұрын

Maybe this video will help kzread.info/dash/bejne/jHqJxa6Lns-oqbw.html

01-02-03-08-09-10-12-15-16-18-22-24​ 02-03-04-05-06-08-14-15-16-17-22-23​ 03-06-07-08-09-10-12-15-16-17-20-23​ 07-08-09-10-12-14-16-17-19-22-23-24 02-03-06-07-09-11-14-15-16-18-21-22​ 03-05-06-07-08-09-11-16-17-20-21-22​ Question: Is there an excel formula that I can use to find a duplicate of all 12 numbers chosen in a lottery database over a series of data collected from 2014 to 2018. Example: I want to see how many times the 12 numbers altogether in row 1 have been duplicated in the whole database. Question 2: Once i am able to find that, is there a way in one formula to have the second row be locate in the series and so on...

@DougHExcel

6 жыл бұрын

Seems like the pattern is number and dashes in your list so you could set up the lookup string as the number and then the dash with the repeats (i.e XX-XX-XX-XX-XX-XX-XX-XX-XX-XX-XX-XX). There a couple options you can use --> FIND function, SEARCH function or the FIND command. Here's some videos for insight kzread.info/dash/bejne/nq2l2qhvepWeos4.html kzread.info/dash/bejne/e4Ces8eJXcWuXbQ.html kzread.info/dash/bejne/nq2l2qhvepWeos4.html this one is FIND&REPLACE, but you select the button to find all

@cynthiacoc6400

6 жыл бұрын

thank youso much :))

I'm trying so hard to figure this out - but - I want to use drop down lists that I can choose that will show all Widgets, 2nd column to populate with sizes available on widget chosen, 3rd to populate colors available for widget/size combo and then result showing quantity, rather than allowing text input. Your video is the closest I've come to figuring that out - but I also wonder, is it possible for any output to be an image?

@DougHExcel

5 жыл бұрын

maybe one of these might help kzread.infosearch?view_as=subscriber&query=lookup+picture

hello, do you know a formula equivalent to CTRL + F? thanks

@DougHExcel

4 жыл бұрын

Try kzread.infosearch?query=search

I've a question. My columns are month, year and sales.I may have more than one Jan and more than one 2018. Let me put is this way, Widget C, Med, Green = 41, and there is this (fake), Widget C, Med, Green = 51. I need a formula to calculate the total of Widget C, Med, Green.

@DougHExcel

6 жыл бұрын

Have you tried the SUMIFS function? kzread.info/dash/bejne/g6yCyK-jo8vcf84.html

with the option 2 and using the INDEX function, I was not able to do this for multiple records. I tried to change the array and excel gave me an error. How to do this for multiple records. Your example only showed one record. Thanks.

@DougHExcel

4 жыл бұрын

power query may help kzread.infosearch?query=lookup+power+query

Why have u put 1 as a look up value in MATCH function

@DougHExcel

3 жыл бұрын

it's for an exact match

YOU solved it with 3 Criteria but how will we solve it when this are 4 Criteria? please help

@2023noone

5 жыл бұрын

you have to put four arguments instead of 3 in the array functions, i guess

@DougHExcel

3 жыл бұрын

This might give an idea kzread.info/dash/bejne/mK6asJptm6fXl7g.html

Use the DGET function. It's so much simpler.

@DougHExcel

5 жыл бұрын

Hi Rickey Nolte, thanks for adding to the thread! DGET is sometimes an unknown feature to folks and I've got a video here kzread.info/dash/bejne/YmF-l5iGqa3ZmLQ.html

so helpful! Thank you

@DougHExcel

4 жыл бұрын

You're Welcome!