MIS Interview Prep: Analyzing Employee Data & Reporting (Based on a Recent Event)

Key Takeaways

Hi friends, this is Excel guru Raj Kant with the new MIS interview query and if you find any information in this video and it may be useful for you also and please share with your friends and please it's a kind request because every person in some time they need these type of queries. Okay, here is the screen and here is the solution. We have to find the last actually the company last Monday is company's 50th anniversary. So who done exactly closed 50 tickets for that employee? We need 5,000 bonus. We have to the company has to give 5,000 bonus to that person who closed exactly 50. Okay. So yeah now I will show you the solution. First of all what you need means we need the last Monday. Where is last Monday? That is 26th here. Okay. This whole row we need okay observe guys this whole row we need. So for that reason what you have to do means first of all it's a column right. So what you'll do text of we have all we have to check how many Mondays are there and we have to find the last Monday. Okay. Monday, right? Okay. Wherever it find the Monday, it will show us the true. Okay. And now we have to find the all the positions wherever it is there wherever Monday occurs that position we need. So what you have to do means here It's a column right into I'm copying this one Ctrl C the same range for that reason I'm copying that one column of Ctrl V minus column of B2 + one so it will pick up all the positions that wherever it occurs the Monday right That is 4 10 16 22. Four Mondays are there. 4 10 16 22. Now we need last Monday. So what? For that reason you have to use a max of this F9. So it will pick up the 22. Right? Now what you have to do means you have to use a index function here to pick up the 22nd column. Okay, index of we need column but not the row. Observe guys, we need the column. So what you have to do means I'm select the whole table. F4 comma and uh it's a row. Okay. So what I'll do I skip the row and I will place it like this. Whatever we need you have to mention over there that is by see all of the 41 34 26 50 everything is there from the 22nd column number. Okay. 22nd column number here it is 41 to 41 but 50 here 1 2 3 4 3 Okay. Now we are coming to the formula. No 41 41. Okay. Ctrl Z and match the position where actual 50 comma F9 it is in 10th position. Okay. Why show 10th position? It should not show 10. Okay. I have not used this one comma zero exact match that is force. Now coming back to the same and we need enter. Okay, it is in fourth row. The 50 is in fourth row. So use index to pick up the employee name from this range. From this range, pick up the employee name. comma control backspace and enter. It's an array function. You have if you are using Excel 2010 then you have to use control shift and enter. If it is more than 2010 like uh 21 365 then enter is enough. Snehari did exact 50 on last Monday. So he snhari is eligible for 5,000 rupees bonus on that day. Thank you guys. And if you want more like this, please like, share and subscribe. Click on bell icon then only you'll get a notification whenever I upload a new interview question. These are all new interview questions. Okay. So I I request everyone please like, share and subscribe my channel.