ฝัง
- เผยแพร่เมื่อ 8 ต.ค. 2017
- In this tutorial video, I am going to demonstrate how to use Match/Index functions to perform a lookup based on a date value to look up a date range (between two dates) and return the corresponding column.
► Buy Me a Coffee? Your support is much appreciated!
------------------------------------------------------------------------------------------
☕ Paypal: www.paypal.me/jiejenn/5
☕ Venmo: @Jie-Jenn
💸 Join Robinhood with my link and we'll both get a free stock: join.robinhood.com/jiej6
► Support my channel so I can continue making free contents
---------------------------------------------------------------------------------------------------------------
🌳 Becoming a Patreon supporter: / jiejenn
🛒 By shopping on Amazon → amzn.to/2JkGeMD
🗓 Get updated on new Python videos → / madeinpython
📘 More tutorial videos on my website → LearnDataAnalysis.org
📺 Also check out my 2nd channel Excel channel focus on sharing Excel tips: bit.ly/3B1DjSA
✉ Business Inquiring: TH-cam@LearnDataAnalysis.org
#Excel #ExcelLookup #ExcelTip
![[TH] Esports World Cup : Knockout Stage Day 1](http://i.ytimg.com/vi/ES-vr-FarFg/mqdefault.jpg)
Not just saved my day, you saved my weeks. Much appreciated.
Thank you so much! This helps me determine which quarter a date falls into. SO helpful.
and all this time I was stacking if formulas inside each other, one for every row in the reference. THIS IS SO MUCH BETTER!! THANK YOU!
Thank you! I hadn't needed to do this in a while and really appreciate this helpful explanation! I needed to add another column to the array, but once I had the dates figured out, the additional criteria was simple.
Thanks. This was the one and only answer to my question on the web.
This formula is exactly what I was hoping to find!, You are the best!
This was extremely helpful. Thank you!
This formula is epic, thank you!!
Dude. I've literally been trying to figure out this problem all day! THANK YOU!!
My processors are going to be crying over my 240,000 lines for the next two hour lol 😆
Thank you for your clear and thorough demonstration - this is exactly what I needed to do with my data!
Glad my video helped.
Great video!! Just what i was looking for
Can't thank you enough, this is exactly what I was looking for, thank you!
Very useful, step by step. Thank you.
This is exactly what I wanted and you have helped me BIG TIME!! BIG THANKS :)
THANK YOU!! You just saved me so much time on my report!
Thanks a lot. I never knew this formula. This is actually very helpful in my reporting at work. Keep posting such useful videos.
Glad the video helped.
This is excellent! Thank you! I've looked at videos for two hours trying to figure this out. You explained it quickly and easily. I didn't understand why you put curly brackets on the formula at the end because it didn't work, but when I took off the curly brackets then it worked. Is there a purpose for the curly brackets?
I think because newer version of office doesn't require curly bracket anymore.
great job
Thanks man!!
You're my saviour!
you are a genius thank you so much
Big help!!! Thank you!
You absolute legend, thank you :)
thank you for the amazing help !
Perfect!!!!!!
You are a life saver!
Hi Jie,
Thank-you
This was very helpful!
Glad my video helped.
Perfect, thank you
Great video helped me solve by issue
very helpful, great job!
I don't understand lookup value 1 in match function. Could you please help me explain?
Thanks this was just what I wanted!
Glad my video helped.
Thank you sooooooo much, really helped me.
I used range names and did not have to use C+S+enter , did not realize until I asked my self, Wait, where did I have to use C+S+ enter ? . 1000 thanks.👍🏻👍🏻👍🏻
Thanks for your help. I want to do a calculation once I find a Match. do you have any video about that? So, once I locate the date fall into the date range, I would like to know the number of days between the 2 days
Thank you.
Unreal - huge help
Thank you so much!!!
Thank you for the amazing video! But I have one question! What if i have to include a criteria in this formula? Like: i need to search an ID in the range between two dates? What i have to do?
Genius
I have a calendar and need to find one/range of date/s, please advise, thanks
I miss why it is “1” in the formula..? Would you please explain?
I need one help..this is superb formula..what if I have different ranges for different number/I’d..example..1234 have range jan to feb and 3456 have range from mar to apr..the formula should first match with the number and then its range and throw the value..can u guide me
What format in the date are you using?
Hi Elizabeth, the date format in my example is "m/dd/yyyy". However, any date format should work. In column C, they are just regular texts
Hey with this formula it gives only one data, if I have multiple data to match than how to find?
Thanks for this Formula, I have a question about this
Can we do this formula with multiple criteria, because I have tried it but always show ""NA"
could you please help me out with this
Not at the moment, time is tight.
please how to add another criteria ?
Is there a way to use this formula to pull the same information but if the date format in the lookup table is set to for example instead of 04/15/2017, it is April 15, 2017. I tried it and it is not working. thanks for your help
Send me your Excel file to TH-cam@LearnDataAnalysis.org for me to take a look at it.
Is there a way to use this in excel 365?
The formula should also works on Excel 365.
@@jiejenn thank you!!
I've tried your formula but it doesn't work. I've even created your spreadsheet hoping it will work but still doesn't. Any suggestions?
If you send me your Excel file to jiejenn@businessanddata.org I can probably take a look.
I also tried. But still not working. Pls assist.
You have to use the 'Control + Shift + Enter' to get the array formula to return the correct figure. I missed that step and it didn't work by typing it in.
Great! y u used 1 in match formula
1 represent True value, meaning a non empty valhe.
why do you put 1 under the MATCH?
Shouldn't the MATCH function read =MATCH(1,IF($E3>=$A$3:$A$7,IF($E3
If your end date overlaps with a start date, then greater sign is correct. Otherwise, you want to use
If i put a 1 in my match function, excel tryes to look for 1.
Not following. What is the exact error message you got?
Jie Jenn Thanks for reply. I figured it out. I have a danish version of excel. Here the match formula wont accept 1 or 0 as loopup input. I have to type either true or false. In this case i must type “True” where you put a 1.
How do I message you?
Hi there. My email is in the video description.
Oops. My bad. Thanks
I followed this precisely and it didn't work... ugh
Cant see your formula
Other people can see the formula just fine. Maybe your monitor is too small.
@@jiejenn can I get that formula sir in msg or mail pls
@@jiejenn thanks for ur valueable reply sir