Group query To find sales and Quantity With 2 conditions by using VLOOKUP

Key Takeaways

hi friends this is Excel Guru rajinikanth and here is the query which is asked in Facebook group see I'll explain the query later I'll show you the solution see here is the data where it is having ID and product in different ranges like in B range it is having ID numbers in series and C range it is having product names and here we are having quantity and price right so here what we have to do whenever you enter any of these two ranges like this any of these two ranges like either LED or a product ID or a product if you'll do it will enter any of these two things then it should extract the quantity and the price in different columns okay what I mean to say see your product key is there see if I enter any one see what will happen it will show us this 16 and the 500. see I'll delete this one oh very my Emojis big here is the I don't know why what happening to my mouse it is playing with me okay I have to play with Excel but it is playing with me I don't know what automatically it is moving up and down I've not done anything I don't know why it is going like that okay here is the solution where I want to explain you first of all we have to by using vlookup guys by using only vlookup but not with index and match this is the thing you have to remember see what I'll do first of all here I will do here is a it is an R condition first of all first of all I like to explain you it is an or condition we are saying that any of these these things are these things I'm not telling this both should be and it is or either one two three products or product names we have to extract quantity and price so here is the solution what you will do in first of all you select these things I'm walking simply no don't worry you know so so see wherever it finds it finds the true and remaining oral forces now we have to get this one also so what I'll do I'll use a plus in Boolean plus is called or condition and multiplication that is asterisk sign indicates that is and condition it's an or condition okay simply a press enter I'll show you here and in Boolean or um Plus is not coming let me check place like or how it can be see one one this I'm taking this multiplication one okay it will be one it is all how will it be will give you two okay I'm dealing or and this is and okay if you have any doubt ask me coming back to the formula I have to F9 when I went to okay I have not mentioned the question over here equal of this F9 see enter and explain step by step After Fall I'll highlight this thing F9 and F9 see what it is it is saying that whether in this range one is available or not if it is dead it has to show true true false false this is one condition or is it there true false or is it true no false false words true plus false will be answer one two indicates one false indicate zero so here is the thing now what you have to do we got a number right now I have used the function called choose where it can pick up these ranges if you pick up the ranges and converts into a table with multiple values by using index function index number here you can see here index actually [Music] um choose not one actually it is what I'll do I want both things one comma two and index 1 will be this thing whole thing what will be the index two here is the logic what you'll do simply use this thing I'm not freezing anywhere okay because I want to move this formula from here to this comma C 18 to d18 now see here what will happen if you highlight this whole thing and press F9 see it will converts into a table 1 for 16 0 17 0 24 okay I mean you understood what I mean to say so it will uh converts into a table from link to table I converted from range to table by using choose function now what you have to look up you have to look up one comma and comma column index second thing comma zero exact match when a press enters it will give you error because it's an array function you have to use Ctrl shift and enter see I drag this formula when I change Pro 3 Pro 3 C when I change Pro 2 thing if you have any doubt ask me and here I will give you the Syntax for what you'll say choose [Applause] Ctrl Shifty a I'll Connect into text this is the thing done or any doubts let me know and more are they more more things are there so you have to be there right more things are there and subscribe and my channel and click on Bell icon so that you will get a notification whenever I upload a new video regarding Mis interview questions these are all things are whatever I am shooting or a what I'm recording or what I am sharing with you all are Mis interview queries thank you guys thanks for your support have a nice day