GROUP QUERY

Key Takeaways

okay guys here uh rajinikant Excel Guru and here is the query which I'm going to share a solution with you guys and this query from telegram group so I'm using only 2010 functions because everyone is uh easy to apply because and not only that maximum people is not having micr of 365 for that reason I'm using Excel 2010 functions here is the query see I'll Zoom a little bit see what it is actually see here is the names think like that here is the names and here is the product and here is the quantity or uh sales amount or whatever it may be some numbers so whenever it matches here the product and the state or else state or a product suppose it's a state and here is a product so you have to find quantity if 40 is available this is the city assume that this is the city and this is the quantity so we have to find this quantity the 4 quantity is in which product which product is having that's much quantity okay if I enter at 20 over here a20 is not available let's see not a available now A1 here is the city and here is the product 10 products only okay now here I'm going to share a solution with you guys so simple so first of all what you need as for this we need these two things as for this city we need these two things if it is B it has to come 20 and 50 if it is C it has to come 30 and 60 right so we have first of all we have to make it like that then we'll go forward see index first thing index and selecting the whole table this okay because everything I need from the table the values okay comma and what will be the row numbers so I'll use the math function to get that RO number comma and here is the P comma zero so it will pick up the position of a row number that is one in a index table so so what about the column if you select the whole table it will ask the column number also okay if you select a range it can automatically it will understand that you you want row or a column but if you are selecting the whole table then you have to mention the column number so what will be the column number here so I need each and everything so I leave it blank like this then it will understand that you need the whole column if you need the whole row then you have to use a comma over here whichever you need just keep that one and continue with the other ma F9 a 10 and 40 done still here I think you understood and if you have any doubt you can ask me 10 and 140 in a okay let's check it out I'll press enter let's check it out in place of C enter what will happen see 10 is not available it is showing 30 and 60 only 30 and 60 is there 10 is not there okay so formula is here F2 so what I'll do equal to this okay this is the first thing and I press F9 to evaluate and check is there any Tru or falses are there control Z Now what I'll do here 1 ided by 1 / by so 1 divided 0 is divisible by zero error it's an error one divided by true equal to 1 1 divid false equal to error that is d be zero error okay now I press F9 and okay now what I'll do I press enter and I'll show you in see what are the things are the 60 see what will happen F2 F9 see one is available that is one divided by true one divided by true so one okay so we are interested in that one right so here I'm going to use an amazing function called a lookup function to look up a maximum number in Excel that is 9.9 e + 37 comma okay so it will pick up the last occurring number whatever it may be it's the last occurring number and coming to and and what will be the result vector the result VOR will be the whatever we need that is q or a w control shift right arrow close paresis it's an ARR function so there no need to in it will work okay look up can handle array functions press enters W 60 is avilable in 60 quantity w prods in the city of C if I enter anything that is B it will not accept C because in B there is no 60 if I use 50 here then it will pick up W okay guys I hope you understood and if you want more about this please like share and subscribe after subscribe click on Bell icon so that you will get a notifications whenever I upload a new video regarding Exel thank you guys thanks for your support