You are a genius! Seriously. I would have thought initially that INDEX wasn't the right function for what I was trying to achieve. But Voila! Watching the tutorial made perfect sense, was easy to follow, and got me the result I was looking for.
Thank you, works like a damn charm and even though what I was trying to figure out was slightly different, these steps actually got me on the way to figuring out my problem.
Absolutely amazing teacher. I have went all over looking for information and this channel is the absolute best!! Google should hire you all for it's learning labs. The only thing that would be awesome is if you could include your final formula so I could copy and paste it into my own and adjust from there. But maybe people wouldn't watch it until the end and that would be no good for the channel. but thanks again!!
This was so brilliant!!! Thanks so much. Now I have an issue with sorting static and dynamic data on a tab. Could you please do a tutorial on this as well?
Yes, by either clicking the sheet and select dragging the cells, or by typing the sheet name 'Sheet2'!A1:A2, using apostrophes around the sheet name, instead of just cell names.
Now... how do you formulate all of these functions to a drop down list? So instead of typing in a query, you just select a stock number from the drop down list.
I have a question that I can't seem to figure out, perhaps you have a video on it or you could guide me in the right direction. I want to have a table of various expenses, and then I want to create an expense tracker using these various categories. In my expense tracker, I want the first column to be to select the category, and then I want the second column to have a drop down list of only the items under that category. Ex. Select "Home" in column 1, then column 2 drop down list dynamically changes to show only "Mortgage" "Internet" "House insurance" etc... the items that are under the Home category in the other table. I can't seem to get the 2nd column to show the correct values in the drop down, dependent on what is selected in the drop down in the first column. I am using Google Sheets. Thank you for your help!
hello ! please i need your help ! i want to replace numbers with rows : for example : i want to replace number 53 ( the cell only countains this number ) with the row 53 please save me !!!
In google spreadsheets how would you simplify the below calculation? Basically, it's an if statement that finds the first number from bottom to top in a column and displays it, if there is no number in the column it checks the 2 columns to the left for a number (bottom to top). IFS(ISNUMBER($J$12),$J$12,ISNUMBER($J$11),$J$11,ISNUMBER($J$10),$J$10,ISNUMBER($J$9),$J$9,ISNUMBER($J$8),$J$8,ISNUMBER($J$7),$J$7,ISNUMBER($J$6),$J$6,ISNUMBER($J$5),$J$5,ISNUMBER($J$4),$J$4,ISNUMBER($G$12),$G$12,ISNUMBER($G$11),$G$11,ISNUMBER($G$10),$G$10,ISNUMBER($G$9),$G$9,ISNUMBER($G$8),$G$8,ISNUMBER($G$7),$G$7,ISNUMBER($G$6),$G$6,ISNUMBER($G$5),$G$5,ISNUMBER($G$4),$G$4,ISNUMBER($D$12),$D$12,ISNUMBER($D$11),$D$11,ISNUMBER($D$10),$D$10,ISNUMBER($D$9),$D$9,ISNUMBER($D$8),$D$8,ISNUMBER($D$7),$D$7,ISNUMBER($D$6),$D$6,ISNUMBER($D$5),$D$5,ISNUMBER($D$4),$D$4)
For some odd reason Google Sheets will not allow my Index Match Match Function to work. Never had this issue in Excel. I copied your exact sheet, but still no success. Any advice?
First, how google search only now directed me to your channel after years of me "fighting" Google Sheets. Great Content! I'm trying to merge formulas from a few of your videos, but I'm lost now... I have a number values in 'Sheet 1'!A:A, and in 'Sheet 2' thru 'Sheet 11' numbers scattered in A:D tables with Column A being the rows identifiers. How do I pull in 'Sheet 1'!B:B the identifier row of the Sheet 1'!A:A values that could be in any of those sheets from 'Sheet 2' thru 'Sheet 11' and are not repetitive numbers. I'm trying the INDEX, MATCH, INDIRECT combination but can't make it work. Please help! Thank you!
What is the formula to link color from one cell to another? Without calculating cell value. as it is ( only color not conditional formatting from other cell)
Great Video. I have a challange. I am using the following formula to auto populate a cell in a sheet from a rolling down cell in another sheet but when I run it, it doesn't show me the latest cell's value. Do I need to do anything in my sheet1 for this to show the latest cell's data? Thanks =index(sheet1!M:M,COUNT(sheet1!M:M)+1)
I want to extract 1. the row and a specific column where CM exceeds RCM first time and its previous row 2. the row and a specific column where Difference is minimum in the sheet docs.google.com/spreadsheets/d/1N9VL5nxNZI33dz6itlVTM5vqZMT_IaQZudD_O0mXT0M/edit#gid=0 If you can give me the idea it will be great help for me
@@ExcelGoogleSheets Thanks for your reply. But for first question I need to extract the entire row 32 and 33 by comparing column A and B only and for second question I need to extract the row 28 because the column H contain the minimum value.
Dude.... you are a brilliant teacher. Slow, steady and very effective. how do you not have a million subscribers?
Because youtube don't promote the teachers and good people. they promote the singers, poppers etc,..
I agree! Love this guy!
@@3zha He taught me everything I know about Google Sheets
I have never seen a teacher like u who can solve people problem in a minute. Hats off to You!
grasped the concept before the video was even done. that's how clear your build up was.
exactly what i was looking for, thanks!
never thought I could succeed with these, i understood 100% of what you've demonstrated and it worked for me.
Thanks.
You are a genius! Seriously. I would have thought initially that INDEX wasn't the right function for what I was trying to achieve. But Voila! Watching the tutorial made perfect sense, was easy to follow, and got me the result I was looking for.
Fantastic, learning so much from these videos!
Thank you, works like a damn charm and even though what I was trying to figure out was slightly different, these steps actually got me on the way to figuring out my problem.
more understand after watch this video, after try from just reading at other blog and google website.
Thank you so much for this video.
You deserve to be a Teacher. Your way of explaining hidden points helps more. Thanks you so much for the video.
\
Thank you!
I can't say enough about your channel. Thank you so much for your content
I appreciate that!
Absolutely amazing teacher. I have went all over looking for information and this channel is the absolute best!! Google should hire you all for it's learning labs. The only thing that would be awesome is if you could include your final formula so I could copy and paste it into my own and adjust from there. But maybe people wouldn't watch it until the end and that would be no good for the channel. but thanks again!!
Thank you!
superbly well explained. was looking for a solution tried various options did not understand, till I saw your video. Thanks a ton
hey man v good presentation. easy to pick up on . strongly recommend this video for computer illiterates.
This was so brilliant!!! Thanks so much. Now I have an issue with sorting static and dynamic data on a tab. Could you please do a tutorial on this as well?
Thank you teacher. Your videos are amazing
Your videos are so, incredibly useful. Thank you!
Glad you like them!
So if we add a sort fuction to have them automaticaly from the maximum match to the lowest will do?
superb
What is the opposite of the MATCH function. I.e. I want to find the cells that DO NOT CONTAIN a certain word, like NIKE. Is that possible?
very useful with super clear explanation. Thanks so much!
Thank you Bro ! You saved my life ! :)
Can we this work across multiple tabs/sheets?
Yes, by either clicking the sheet and select dragging the cells, or by typing the sheet name 'Sheet2'!A1:A2, using apostrophes around the sheet name, instead of just cell names.
Awesome, thanks for the video
Thank you so much for your tutorial, such a big help!! God bless 😊
Can you use filter and ONLY get the match?
I merged two or three cell for stock number column cell and this formula only give the first match type/price. How can I solve this?
Thank you so much! This info is invaluable!
Very good. I would like to see matching on dates too.
What is the advantage (if any) of INDEX and MATCH against VLOOKUP? Thanks for the answer.
Hi, how to highlight matched value in 2 different workbooks in Google sheet
You are amazing bro, thank a lot
THANK YOU SO MUCH!
Great tutorial !!!, why don't you share the link of the Google sheet, then we can practise also. Thanks for the tutorial.
How did you get the $ to the left?
Now... how do you formulate all of these functions to a drop down list? So instead of typing in a query, you just select a stock number from the drop down list.
I have a question that I can't seem to figure out, perhaps you have a video on it or you could guide me in the right direction.
I want to have a table of various expenses, and then I want to create an expense tracker using these various categories.
In my expense tracker, I want the first column to be to select the category, and then I want the second column to have a drop down list of only the items under that category.
Ex. Select "Home" in column 1, then column 2 drop down list dynamically changes to show only "Mortgage" "Internet" "House insurance" etc... the items that are under the Home category in the other table.
I can't seem to get the 2nd column to show the correct values in the drop down, dependent on what is selected in the drop down in the first column.
I am using Google Sheets.
Thank you for your help!
th-cam.com/video/1SIN5NyQ9fw/w-d-xo.html
hello ! please i need your help ! i want to replace numbers with rows : for example : i want to replace number 53 ( the cell only countains this number ) with the row 53 please save me !!!
In google spreadsheets how would you simplify the below calculation? Basically, it's an if statement that finds the first number from bottom to top in a column and displays it, if there is no number in the column it checks the 2 columns to the left for a number (bottom to top).
IFS(ISNUMBER($J$12),$J$12,ISNUMBER($J$11),$J$11,ISNUMBER($J$10),$J$10,ISNUMBER($J$9),$J$9,ISNUMBER($J$8),$J$8,ISNUMBER($J$7),$J$7,ISNUMBER($J$6),$J$6,ISNUMBER($J$5),$J$5,ISNUMBER($J$4),$J$4,ISNUMBER($G$12),$G$12,ISNUMBER($G$11),$G$11,ISNUMBER($G$10),$G$10,ISNUMBER($G$9),$G$9,ISNUMBER($G$8),$G$8,ISNUMBER($G$7),$G$7,ISNUMBER($G$6),$G$6,ISNUMBER($G$5),$G$5,ISNUMBER($G$4),$G$4,ISNUMBER($D$12),$D$12,ISNUMBER($D$11),$D$11,ISNUMBER($D$10),$D$10,ISNUMBER($D$9),$D$9,ISNUMBER($D$8),$D$8,ISNUMBER($D$7),$D$7,ISNUMBER($D$6),$D$6,ISNUMBER($D$5),$D$5,ISNUMBER($D$4),$D$4)
For some odd reason Google Sheets will not allow my Index Match Match Function to work. Never had this issue in Excel. I copied your exact sheet, but still no success. Any advice?
Can you do this across different google sheets/files?
yes, use IMPORTANGE
First, how google search only now directed me to your channel after years of me "fighting" Google Sheets. Great Content!
I'm trying to merge formulas from a few of your videos, but I'm lost now...
I have a number values in 'Sheet 1'!A:A, and in 'Sheet 2' thru 'Sheet 11' numbers scattered in A:D tables with Column A being the rows identifiers. How do I pull in 'Sheet 1'!B:B the identifier row of the Sheet 1'!A:A values that could be in any of those sheets from 'Sheet 2' thru 'Sheet 11' and are not repetitive numbers.
I'm trying the INDEX, MATCH, INDIRECT combination but can't make it work.
Please help! Thank you!
What is the formula to link color from one cell to another?
Without calculating cell value.
as it is ( only color not conditional formatting from other cell)
There are no formulas to get color. You can do it with a script.
15:36
the moment he pressed enter...
...my mind IMMEDIATELY went 🤯.
Great Video. I have a challange. I am using the following formula to auto populate a cell in a sheet from a rolling down cell in another sheet but when I run it, it doesn't show me the latest cell's value. Do I need to do anything in my sheet1 for this to show the latest cell's data? Thanks
=index(sheet1!M:M,COUNT(sheet1!M:M)+1)
Thank you! I finally got mine to work!
Can you mix if and match functions? As in if it matches then do something, else do something else.
=if(ISNUMBER(MATCH(blah)),doifmatch,doifdoesntmacth)
@@ExcelGoogleSheets thank you. Will try it. Idea for a video tutorial?
why would you use combined index/match instead of VLOOKUP?
to avoid hard-coded column index in VLOOKUP, which fails once you add or remove columns from your source data.
Great...!! thanks
thank you. very much explained
You are Superb !! Keep up the good work
If I may conclude, what INDEX & MATCH does is equal VLOOKUP right?
Yes, in most cases.
You are really brilliant. Do u know how to get dynamic web data to google spreadsheet?
Depends. This may work for you th-cam.com/video/pwZ44kAeiOo/w-d-xo.html
@@ExcelGoogleSheets Thank You I will check it.
Thanks for posting.
Great explanation!
what if there are two matches??? how to reveal the other one?
th-cam.com/video/_qEc8CEgUl4/w-d-xo.html
Great! Thank you.
Thank You !!!
Why not a vlookup?
I was told if what you're looking for is on the left side.
awesome
Thank you
thank you very much
Thank you bro..
Thank you!
🙏🏻
Thanks a lot
yeah
10:00
This is some kind of backward vlookup , isn't it?
Yes. It is a lot more flexible than VLOOKUP though.
I want to extract
1. the row and a specific column where CM exceeds RCM first time and its previous row
2. the row and a specific column where Difference is minimum
in the sheet docs.google.com/spreadsheets/d/1N9VL5nxNZI33dz6itlVTM5vqZMT_IaQZudD_O0mXT0M/edit#gid=0
If you can give me the idea it will be great help for me
1. =arrayformula(MATCH(TRUE,B:B>G:G,0))
2. I don't understand the second question.
@@ExcelGoogleSheets Thanks for your reply. But for first question I need to extract the entire row 32 and 33 by comparing column A and B only and for second question I need to extract the row 28 because the column H contain the minimum value.
The spreadsheet is read only and what do you mean by "entire row"?
It was in view mode Now I have changed it to Edit Mode. Entire row means the following data
3736.45 1385.45 361.05 1200 3.85 33.1 1443.1 57.65
You are all set.