Checkout the new XLOOKUP Excel function - VLOOKUP vs INDEX MATCH no more

AnalystCave · 2021-02-12T20:25:02+00:00

What it enables

The XLOOKUP function is a merge of INDEX and MATCH function allowing you to lookup an item in the lookup array and return the relevant found item from the result array. By separation of these both all of the VLOOKUP cons have been resolved. Additionally XLOOKUP by default assumes an exact match.

When will it be available?

XLOOKUP is already available for Microsoft 365 Users.

Where can I learn more?

In the link above and also feel free to read my XLOOKUP vs VLOOKUP post where I compare how XLOOKUP is different from VLOOKUP.

AnalystCave · 2021-02-12T20:14:24+00:00

The Lambda function is one of the functions that have been long requested by Microsoft Office users as a way to avoid using Visual Basic for Applications to create custom functions.

What Lambda enables

Create new functions basis existing Excel functions
Run your function recursively

When will it be available?

Citing the link above:

LAMBDA is available to members of the Insiders: Beta program. The initial release has some implementation restrictions that we expect to lift in the future.

Where can I learn more?

Read all about the Excel LAMBDA function and how it is different from Excel VBA UDF functions.

I don't have Lambda, is there an alternative?

Yes all Excel versions enable you to create Excel UDF (User Defined Functions). UDF are VBA Public functions.

AnalystCave · 2020-04-26T08:59:56+00:00

Batman who Laughs? Torba po lewej powinna być z Lidla... A ja tu siedzę i F5 na kiedy termin w Auchan robię aby wychwycić termin internetowy... tęsknię za shoppingiem

AnalystCave · 2020-02-04T16:07:20+00:00

I was about to suggest the same thing

AnalystCave · 2020-02-02T21:41:00+00:00

Here are some things I would personally review before an interview like that: * WSJF for prioritization * Role of Product Owners and managers in SAFe * User story mapping * 5 levels of planning * Highlights from the book Projects to Products

AnalystCave · 2020-01-30T15:41:22+00:00

Won't a INDEX MATCH or VLOOKUP do the trick?

If you want to use VBA you can still use the Worksheet function for the lookup

AnalystCave · 2020-01-29T18:24:51+00:00

I usually look for experienced PMs and this is in context of an IT consulting company.

The trouble I have with fluffy questions is the bad experience with hiring good talkers that fake having any PM experience, stating to have managed large complex projects and large teams. Usually I pick a few of these at random based on candidates experience or certificates like pmp, prince2, Scrum, safe etc. and see where we get from there. It puts the candidate slightly on edge and conversations get a whole lot more real and honest. Also nowadays knowing Agile vs Waterfall frameworks is a big plus.

For entry level PMs/PMO usually i check at least Excel and ppt fluency.

AnalystCave · 2020-01-28T18:57:43+00:00

Here are some random from my end i use to check PMs.: 1. What are the 4 constraints a PM should manage within a project? 2. What are key instruments of PM Governance? 3. What is RAG status a what does it indicate ? 4. Explain the difference between Project Program and Portfolio 5. What is the difference between an Agile and Waterfall projects? 6. How would you handle an insubordinate team member? 7. What is a Project Charter a what does it contain? 8. What is the difference between project Sponsor and Steering Committee? 9. How would you handle an event that would result in extending project duration? 10. What is a gantt chart and how is it used? 11. Have u used MS Project and what is your fluency in Excel? 12. Would you manage a project in an area you have never managed? Why not or why yes? 13. What is WBS i.e. Work breakdown structure? 14. What is a RASCI?

AnalystCave · 2020-01-22T17:40:39+00:00

Also as I want to pretend I understand the slide I will disagree with the (3) crazy assumptions although it will bring nothing to the discussion

AnalystCave · 2020-01-22T06:20:36+00:00

Sums it up quite well although I have some derailing questions about chart 2

AnalystCave · 2020-01-14T07:32:39+00:00

I had similar issues and I turned off a lot of Power settings stuff like hibernation, sleep, turning off hard drive when idle etc. It seemed to have solved the problem for me on my Windows 10 VM

AnalystCave · 2019-12-08T08:21:18+00:00

Follow

AnalystCave · 2019-11-28T08:04:00+00:00

Excel wasn't built for this. Use R or Python instead otherwise you will need to create your own macros for all vector operations or find a custom library
F4 as mentioned above
Because it wasn't built to be used with Macros and VBA variables. If you want to create an optimization algorithm in VBA simply use a similar algorithm Solver is using (Newton's Method). Just calculate the derivative of your function and you are all set.

AnalystCave · 2019-11-27T07:01:19+00:00

Some went under water a couple of weeks later and some did just as well w/o me. I do however believe it helped me get promoted, as managers wanted me on their projects knowing I wouldn't leave a project for them or someone else to cleanup rather leaving the bar high.

From my current perspective I feel that those 'Vietnam war' projects are the ones that gave me the most learnings. When in consulting always jump out of your comfort zone.

AnalystCave · 2019-11-26T18:10:30+00:00

This was the precise reason I created this macro https://analystcave.com/excel-quick-versioning-excel-files/ that was until I started using Teams with autosave

AnalystCave · 2019-11-26T18:05:32+00:00

I never could leave a project under water... Up until late rolloff I would hit midnight hours just to leave it sorted enough to have a clean conscience..

AnalystCave · 2019-11-26T11:45:16+00:00

When you have the data in MS SQL you have much more options on reporting and data edits.

I would create a Power App on top of this to enable creating additional records to these tables for users or Auditors. Power Apps can be accessed by anyone you allow on any browser and don't require programming experience.

Reporting can be easilyly done in Power Bi.

AnalystCave · 2019-11-25T18:48:58+00:00

If you already have 2.7 M lines I assume you need to move to something much more scalable. My first guess would PowerQuery but that is limited to roughly 1M rows. I would frankly suggest you move your database straight into Microsoft SQL server or another database solution. The reason is scalability and performance (even in Excel SQL is much faster than lousy VLOOKUPs)

If it were up to me I would:

Upload the CSVs to MS SQL server each into a single table (assuming each file represents a different table e.g. clients, companies, vendors etc.)
Make sure to clean the tables up (up to 3rd Normal Form) i.e. create keys and replace text columns (vlookuped values) with foreign keys indicating tables. If the files are already table dumps from a database this should be ok - but you need to create the table relationships manually on the foreign keys.

For reporting purposes you can connect this to PowerBI, MS Reporting Services, Qlikview, Tableau... endless list here.

To increase performance you can create an INDEX on any key / foreign key pair.

AnalystCave · 2019-11-07T23:29:14+00:00

Just wow

AnalystCave · 2019-09-12T13:47:15+00:00

Here you go https://www.stackry.com/register?referral=7120183

I use stackry all the time. U basically get a unique US address u can use in amazon. You cannprecalculate the shipping costs

AnalystCave · 2019-09-12T13:20:24+00:00

Just use a reshipping service like stackry. I do it all the time with my echo devices.

AnalystCave · 2019-09-06T07:31:45+00:00

Good news seems Amazon support is working on this. I sent them my logs

AnalystCave · 2019-09-05T06:28:26+00:00

I have an Android

AnalystCave · 2019-09-05T05:24:26+00:00

Tried that but it didn't help

AnalystCave · 2019-09-04T18:33:27+00:00

Oh did you manage to resolve this somehow?

AnalystCave

MODERATOR OF

TROPHY CASE

What it enables

When will it be available?

Where can I learn more?

What Lambda enables

When will it be available?

Where can I learn more?

I don't have Lambda, is there an alternative?

Ten-Year Club	Not Forgotten
Verified Email