MIS INTERVIEW QUESTION -101 FINDING VALUE AS PER CHARACTERS LENGTH BY VLOOKUP

Key Takeaways

hi guys how are you all here your friend rajinikanth Excel Guru are you able to see my screen like okay I forgot to share my screen actually wait a minute forgot to share my screen [Music] yes this one just this one okay done okay now I think everyone can able to view my screen properly right and here is the interview question which is asked by one of the company so my friend asked me how to find uh the number of characters length to find this one the visited doctors I mean to say that the character is one then the value should come 15. the character is 2 the character number pack is true then it should come like this whenever we change this one the 15th one see here you can able to see 15 it is pre formatted in it is pre-formatted okay so it is showing only number if I enter 1 here should pick up the first thing this one the 15. okay how is it possible that too with the vlookup only right so here I will explain you the procedure kindly observed clearly right see what I'll do first of all right let's explain you this and this now this and this note see this indicates that the asterisk indicates more than one characters more than one character okay question mark question mark indicates more than oh sorry question mark indicate one character only okay question mark indicates one character only indicates indicates more than one character and question mark indicates single candidate Single Character okay so here observed clearly that how I can do this one I will do this one with help of this asterisk and question mark okay which one I have to use first of all see what I'll use is equal to a repeat of which one you have to repeat mark how many times this many times okay this many times see I'll show you what actually it will show you the repeat function see the text which you want to repeat which text you want to repeat text always in double quotes so which text you want to repeat repeat of which text the question mark question mark indicates Single Character if you give abstract what it will happen it will pick up more than one character all characters more than one characters but we need count of characters that first character I mean this is two characters three characters the length of the characters we need length of the characters so for that reason I am pickuping question mark it indicates Single Character so what you'll do with this I'm asking how many times you should repeat maybe 10 times 5 times how many times so what I will do I'll pick up this one so what it will happen it will show you only one one question mark I am pressing F9 function key see I'll press enter and here again I will show you instead of one I will write 15. so it will show you 15. if I type 2 it will pick up two question marks two question marks with two character strength water it may be two characters of it may be equal sign it may be uh other than numbers other than numbers it has to pick up that thing okay so here what I'll do now I'll use vlookup of here two characters length the lookup value two character strength okay understood what I mean to explain you the two characters length is the look of value so what will be the table array the t is the same table array the same table array F2 I'm selecting the data F4 locking simply unlocking so that I can jump to the formula and which characters under which column do you need I need second column that is 2 comma and it should be exact match not exact story it's the false it's an exact match so I will use 0. now I press enter let's check it out the two characters length of the customer name visited doctor is level so two ways to here it is two okay now I want a conditional format so that whenever I enter any number over here like a two or three or four like this okay so it should highlight this both things simultaneously okay customer name and visited doctors four things it will highlight so what I'll do first of all I'll move a little bit okay from here again I'm coming here what I'll do here most of my characters four characters means it will become what it is become very good value okay sorry Ctrl Z test right four okay it's working perfectly no what I'll do what I'll do wait a minute right now see what I'll do is equal to length of this length of this I need two and false for conditional format length of this equal to this F4 and here I am locking only this column but not the row because I want to copy formula from E to e 2 to f 2. so I don't want uh we should move to C okay only it has to check three four five six but not B to C for that reason I log to B and here it should not move anywhere it should be stable over there only whenever I drag the formula enters controller Ctrl d see here it is showing four characters see here showing true if I enter 2 here not 2 1. see it is pickuping this one still so what I'll do I'll copy this one I'll copy this formula over here Ctrl c f k i highlight this one Ctrl shift right arrow control shift down arrow alt h l n I think you should not uh yes it is visible use formula to determine control V and the format as you like depends on your criteria or anything else okay what I'll do alone color black bold okay now okay okay it should like it should see we have to see like this okay see now I'll change one to five immediately it will go to 50 prime number of characters that is III and value is 25 C here 25 this indicates and if I enter anything else other than one see two four I'm not understood if I understood in next video I am going to show you another thing that is here I think so ah yes okay I'll Zoom it okay everyone should keep concentration on this there are class sections class one first class second class third class fourth class and here is the section whenever you change the student name over here it should reflect that in which from which class and from which section that is student number 10 so here it is what uh 10 a uh 10th class a section okay I did not done just I'm showing tenth class a section there's two pickup okay this thing you have to done by yourself I have not done yet okay I have to do right guys and and I'm expecting thousand likes and thousand views for this video because next video this this is the query I'm going to explain you guys right 10th class and a section here is to the 10th class a section understood okay guys thanks for your support have a nice day