TECH-002 - Find a value in intersecting rows and columns in Excel
YOU CAN DOWNLOAD ALL MY FILES FOR FREE AT www.theexcelchallenge.com
Learn how to create a spreadsheet that will allow you to easily find intersecting values between rows and columns.
In this video you will use the formulas MATCH, INDEX, CONCATENATE, you will use Conditional Formatting, you will create Dropdown menus by using VALIDATION in the form of LIST, you will learn how to remove redundant cells and formulas, etc. etc.
I hope this tool is something you can use in your daily routine so you get more with less effort.
If you have a challenge with Excel that you need help with, whether it is at work, at home, or at school, please write it in the comments below. I try to resolve the most common Excel challenges from our viewers and subscribers.
Thank you for watching.
Check out our previous video in the links below:
* TECH-001 - Compare data in charts selecting values from dropdown menus
• TECH-001 - Compare dat...
Пікірлер: 63
Thanks a ton. I was stuck with a problem for past many hours, you sorted it out easily. Loved the way you explained. Cheers
Thanks so much for breaking it down in such a brilliant way!! Best tutorial ever for Index Match...
beautifully simple! Thank you!
Thanks, you make it easy for me to learn. Keep going
Thank you. That was very helpful.
Thank you, thank you for this tutorial!
I was stuck and now making some progress. Thanks. Great video.
Amazing! I was wondering how to find for one "X" , a value which is having multiple "y" values. This video helped me out. Thanks!
Very well simplified. Thanks.
Man! This was a saver. Thanks a lot.
Thank you for blowing my mind again :D
Very good, easy to understand tutorial
Thank you!! Subscribed.
thank you for this its very clear
And if wanna have "Intersection" formatted with specific color you can add one more formula in conditional format using =AND(from city = Atlanta, to = Atlanta) - with proper referencing. Great videos, its helpful tough.
excellent !!
MASSIVE THANK YOU!!!
Thank you so much brother!
Wow that's a complete explanation. I'm a loyal fan now.
@TheExcelChallenge
6 жыл бұрын
LOL. Thank for the loyalty Norman. It's encouraging to hear you liked the explanation. T-E-CH
Really very useful. Thanks
Hey, thank you very very much, you have solved my problems, great
Thanks you have solve my most pending problem...thanks once again. God bless You.
the explanation id so clear .. this really helped me , THANK YOU FOR BEING THERE FOR US :)\
@TheExcelChallenge
6 жыл бұрын
Hello Sabu. It is very nice to hear that my video helped you. To help others is the whole reason for me to produce and to post videos. Thanks for watching. T-E-CH
Thank you!!!
Good work.Thanks
@TheExcelChallenge
5 жыл бұрын
Thank you Syed. Glad to hear you liked my work. T-E-CH
great thanks 🎉
Thank you it is very useful to me useful information and professional explanation I would like to ask about the presentation software used in the explanation
Best explanation given. Gj
It is Great 😊💓💓
Awesome
Is there a way to only highlight the intersecting cell instead of the whole row or column around it?
+1 subscriber
What if i want to have 2 tables for the lookup array? I don’t know how to fix this
you are a magician (Y)
@TheExcelChallenge
7 жыл бұрын
Thanks Munawar. Hope you found it helpful.
hi, how do I find the column name for a value in a table? So I have these sets of scores on a column, and I wanna add the name of the column that the score is from to the cell next to it. How do i do that?
I have a matrix which is a result of a pivot table and I need to look up the value in an intersection and put it in another spread sheet (for all the values) which will be used for mail merge spread sheet. Your video is very clear for 1 intersection. How do I populate the entire mail merge spread sheet with the data from the pivot table? Thanks!
@TheExcelChallenge
3 жыл бұрын
Hello Mike. Thanks for sharing this challenge. If you would like, I can help you with your sheet. Email me at theexcelchallenge@gmail.com and we’ll communicate there. Remove any confidential information. I don’t need your data. All I need is the structure you have and the structure you need. I’ll be looking out for an email from you. Thanks for watching T-E-CH
@mikesison3768
3 жыл бұрын
I think the simple way of putting it, say in your example is.... how do I find the formula for each distance in each intersection.
@mikesison3768
3 жыл бұрын
So, in your example, I would use =INDEX(I3:W17),MATCH(H3,H3:H17,0,I3) to get the blank. Do I just fill up the rest of the spread sheet one by one? Thanks again.
What is the best formula(s) and how to use it, which can select multiple cells and show (with color) in a large excel file that contains financial data "between value", example: all cells between 31,950 to 32,020, then those between 39,450 to 40,100 in the same file.
Hello nice explanation. My question is: given a value, how can I have excel find the closest value in a defined matrix and then Have excel tell me what row and column it’s located in?
@biswadipghosh6958
Жыл бұрын
Same problem I am facing .. you found out any ?
Thanks a lot , but I have some problem my coloumn range contains values like 20,40,60 with a difference of 20 but I need to find intersect having decimal values like 20.1,20.2,20.4......., if I put all data in column it becomes a huge table . Can you help me to find intersect between the two values in column like between 20 and 40 so on.
Thank you. this was a very helpful video. I have a question, i need help with, would you be able to help me please. this has to do with event scheduler that has the (day(row 1) and date(row 2)) on the column and the hours(15min interval) in the row. I want to be able to insert a data by selecting day or date vs time and fill in the intersection with info. I would appreciate your help. If I can provide you my email please let me know, i will temporarily post it upon your reply. thank you.
Thanks alot for this video but may I ask how do we inverse this process? as if I enter "129", then it would return me "Driving from Philadelphia to new york is 129 miles"? Thanks again
Any idea, how can i retrieve the column name.. let's say i want to find which city if the most distant from Boston? I would really appreciate it. Thanks guys
How would one go about creating a chart; whether pie, bar etc. to show the difference between the cities? I am currently working on a similar project and would like some assistance please! I am having issues finding a visual aid that would show for example in this instance: "the distance traveled between Chicago and Atlanta is 944 miles." #HELP!
@TheExcelChallenge
4 жыл бұрын
Hello Deanna-Kaye. You can use the formula foundation that I show in the video, and from there, play with formatting, i.e. larger font, colored cell background, colored font, etc. in order to highlight the features you want to highlight. If you have more questions, you can ask them here or you can email me at theexcelchallenge@gmail.com Thanks for watching! T-E-CH
i need to do this same formate but with time, how do i go about doing that?
@TheExcelChallenge
4 жыл бұрын
Hello Mai, Select all the cells where you have numbers and right-click on them. Find the Cell Format option and once the window opens up, select a time format. Repeat the exercise as I show it in the video and that should do the job. Thanks for watching. T-E-CH
8:00 is the magic
how would this be done in Google Sheets?
Cool Stuff. I need some help. I've got an employee schedule. Rows: B15:B100 has the employee names. Which match the Application.UserName Row: 2 has the Date: mm/dd/yyyy What I'm trying to do is when a given employee opens up the workbook, the sheet will activate to: Today's Date Employees Row. I'm able to do the above as individual functions but not together. For date: I found some code that does a .find on CLng(date) and then once it finds the cell it does the application.goto cell For the employee name: I just set the range: B15:B100 and if value = Application.Username then I go to that cell. I just can't get them to work together.
It looks like that function only look for an max of 15 arros cells and 15 columns cells, i does give me an error #Ref :\
can you send me the file
@TheExcelChallenge
6 жыл бұрын
Hello Vinay. I just sent you the file. Check your inbox and if it isn't there, try your spam folder. It may have gone there. Thanks for watching.
Please tell me " The cell address of the cell made by the intersection of D column and 7th rows is ________
If i want the opposite one ? Select cell and get the name given nearest to the row and column