MNC COMPANY INTERVIEW -6

Key Takeaways

hi friends this is Excel Guru rajant and this is MNC company query maybe six or seven I'm collecting from all the possible ways to share a Solutions with you guys so that if anybody go for interview they make they may face this type of interviews so after watching my solution they can easily crack the interview maximum companies are asking this type of questions since one week I'm observing out of five companies three three companies I think so three companies are ask this query right so here I'm going to give a solution now I will share a screen with you guys here is the screen okay actually I'll Zoom a little bit so that you can able to watch properly okay right so here is the query what you have to do with this query I will explain you guys here what what happens means here there is a product and we have to create a do drone list as per product we have to create a zone for products after that we have to get a price with a formula this is the query so here I will show you the solution before going to show the solution I will show you how it works see it is product one England America India prod one one England America India so here see you can see England America India right see if I change the product as for this product three here America is not there so I'm changing to where product three Malaysia so we have to get Malaysia price okay okay nice so here first of all you have to create a data validation as per product okay you have to create data validation for zone that is Malaysia whenever we change the product it has to show them wherever these products are available product two are there so what is the product Iran Iraq Thailand turkey all these things are there so it has to show over here like this okay like this done now I will show you the solution for this how to create a data validation for this now equal to we have to use the offset to create a table okay offset where we have to start we have to start from here that is D7 sorry E17 comma How many rows do you want to jump from there so in OBET it counts from zero not from one so how many rows it has to jump with that you have to use a match function to pick up the location of this product so it will it has to pick up the location for this F4 comma Z then it will pick up the position that where is product two okay I'll show you row here F9 so fourth position in fourth position there is a product two means in offet what it will do it will start from e77 and it will jump four rows down right understood and now coming to the column how many columns do you want to move from here to uh next so I'm I'm telling you have to move only one column forward means here to after coming to here after coming to here it has to move here you understand after coming to here it will move from from there to here okay and height how much height do you need from starting here to from to how much height un need means it has to include all these things okay so what you have to do you have to use a coun function to pick up the length that is we have to pick up how many couns are there still at that place you have to pick up the height and how many [Music] width comma width it has to move from here to width how much it has to move length is width so what I'll do width will be assume one okay let's think it out and press F9 to evaluate three and how many product are there it may be 1 2 3 4 5 6 so automatically it will pick up six zones got my point it will pick up six zones so what I'll do now here I will do a i press enter and I'll copy the formula from here F2 from starting to here I will name this formula did you got my point what I mean to say I'm naming this formula by pressing control F3 already there so I delete it and new is this one what happened here yes new so what I'll do here I will rename with this not not s it is DV DV data validation okay close now it will create a data validation for us alt D list and here equal to what is the name DV suppose if you forget the name range which has been given by you by the user or you then you have to press only F3 to list of the names is F3 see DV is already there so click on that okay okay [Music] only I don't know where it went from F3 sorry alt I'll make it here alt b l F3 DV okay now press okay it has to come right where I went wrong here okay no need to worry we'll past the formula over here whole formula I pasted over here okay now Che seean Iraq if I change from product two to product one think so what is the product one England America and India that's it okay so here if I change here say England America India right now here is the formula which I'm which I'm going to show you to get the price of the product as per the zone right so here is the formula first of all what you'll do you have to make a two condition that is if this product is available over here or not this is first four right into this the first condition and is there any Iran in the zone range F4 so true into true one I press F9 to get so okay here I show you sorry IND here it is F9 see okay now what I'll do here I will make a v lookup for this that is V lookup of one comma two of I'm using as a array constraint that is 1 comma 2 bracket comma and what will be the second one second one will be the prices F4 close parenthesis okay let me check yes everything is right look one here open the bracket here look up of one choose table what is the column index number the column index number always be at 2 comma 0 it's an array function I'm for that reason I press only enter if you're using 2010 or earlier then you have to use contr for this okay okay okay here the brackets I have to use this again this you want you can check it by using product product two to Thailand I need the price of 2051 cor to Thailand 20 51 okay guys I'll be back with another query see you soon please like share and subscribe