Vlookup, Index, Match

Hi Coralie,

Thanks you very much for your forum question. My name is Ron and I am a trainer at STL

To answer your question it would be, as you suggested, useful if you could send an example file over. If you want to send it straight to my email address then I will have a closer look and be better able to answer your questions.

My email address is:

ron.oldeboom@stl-training.co.uk

Awaiting your file

Kind regards,

Ron Oldeboom
Learning and Development Consultant
STL training

Hi Coralie,

After a lot of failed attempts I think I finally cracked your answer. The formula is nested and quite complex. The best way to analyse it is to select cell E5 in the Duplicate sheet and then select The Formulas Tab in the ribbon  Formula Auditing group  Evaluate Formula. This will dissect your formula step by step.

In the evaluate formula tool, the underlined item is the item it will evaluate. After evaluating, the result will be shown in Italic.

I used a combination of the following functions just in case you want to see the arguments in the Help pages
SUMIF
VLOOKUP
MATCH
OFFSET

I can’t stress enough that if you had wanted to do this in Power Bi, you would have only needed to bring in the tables on the various sheets, set up a few relationships and put your dashboard together without having to do all this painful formula work.

I believe it is vital for a person in your line of work to learn Power Bi early to keep up with the changes happening in the analysis world.
STL do excellent virtual deliveries in Microsoft Teams. Feedback has been excellent from people that attended our virtual classrooms with some giving preference to virtual over face to face even.

STL runs a one day course https://www.stl-training.co.uk/syl/355/power-bi-training-courses.html which is more of an intro for people that need to work with reports but not necessarily design the reports themselves, However, for the type of work you do I would advise the 2 day course. https://www.stl-training.co.uk/cms/syllabus.php?syllabus_id=205
You can check out the links above for a course outline.

Also if you have a look at the below link, there is a little overview of the Power Bi process you might find interesting. It explains the process from connecting to data to ultimately sharing the reports with other users.
https://web.microsoftstream.com/video/7acb685a-c168-490d-81b6-189615a5bac3

I hope the formula is what you were looking for.

All the best Carolie and very nice to speak with you yesterday.

A Bientot

Ron