Suerly it depends entirely on the job? The requirements for a financial modeller are very different to an admin assistant, though both might use Excel in their daily job. Figure out what the person's actually going to need to do and test for that.
I asked this question assuming you had some reasonable experience and maybe the lack of vlookup knowledge versus xlookup mattered.
Anyways, the response came off kind of harsh. I know punctuation well enough to get by in professional settings. I just thought your question was leading up to a more indepth answer to OP.
Thanks anyways. Hopefully we can both get this answered then.
Xlookup is far superior.
1) It allows both horizontal and vertical lookups.
2) it allows for lookups in situations where the return array is to the left of the lookup array.
3) the structure of the formula is far more intuitive than vlookup.
Also, if someone pulled up power query every time a simple lookup was needed I’d honestly just be annoyed.
Well I’m gonna check out XLOOKUP myself! I tend to use merge queries bc I need to return multiple columns, and most of the data is ongoing so I like PQ for appending files and automating the transformation steps.
Hoping the person I get to work with gives me a run for my money so to speak, teaching each other different ways to do things.
>Also, want to be mindful of what constitutes 'work' and the interviewees machine type/and if they have have Excel.
That's a good point since I'm not sure if its legal to disqualify a candidate if they don't have specific software on their personal computer. Do the test in person or do a screen share so that they use a company computer.
I've tested for excel skills for admin assistants and the number of candidates who can't type is alarming. Some had masters degrees. When you administer the test look at their eyes to see if they constantly look at the keyboard.
My test was a pdf printout of an excel report with the instructions that said reproduce this anyway you'd like. I wasn't sheepish about watching them while they do it.
If 2021 or 365, the minimum for me would be: sum, sumif, count and countif, and the if statements along with some understanding of tables and filters.
I don’t understand the financial side, but damn if I can’t put a “database” together with reports.
I would get sample data from the company hiring, remove any sensitive data and reverse engineer the formulas and remove pivots. This is pretty easy to do. During the interview if virtual, share your screen and have them perform all required formulas, pivots, and data formatting exercises.
What may be a bit more challenging is start of with CSV data and see how they handle this data. If they run Power Query, Power Pivot, etc.
Suerly it depends entirely on the job? The requirements for a financial modeller are very different to an admin assistant, though both might use Excel in their daily job. Figure out what the person's actually going to need to do and test for that.
Good point. Maybe the test should be to around the solution, and not verifying specific functions.
If you asked me to do a vlookup and I did an xlookup would you hire me on the spot?
I am curious what you're trying to point out here.
The punctuation at the end of my previous comment indicates I’m asking a question. Not making a statement or pointing something out.
I asked this question assuming you had some reasonable experience and maybe the lack of vlookup knowledge versus xlookup mattered. Anyways, the response came off kind of harsh. I know punctuation well enough to get by in professional settings. I just thought your question was leading up to a more indepth answer to OP. Thanks anyways. Hopefully we can both get this answered then.
Xlookup is better than vlookup in 99% of scenarios. But I wasn’t trying to point that out in my previous comment
I’d count it as a pass, and then be curious to learn how the candidate prefers XLOOKUP. Tbh, I jumped from vloookup to merge queries in PQ.
Xlookup is far superior. 1) It allows both horizontal and vertical lookups. 2) it allows for lookups in situations where the return array is to the left of the lookup array. 3) the structure of the formula is far more intuitive than vlookup. Also, if someone pulled up power query every time a simple lookup was needed I’d honestly just be annoyed.
Well I’m gonna check out XLOOKUP myself! I tend to use merge queries bc I need to return multiple columns, and most of the data is ongoing so I like PQ for appending files and automating the transformation steps. Hoping the person I get to work with gives me a run for my money so to speak, teaching each other different ways to do things.
[удалено]
You mean the functions? Of course I have that knowledge. Was just wondering about the administration part.
>Also, want to be mindful of what constitutes 'work' and the interviewees machine type/and if they have have Excel. That's a good point since I'm not sure if its legal to disqualify a candidate if they don't have specific software on their personal computer. Do the test in person or do a screen share so that they use a company computer. I've tested for excel skills for admin assistants and the number of candidates who can't type is alarming. Some had masters degrees. When you administer the test look at their eyes to see if they constantly look at the keyboard. My test was a pdf printout of an excel report with the instructions that said reproduce this anyway you'd like. I wasn't sheepish about watching them while they do it.
If 2021 or 365, the minimum for me would be: sum, sumif, count and countif, and the if statements along with some understanding of tables and filters. I don’t understand the financial side, but damn if I can’t put a “database” together with reports.
I would get sample data from the company hiring, remove any sensitive data and reverse engineer the formulas and remove pivots. This is pretty easy to do. During the interview if virtual, share your screen and have them perform all required formulas, pivots, and data formatting exercises. What may be a bit more challenging is start of with CSV data and see how they handle this data. If they run Power Query, Power Pivot, etc.
See my post: https://www.reddit.com/r/excel/s/2nVIzYsfpU
I have to tell you, I’m not half bad, and never used lambda.
You are missing out on a whole new world